none
Calculating date from Unix Timestamp in Power Query RRS feed

  • Question

  • So, I'm trying to analyse my Google Location History. I'm loading from the Google export file, through Power Query, into a Power Pivot data model.

    The file contains the datetime of each locational fix as a integer. This integer represents the milliseconds since the epoch, which is 1970-01-01:00:00:00.

    In SQL, this would be straight trivial. I'd get rid of the milliseconds, and do this:

    select

    dateadd(second,1410907851,'1/1/1970')

     and get

    2014-09-16 22:50:51.000

    So, basically, I have the seconds value in the table, and I'm 'hardcoding' the epoch, but that's fine.

    I'm sorry if I'm being stupid, but how on Earth do I do this in M in Power Query?

    Tuesday, November 11, 2014 9:56 PM

Answers

All replies

  • #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, 1410907851)

    • Proposed as answer by Curt Hagenlocher Tuesday, November 11, 2014 9:59 PM
    • Marked as answer by Sam Loud CW Tuesday, November 11, 2014 10:05 PM
    Tuesday, November 11, 2014 9:58 PM
  • Absolutely sensational. Thanks very much Curt, I was a very long way away from working that our for myself.
    Tuesday, November 11, 2014 10:05 PM
  • Is it possible to do this in reverse and change a date formatted as:

    21-Jun-17 13:20:42.34700

    to Unix Epoch time in seconds with milliseconds after the decimal?

    Thursday, June 22, 2017 8:43 PM
  • Hi Brendan,

    One of possible ways is

    Number.Round(86400*(Number.From("YourDateTimeValue")-Number.From(#datetime(1970,1,1,0,0,0))),3)
    Regards

    Friday, June 23, 2017 11:06 AM
  • When I sub in my Date/Time column for "YourDateTimeValue" I get the following error:

    DataFormat.Error: We couldn't convert to Number.
    Details:
        21-Jun-17 13:39:22.77802

    is there a workaround for this?  I have the type set to "Text" because if I change it to Date/time I lose the milliseconds. 

    Monday, June 26, 2017 3:27 PM
  • You do not lose your milisecons... You can't see it only.
    Please, select one "cell" with your data... bottom in the PQ window you can see what is exactly in this cell.
    Monday, June 26, 2017 8:11 PM
  • I have added a custom column with the following:

    Number.Round(86400*(Number.From([Time])-Number.From(#datetime(1970,1,1,0,0,0))),3)

    "Time" is the name of my date/time column.  In the new column it just says Error, but doesn't show any value.  When I click on the individual cell I see the following error in the table viewer:

    DataFormat.Error: We couldn't convert to Number.
    Details:
        21-Jun-17 12:57:59.67201

    Do you see anything I am doing wrong that will explain this?

    Tuesday, June 27, 2017 3:26 AM
  • Try this code below (put it in advanced editor) and look at the result.
    This solution is more precise than previous one.

    let
        Source = Table.FromColumns({{"21-Jun-2017 13:39:22.7780200", "7-Oct-1998 06:02:39.0580150", "21-Jun-17 12:57:59.67201"}}, {"Time"}),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type datetime}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Seconds(Unix)", each Duration.TotalSeconds([Time]-#datetime(1970,1,1,0,0,0))),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "DateTimeFromSeconds", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [#"Seconds(Unix)"])),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Is Time = Seconds(Unix)?", each [Time]=[DateTimeFromSeconds])
    in
        #"Added Custom2

    In my example (code above) my start data (source) are text ,  then i change the type of "Time" column.

    The most interested part ( for You) is
     Duration.TotalSeconds([Time]-#datetime(1970,1,1,0,0,0))
    This is what you need.

    Hope, everything is clear now :-))

    Regards


    • Edited by Bill Szysz Tuesday, June 27, 2017 11:03 AM
    Tuesday, June 27, 2017 11:00 AM
  • Thank you very much!  This works perfectly.

    Brendan

    Tuesday, July 4, 2017 11:54 PM