Reply
Frequent Visitor
Posts: 8
Registered: ‎01-26-2019
Accepted Solution

Creating Timestamp based on FixedNow()

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?


Accepted Solutions
Highlighted
Frequent Visitor
Posts: 8
Registered: ‎01-26-2019

Re: Creating Timestamp based on FixedNow()

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 10,536
Registered: ‎07-11-2015

Re: Creating Timestamp based on FixedNow()

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.

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 8
Registered: ‎01-26-2019

Re: Creating Timestamp based on FixedNow()

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

Super User
Posts: 10,536
Registered: ‎07-11-2015

Re: Creating Timestamp based on FixedNow()

[ Edited ]

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 8
Registered: ‎01-26-2019

Re: Creating Timestamp based on FixedNow()

[ Edited ]

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.

Super User
Posts: 10,536
Registered: ‎07-11-2015

Re: Creating Timestamp based on FixedNow()

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Frequent Visitor
Posts: 8
Registered: ‎01-26-2019

Re: Creating Timestamp based on FixedNow()

[ Edited ]

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