Converting a string format timestamp into datetime format?

[synergy825]

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!

[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 · Accepted solution]

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.

[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 · Accepted solution]

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.