Problem Converting UNIX time power bi desktop

[Anonymous]

Hi,

In this topic explain how to convert the unix time type into power bi desktop:

https://community.powerbi.com/t5/Desktop/Converting-UNIX-time-to-Date-in-PowerBI-for-Desktop/m-p/132...

my problem is that my time zone is having day light saving hours which are different for summers and winters. How I can do this ?

Thanks
Shubhs

[Anonymous]

In this topic explain how to convert the unix time type into power bi desktop:

https://community.powerbi.com/t5/Desktop/Converting-UNIX-time-to-Date-in-PowerBI-for-Desktop/m-p/132...

my problem is that my time zone is having day light saving hours which are different for summers and winters. How I can do this ?

Thanks
Shubhs

[v-jiascu-msft · Accepted solution]

Hi Shubs,

The "2" is the adjustment of daylight savings. It should be the below one in your scenario. The time saving is an interval. You can adjust it yourself. 

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", 
each if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 
then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 5, 0, [UnixTime]/1000) 
else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 4, 0, [UnixTime]/1000)),

Best Regards,
Dale

[v-jiascu-msft]

Hi Shubhs,

Daylight saving may not be a problem unless you want to adjust. Because we just convert it rather than changing it. If you want to adjust it in the converting step, maybe you can try it like this.

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", 
each if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 9 
then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 2, 0, [UnixTime]/1000) 
else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)),

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft,

Thanks for the reply and sorry for my delayed response.

Can you please explain the purpose of dividing 1000 on Unix time column, i.e, [unix time]/1000 ?

I was using this formula(when everything was in GMT/UTC

Table.AddColumn(#"Changed Type", "DateTime", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[stored]))

[v-jiascu-msft]

Hi @Anonymous,

Sorry for the confusion, I just cited the example from your link. The time there has milliseconds part. That's why we need to divide it by 1000. According to https://en.wikipedia.org/wiki/Unix_time, we don't need to do it most of the time.

Best Regards,

Dale

[Anonymous]

Hi @v-jiascu-msft

Thanks for clearing this. Just one quick one, that day saving hours changes with time( presently 4 hours and after november it will be 5 hours ), So do this formula work fine then as well ?

Also, Can you please explain the logic of '2' below :

#duration(0, 2, 0, [UnixTime]/1000)

 Thanks for the help. I appreciate your efforts.

Thanks
Shubs

[v-jiascu-msft · Accepted solution]

Hi Shubs,

The "2" is the adjustment of daylight savings. It should be the below one in your scenario. The time saving is an interval. You can adjust it yourself. 

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", 
each if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 
then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 5, 0, [UnixTime]/1000) 
else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 4, 0, [UnixTime]/1000)),

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft

each if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 

This will take the 5 hour day saving hours for just November and December only.. Not Jan, Feb or march ?

As the the month number will be less than 11 ?

Are you understanding the above thing ?

Thanks
Shubs

[v-jiascu-msft · Accepted solution]

Hi Shubs,

I don't know the other boundary. That's why I asked you to adjust it. I assume it is April. It should be this one.

if Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) >= 11 
or Date.Month(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime]/1000)) <= 4 
then #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 5, 0, [UnixTime]/1000) 
else #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 4, 0, [UnixTime]/1000)

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft

Thanks for your help

[Anonymous]

Hi @v-jiascu-msft

Thanks for your help !