Convert Epoch time to actual date format

[Sachin123]

Hi Guys, 

Can anyone help me how to convert Unix Ephoc time to actual time, this is is because i need to calculate the days difference between two ephoc date fields.

[MarqueeCrew]

I found this in a post: https://community.alteryx.com/t5/Data-Preparation-Blending/Does-Alteryx-allow-you-to-convert-Unix-Ep...

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')

@michael_treadwell solved it :)

[Sachin123]

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

This above format is not working..  What should be value in [Unix Epoch Time]

[MarqueeCrew · Accepted solution]

It should be an numeric value

[gcarmich]

When I run

DATETIMEADD('1970-01-01 00:00:00',[time],'seconds')

I get the following error message

"Warning: Formula (5): Interval out of range for DateTimeAdd: 1499537667000.000000 Record #1 in field date_time"

for all rows.

What does this error message mean?

[gcarmich]

I solved the issue.  My unix time had 11 digits instead of 10.  I trimmed the final number and the formula worked.  I'll need to go back and check to see why the trim is needed and how it effects the actual date.

[GavinAttard]

Hi

Not sure if this is still an issue for you, 

I had a similar issue and figured out the timestamp is returning in milliseconds as opposed to seconds. So rather than trim just convert to seconds. 

Cheers

Gavin

[gcarmich]

That worked.  Dividing the timestamp by 1000 then performing the conversion (DATETIMEADD('1970-01-01 00:00:00',[time_fix],'seconds')) was successful.

Thank you,

Gilbert

[AKPWZ]

Hi @gcarmich 

I have one query related to unix/epoch time conversion.

I'm using this formula to convert this unix/epoch time into normal date time format but it throwing an error - 

Error - "Error: Formula (23): Parse Error at char(38): Formula: tried to apply numeric operator to string value (Expression #1)
"

Formula: DateTimeAdd('1970-01-01',[created_at]/1000, 'Seconds')

Can you please help me what wrong I'm doing here?

Thank you

[Luke_C]

@AKPWZ 

Based on the error message [created_at] is likely being stored as a string, so you can't divide it. If you convert it to be a numeric value it should work.