topic Re: Creating Timestamp based on FixedNow() in Developer

Creating Timestamp based on FixedNow()

TimAdams — Sat, 26 Jan 2019 09:43:21 GMT

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}}),

ConvertedtoTable

How can I create a timestamp column based on LastRefresh?

Re: Creating Timestamp based on FixedNow()

Greg_Deckler — Sat, 26 Jan 2019 14:56:31 GMT

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.

Re: Creating Timestamp based on FixedNow()

TimAdams — Sat, 26 Jan 2019 16:37:46 GMT

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

Re: Creating Timestamp based on FixedNow()

Greg_Deckler — Sat, 26 Jan 2019 18:08:59 GMT

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?

Re: Creating Timestamp based on FixedNow()

TimAdams — Sat, 26 Jan 2019 18:55:09 GMT

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.

Re: Creating Timestamp based on FixedNow()

Greg_Deckler — Sat, 26 Jan 2019 21:26:38 GMT

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?

Re: Creating Timestamp based on FixedNow()

TimAdams — Sun, 27 Jan 2019 07:09:02 GMT

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