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

Convert Epoch time to actual date format

Sachin123
6 - Meteoroid

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.

 

10 REPLIES 10
MarqueeCrew
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Sachin123
6 - Meteoroid
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
20 - Arcturus
20 - Arcturus
It should be an numeric value
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
gcarmich
7 - Meteor

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
7 - Meteor

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
11 - Bolide

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

Alteryx Everything, Leave no one behind.
gcarmich
7 - Meteor

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
8 - Asteroid

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
17 - Castor

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

Labels