Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Does Alteryx allow you to convert Unix Epoch time to another date format?

Dom
8 - Asteroid

Does Alteryx allow you to convert Unix Epoch time to another date format?

13 REPLIES 13
michael_treadwell
ACE Emeritus
ACE Emeritus

Unix Epoch time is number of seconds since '1970-01-01 00:00:00'

 

The function DATETIMEADD() is what you need

 

DATETIMEADD('1970-01-01 00:00:00', [Unix Epoch Time], 'seconds')

dataMack
12 - Quasar

Great suggestion Michael.  The same method is also useful if you ever have to convert MS Excel dates that are coming across in the serial format.  For that one 'time started' on 1900-Jan-0 so that would be your beginning in the date add function

Dom
8 - Asteroid

Thank you, Mike! I will give this a try and let you know if it worked.

 

RodL
Alteryx Alumni (Retired)

Since I've seen this create some confusion at times, I always like to point out that the Excel date sometimes needs "tweaks" to the formula (as in adjusting a day or two) because of the way Excel treats dates depending on the platform and because it assume 1900 was a leap year.

 

See Difference in Excel dates and Issue with 1900 as a Leap Year for details on these issues.

bobpeers
9 - Comet

Hi,

 

does this still work in Alteryx 11? I tried this solution on a Timestamp field (Int64) and Alteryx throws an error.

 

The error is this (seems that Alteryx thinks this is a decimal instead of an Int64? I tested just typing a number in the formula and it works perfectly)

 

Warning: Formula (10): Interval out of range for DateTimeAdd: 1491389948000.000000 Record #2 in field Time

and the formula

 

DateTimeAdd("1970-01-01 00:00:00",[Timestamp], "seconds")

 

Update - I see my own problem. My time stamp is in milliseconds, it works when I divide the time stamp by 1000 :-)

Balders
11 - Bolide

I keep forgetting how to do this and finding this post again on google. Thanks Michael!

Inactive User
Not applicable

Yes, i have the same issue. either i type in the number or use column int64  i am getting this error.

 

Error: Formula (22): Parse Error at char(0): Interval out of range for DateTimeAdd: 1530224763584.000000 (Expression #2)

Inactive User
Not applicable

Yes, i have the same issue. either i type in the number or use column int64  i am getting this error. 

Error: Formula (22): Parse Error at char(0): Interval out of range for DateTimeAdd: 1530224763584.000000 (Expression #2)

bobpeers
9 - Comet

See my update above, your timestamp is in milliseconds so you need to divide by 1000 before converting.

Labels