Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Convert Epoch time to actual date format

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

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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 reboot. Order shall return.
Highlighted
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]

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
It should be an numeric value
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
6 - Meteoroid

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?

 

Highlighted
6 - Meteoroid

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.

Highlighted
ACE Emeritus
ACE Emeritus

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.
6 - Meteoroid

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

Labels