Creating Timestamp based on FixedNow()

[TimAdams]

I have created a "Last Data Refreshed" table using the query below:

let
   LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
   ConvertedtoTable = #table(1, {{LastRefresh}}),

In

 ConvertedtoTable

How can I create a timestamp column based on LastRefresh?

[Greg_Deckler]

If I understand what you are going for, you could turn it into a function like this:

let fnLastRefresh = () =>

let
   LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
   ConvertedtoTable = #table(1, {{LastRefresh}})
in
 ConvertedtoTable

in 
    fnLastRefresh

Then just create a custom column based on this function and expand the table.

[TimAdams]

Its more, how do I create the timestamp as it is not part of the date column types

[Greg_Deckler]

How about you provide an example of the output that you are looking for? Because I apparently don't get what you are talking about, my converted time has a date and time like "1/26/2019 9:55:18 AM" which seems like a timestamp to me but apparently not?

[TimAdams]

Sorry I am talking about a SQL Timestamp, which should look something like this - 400989863

I did see alot of threads about comnverting a timestamp to a datetime. but not vice versa.

[Greg_Deckler]

Back in the day I created a measure to convert Unix time to UTC time. I looked like this:

UTCTime = 
VAR UnixDays = MAX([UnixTime])/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

So, to convert it back from UTC Time to Unix time, you would do this:

Measure = 
VAR __utc = [UTCTime]
RETURN (__utc - DATEVALUE("1/1/1970"))*60*60*24

So, you would need to know your "epoch" date. For Unix it is 1/1/1970 and it varies depending on which SQL Server flavor you are using. Which version of SQL are you using? MySQL, Oracle, SQL Server, Postgres?

[TimAdams · Accepted solution]

I have got it working using your logic as follows:

let
   LastRefresh = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),
   EpochDate= #datetime(1970,1,1,0,0,0),
   TimeStampWorking=LastRefresh-EpochDate,
   ConvertedtoTable = #table(3, {{LastRefresh,EpochDate,TimeStampWorking}}),
   #"Renamed Columns" = Table.RenameColumns(ConvertedtoTable,{{"Column1", "Refresh Date"}, {"Column3", "TimeStamp Working"}}),
    #"Inserted Total Seconds" = Table.AddColumn(#"Renamed Columns", "Total Seconds", each Duration.TotalSeconds([TimeStamp Working]), type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Total Seconds",{{"Total Seconds", "Timestamp"}})
in
    #"Renamed Columns1"

Thanks for your help