Forum Discussion

netwire's avatar
netwire
Frequent Visitor
10 years ago
Solved

Converting Unix timestamp from Stripe API to Date/Time

Hi,

Is there a way that I can convert the `created` attribute of a Stripe `charge` object to Date/Time? It's currently in text format as a Unix timestamp, i.e.: 1463948811, see example here https://stripe.com/docs/api#charge_object.

 

I'm new to PowerBI, not sure how to do this in PowerBI Desktop. I assume I'll need to use the Advanced Editor?

  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    Well, if it is a Unix timestamp, then it is the number of seconds from 1/1/1970 (UTC). So something like this calculated column should suffice:

     

    UTCTime = VAR UnixDays = [UnixTime]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

    Basically, convert the number of seconds to the number of days and then add it to the date value of 1/1/1970. Essentially, the same thing you would do in Excel.

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Well, if it is a Unix timestamp, then it is the number of seconds from 1/1/1970 (UTC). So something like this calculated column should suffice:

     

    UTCTime = VAR UnixDays = [UnixTime]/(60*60*24)
RETURN (DATEVALUE("1/1/1970")+UnixDays)

    Basically, convert the number of seconds to the number of days and then add it to the date value of 1/1/1970. Essentially, the same thing you would do in Excel.

    • netwire's avatar
      netwire
      Frequent Visitor
      10 years ago

      Thanks Greg_Deckler. I'm new to PowerBI, I just tried to Add Column and entered the formula, substituting [UnixTime] with the actual column name [created], but it doesn't seem to work. I get a EOF error. 

       

      I then simply tried `(DATEVALUE("1/1/1970")+[created]/(60*60*24))` but PowerBI complained that DATEVALUE isn't a valid function. Thoughts?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        10 years ago

        netwire - I am guessing that you are adding your column while in the Query Editor, correct? The Query Editor uses a coding language informally called "M" while what I posted is a language called "DAX". To use DAX, simply import your data. Then, on the lefthand side of the screen in the Desktop, click on the middle icon that looks like a spreadsheet. This is essentially your data model. Click on the table that you just imported and then click the "Modeling" tab in the ribbon and then "New Column" in the ribbon. Then you can enter the DAX I posted.