none
Power BI // Calculating a datetime from a timestamp RRS feed

  • Question

  • Hi,

    I know there's already a thread concerning that topic, but unfortunately it doesn't help me.

    So here's my question: I connect to a MYSQL database where I retrieve specific tables (that already works fine). All tables have stored timestamps (e.g. 1457681580 for 11th March 2016, 07:33 CET).

    As far as I know PowerBI can't work with timestamps (neither use them for filtering or converting to a readable datetime format).

    Do you have any suggestions what to do? Is it possible to add a column and do anything there?

    At the moment I retrieve my data with Excel, do all the converting there and finally read that Excel with PowerBI.

    I'm a PowerBI "beginner", so if you find the time to answer my question please keep that in mind :).. Thanks in advance!

    Wednesday, March 30, 2016 8:28 AM

Answers

  • There's no universally-defined number that's a "timestamp", but in this case it looks like you have a "Unix epoch"-based value. This is defined as the number of seconds since January 1, 1970. You can calculate that simply with one of the following

    #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, 1457681580)
    #datetime(1970, 1, 1, 0, 0, 0 ) + #duration(0, 0, 0, 1457681580)

    The first one is technically more correct, but ends up giving 11th March 2016, 07:33 UTC and not 11th March 2016, 07:33 CET.

    Wednesday, March 30, 2016 1:28 PM
  • Yes. Add a column in the query editor and specify that as the formula. Except instead of the hard-coded constant 1457681580, you'd probably want a column reference like [ColumnName].
    Friday, April 1, 2016 1:27 PM

All replies

  • There's no universally-defined number that's a "timestamp", but in this case it looks like you have a "Unix epoch"-based value. This is defined as the number of seconds since January 1, 1970. You can calculate that simply with one of the following

    #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, 1457681580)
    #datetime(1970, 1, 1, 0, 0, 0 ) + #duration(0, 0, 0, 1457681580)

    The first one is technically more correct, but ends up giving 11th March 2016, 07:33 UTC and not 11th March 2016, 07:33 CET.

    Wednesday, March 30, 2016 1:28 PM
  • Hi Curt,

    first of all thanks for your reply. Where I'm supposed to do this data transformation? Add a column in Power BIs data model and then "= #datetimezone(1970, 1, 1, 0, 0, 0, 0, 0) + #duration(0, 0, 0, 1457681580)"?

    Thanks & BR!

    Friday, April 1, 2016 7:47 AM
  • Yes. Add a column in the query editor and specify that as the formula. Except instead of the hard-coded constant 1457681580, you'd probably want a column reference like [ColumnName].
    Friday, April 1, 2016 1:27 PM