Get current time in UNIX timestamp

[gutovanzin]

Hello!

I created a blank Query that returns the current date and time in ISO format.

= DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-3,0)
Returns: 2021-01-13T16:03:28.8506372-03:00

Is it possible to get the same result however on UNIX Timestamp? Like:

1610519337000

Best, 
Luis

[ruconard]

If you are looking for a formula to the the current UTC Epoch Time, try this:

= Number.Round(Duration.TotalSeconds(DateTimeZone.UtcNow() - DateTimeZone.FromText("1970-01-01T00:00:00Z")))

If you need the value in millisecond UTC Epoch Time, use this:

= Number.Round(Duration.TotalSeconds(DateTimeZone.UtcNow() - DateTimeZone.FromText("1970-01-01T00:00:00Z")) * 1000)

[edhans]

@gutovanzin - I have written a more complete article on this at my blog site. It won't go live until 9am Pacific on Friday, Jan 15 though. But you can see it then. In the mean time, if you have any questions let me know. Otherwise if you could give thumbs up to any posts that have helped and mark one or more as the solution so this thread can be known to be solved it would be appreciated.

[edhans · Accepted solution]

Try this @gutovanzin 

 = Duration.TotalSeconds(DateTime.LocalNow() - #datetime(1970,1,1,0,0,0))

It simply gives you the total seconds from Jan 1, 1970 at midnight through now. Replace my DateTime.LocalNow() with your DateTimeZone.SwitchZone() function and it will work just fine.

You can round that with Number.Round if you don't need all of the decimals.

[gutovanzin]

Hello!
Thanks for the help, i'm trying to get my timezone, but got this error:

= Duration.TotalSeconds(DateTimeZone.SwitchZone(DateTime.LocalNow(),-3) - #datetime(1970,1,1,0,0,0))

Expression.Error: We cannot convert the value #datetime(2021, 1, 14, 8, 40, 13.0448086) to type DateTimeZone.
Details:
Value=14/01/2021 08:40:13
Type=[Type]

Any suggestion?
Thanks!

[edhans]

You cannot use DateTime.LocalNow in a timezone function. It doesn't have any timezone data. You need to use DateTimeZone.FixedLocalNow or DateTimeZone.LocalNow.

However, I am not certian you should use any of those anyway. Unix Epoch time is always UTC +0, and when you load this report in the service, DateTime.LocalNow() as I used above also calculate as UTC +0, so it will be apples to apples. Once you get it into Unix Epoch time, then add (3600 * n) seconds to your final answer, where N is your timezone offset.

[blopez11]

The follwing reference helped me with similar:

https://social.technet.microsoft.com/Forums/en-US/1d5b3cdf-012a-46f7-9fad-fbd45fd2e117/power-bi-calc...