Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Epoch to Datetime

Hi All 

 

Most of this has already been answered by this community, but I have now run out of steam and cannot work out what I am doing wrong 

 

I have a collumn containing Epoch time stamps

e.g: 1538916520 

 

I have used the following formula provided by v-piga-msft  

 

Timestamp = IF(ISBLANK('Table'[epoc]),BLANK(),'Table'[epoc] / 86400000 + DATE(1970,1,1))

I would expect to see the date returned as 20/08/2019 11:07:23

however I get 18/07/1970 19:28:37 

 

I have also attempted using a custom collumn with the following 

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

 

this returns the same value 18/07/1970 19:28:37 

 

I may be missunderstanding what I am supposed to be doing here, I have not worked with UNIX before so please excuse my ignorance of this.

 

Any help would be much apprechiated 

 

Thanks 

Ryan

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Thanks for the reply 

     

    I have managed to get it kinda working with the following 

     

    = VAR conversion = 'Table1'[epochtime]/(60*60*24)
    RETURN (DATEVALUE("1/1/1970")+conversion)
     
    only problem is it is now returning 01/01/1970 for all the blank cells but I think I can deal with that for now 
     
    Thanks for your help v-frfei-msft 

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    Based on my test in power query, for 1538916520, the result should be  1970/1/18. As i made a test using another one. Everything worked well.

    M code for your reference.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1trA0NDM1MlCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [epoc = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"epoc", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[epoc]/1000))
in
    #"Added Custom"
    let
    Source = #table({"Epoch"},{{1486933998060}}),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Epoch]/1000))
in
    #"Added Custom"
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the reply 

       

      I have managed to get it kinda working with the following 

       

      = VAR conversion = 'Table1'[epochtime]/(60*60*24)
      RETURN (DATEVALUE("1/1/1970")+conversion)
       
      only problem is it is now returning 01/01/1970 for all the blank cells but I think I can deal with that for now 
       
      Thanks for your help v-frfei-msft