Converting UNIX time to Date in PowerBI for Desktop

[Bokbob]

Hi, i am having issues converting the time in Unix time (60*60*24) to date time.

i tired http://stackoverflow.com/questions/35650485/how-to-convert-unix-time-to-date-in-powerbi-for-desktop

and https://community.powerbi.com/t5/Integrations-with-Files-and/Converting-Unix-timestamp-from-Stripe-A...

but i couldnt get it to work. i have the values like the attached in first picture. Please help me convert this number to datetime.

[Anonymous]

On my side the answer was not working but i figure it out like this :

let
    lastMonthDate =  Date.AddMonths(DateTime.LocalNow(),-1),
    nbOfSecondSince1970 =  Number.Round(Duration.TotalSeconds(lastMonthDate - #datetime(1970, 1, 1,0,0,0))/1000)
in
    nbOfSecondSince1970

[ArkhangelisK]

Old thread, but I was able to solve the time zone issue by adding a bit of code to add a GMT timezone and convert that to local time.

DateTimeZone.ToLocal(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YOUR DATETIME COLUMN]/1000),0))

[DaviDamasceno]

Hi i make this video to explain

https://youtu.be/3N27eS2oJmA

😄

[Eric_Zhang · Accepted solution]

@Bokbob

You can add a custom column

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)

[jpwilkinson]

Thanks for the post, i got it to work for my data.  Only one point was that the unit time I had was unix time in s not ms, so I didn't need the /1000.  I think unix time is quite common in both formats.

[caito103]

Hey!

And how can i apply this formula but with a specified Time Zone?

Thank you!

[Rickmaurinus]

Hi there,

Adding a timezone can be done with the DateTimeZone.SwitchZone function. In that case you could for example use: 

DateTimeZone.SwitchZone(
  #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, [UNIX Timestamp] ), 
  2, 0
)

This example first adds January 1 1970 as a datetimezone value, uses the Unix value to add the respective seconds, and then adjusts the output datetimezone value with a timezone. 

There's some more info right here: 
https://gorilla.bi/power-query/convert-unix-timestamp-to-datetime/

Rick

[migue7899]

hi 

I have the same problem my time zone is gmt -6?

[MarcelBeug]

Adjust the formula with -6 as hours in the #duration part:

#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, -6, 0, [UnixTime]/1000)

[RobThrive]

Hello,

First thanks for all the people posting in this thread it's been a great help.

However I've got a timezone issue.   The MySQL server sits on a box that uses UTC (provided by a supplier so no control for me) I am pulling in the data by selecting the table and it comes to sit on a UK environment/users which twice a year has it's timezone change.

I'm wondering if there is a way to use this code to adapt based on whether GMT is +1 or not in PowerBI.

If not I'll need to figure out how to do it on MySQL and use SQL command to get the data instead. Just preferred to solve it in PowerBi if I can.

Thanks

[Anonymous]

Hello,
what digit to place for my timezone New york?

[migue7899]

hi 

I have the same problem my time zone is gmt -6?