Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Problem Converting UNIX time power bi desktop

 
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

General Comment
Interesting Links
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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's avatar
    v-jiascu-msft
    7 years ago

    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

9 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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)),

    Problem_Converting_UNIX_time_power_bi_desktop

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        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