Converting a string format timestamp into datetime format?

Hi all,

I am fairly new to alteryx. I have a JSON file where I parsed it correctly. However, there is a field called "recordTimestamp" that I am trying to convert to a DateTime format. I tried using the DateTime tool, but when I ran the output, the field was blank. Can anyone please assist? I have attached the workflow. Thank you!

JSON Test.yxzp

Date Time

Accepted answers

Felipe_Ribeir0

Hi @synergy825

Like @marcusblackhill said, you must check out the official documentation of the source system of this file to understand what does this number that they are sending really means.

BUT i think that this is a Odata datetime/timestamp, i tried to parse it assuming that it was and found some coherent results. If you compare the value here vs the expected from the source system interface for some rows you will be sure.

JSON Test.yxzp

Felipe_Ribeir0

Hi @synergy825

The Alteryx machine that gonna run this workflow is located on the Central TIme Zone?

If yes, you could use this formula to convert from UTC to your local time zone:

DateTimeToLocal([recordTimestamp])

Another option, you can use the DateTimeAdd function to subtract/add hours:

DateTimeAdd([recordTimestamp], -6, 'hours')

This page is very useful, i am always coming back to it to see how to solve some datetime problems

DateTime Functions | Alteryx Help

All comments

marcusblackhill

Hi @synergy825 !

The datetime tool don't work for that case. That tool is designed to convert different date time formats to a date time format understandable by Alteryx or vice-versa, but the the information need to be very clear there, like, month, day, year, etc.

In your case, the timestamp is a value that you need to understand how was built in order to convert it. For example, some systems export the timestamp as an amount of seconds after a certain date (for example, 10000 after the date 1900-01-01 00:00:00). Unfortunately, I don't recognize the pattern of that timestamp you have, but probably is encoded somehow.

Try to understand with the system owners first how that timestamp works in order to you can build the logic necessary to convert it correctly.

Felipe_Ribeir0

Hi @synergy825

Like @marcusblackhill said, you must check out the official documentation of the source system of this file to understand what does this number that they are sending really means.

JSON Test.yxzp

synergy825

hi @Felipe_Ribeir0, and @marcusblackhill

Thank you both for the reply! I will confirm the official documentation to be sure. Based on your workflow though @Felipe_Ribeir0 , it is a good assumption that it is indeed a Odata datetime. Thank you!

synergy825

Hi @Felipe_Ribeir0 ,

Thanks again for your help. Just wanted to follow up with you regarding the formula:

DateTimeFormat(DateTimeAdd('1970-01-01',(tonumber([recordTimestamp])/1000),'second'),'%Y-%m-%d %H:%M:%S')

If the recordTimestamp is in UTC, do you have a suggestion on how I can convert it to Central Time Zone?

Felipe_Ribeir0

Hi @synergy825

The Alteryx machine that gonna run this workflow is located on the Central TIme Zone?

If yes, you could use this formula to convert from UTC to your local time zone:

DateTimeToLocal([recordTimestamp])

Another option, you can use the DateTimeAdd function to subtract/add hours:

DateTimeAdd([recordTimestamp], -6, 'hours')

This page is very useful, i am always coming back to it to see how to solve some datetime problems

DateTime Functions | Alteryx Help

synergy825

Thank you @Felipe_Ribeir0 ! Appreciate the quick reply! This is very helpful.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703