UNIX to datetime with local zone

Question

Hello&nbsp;I have the following problem. I am importing data from azure postgresql from telemetry where the datetime data is in UNIX format so it is a bigint and is in UTC+00. When converting them to datetime power bi understands that they are in my zone and converts them as if they were UTC+2:00&nbsp;but in reality they are still in UTC+00.&nbsp;I show you the problem:&nbsp;Bigint UNIX:&nbsp;1696120272833GMT: Sunday, 1 October 2023 0:31:12.833Your time zone:&nbsp;domingo, 1 de octubre de 2023 2:31:12.833&nbsp;GMT +2:00but power bi interprets that 0:31:12.883 as my UTC+2:00 time zone.&nbsp;&nbsp;Also, when I do the transformation from UNIX to datetime I cannot add +2 because half the year is UTC +2:00 and the other half is UTC +1:00.&nbsp;Does anyone know how I can fix it? I would need that when going from unix to datetime it would be UTC+00 so that later I could transform that column to my local time zone and add +1 or +2 depending on the time of the year.&nbsp;Thank you&nbsp;

lbendlin · Accepted Answer

use #datetimezone() instead of #datetime
&nbsp;
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSzNDM0MjAyN7IwNlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetimezone(1970,1,1,0,0,0,0,0)+#duration(0,0,0,[Column1]/1000),type datetimezone)
in
    #"Added Custom"

Forum Discussion

UNIX to datetime with local zone

2 Replies

Related content

Convert DateTime to Local Time

Converting MongoDB DateTime to Local DateTime

Convert UNIX timestamp to DateTime using Direct Query

Possible Localization issue

Converting DateTime into Unix Timestamp integer (calculated column & measure)