cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
netwire Frequent Visitor
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Converting Unix timestamp from Stripe API to Date/Time

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


9 REPLIES 9
Highlighted
Super User
Super User

Re: Converting Unix timestamp from Stripe API to Date/Time

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


IvanBond Frequent Visitor
Frequent Visitor

Re: Converting Unix timestamp from Stripe API to Date/Time

Hi,

Just an idea - try a function

https://github.com/IvanBond/pquery/blob/master/Date.EpochToDate.m

 

Best regards,

Ivan

netwire Frequent Visitor
Frequent Visitor

Re: Converting Unix timestamp from Stripe API to Date/Time

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?

Super User
Super User

Re: Converting Unix timestamp from Stripe API to Date/Time

@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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


netwire Frequent Visitor
Frequent Visitor

Re: Converting Unix timestamp from Stripe API to Date/Time

Thanks @Greg_Deckler, this worked wonders. Is there a best practice as to when to use Query Editor (with M) and when to use Data (with DAX)?

Super User
Super User

Re: Converting Unix timestamp from Stripe API to Date/Time

Soooo...that's a very good question. One perspective is that it is always the right choice to do as much in "M" as possible as this is more efficient processing-wise. Another perspective on this is that if you are going to end up having to use DAX, then use all DAX, otherwise you are maintaining code in two different languages. This would be like writing a program half in C# and half in java and half in PHP for example. Nobody wants to maintain that. Yet a third perspective is that DAX has, by far, the lower learning curve, especially if coming from Excel so most people start with DAX and then eventually pick up M and start to do more things in M over time.

 

My best practice, use what works for you given your comfort level with the language and use the right tool for the job. Pivoting tables, and really complex manipulations of the data, that's a job for "M". Adding a custom column, that depends on the formula. Some things are a cake walk in DAX and next to impossible in "M" and vice versa. Adding a custom measure, that's DAX all day long.

 

I would recommend reading this:

http://www.powerpivotpro.com/2014/10/5-common-mistakes-made-by-self-taught-dax-students/

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


V_Kyytsis Frequent Visitor
Frequent Visitor

Re: Converting Unix timestamp from Stripe API to Date/Time

Hello,

 

I tried running this on a smallish dataset of about 800 rows. It calculates for a while and then claims to run out of memory and suggests using 64bit version. Which is odd as that is what I'm using, but more odd that this simple conversion would be so memory consuming.  

 

Any ideas?

 

Ville

youngkrs Frequent Visitor
Frequent Visitor

Re: Converting Unix timestamp from Stripe API to Date/Time

For people who want to do this same function in M, its answered in other posts, but want to combine both options here

 

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

 

jramsey5 Visitor
Visitor

Re: Converting Unix timestamp from Stripe API to Date/Time

This just came in handy - thanks Greg!