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

How convert Timestamp to Datetime

Claudio
5 - Atom
Hi guys!
Does anyone help me by teaching me how to convert a formatted field in timestamp(integer) to datetime?
6 REPLIES 6
Treyson
13 - Pulsar
13 - Pulsar

What is the format of your date stamp? May we please have some examples?

Treyson Marks
Senior Analytics Engineer
blyons
11 - Bolide

When you say "timestamp," are you meaning a Unix timestamp (or epoch time)? (There are others that are different. For example, SQL Server has a timestamp data type that is completely different and will not work in the same way.)

 

If so, Unix timestamp is the number of seconds since midnight 1/1/1970 (see http://www.convert-unix-time.com/). So, a simple formula tool containing DateTimeAdd('1970-01-01',timestamp,'seconds') will convert it to a datetime value.

 

I hope that helps.

 

Joe_Mako
12 - Quasar

If you have an integer that based on an Epoch or a Unix Timestamp, a value like 1499056140, then you can use an expression like:

 

DateTimeAdd("1970-01-01",[Unix Time Stamp],'second')

 

If you have an integer to parse like 20170703042900, then you can use an expression like:

 

DateTimeParse(ToString([Integer to Parse]),"%Y%m%d%H%M%S")

 

both would resolve to 2017-07-03 04:29:00

 

For the list of Specifiers to use the DateTimeParse function, see the list on https://help.alteryx.com/11.3/index.htm#Reference/DateTimeFunctions.htm

 

Attached is a workflow that uses each expression.

Inactive User
Not applicable

If you have a timestamp but the data type is integer you would do as follows in a formula tool (assuming ISO format):

 

LEFT([Value],4)+'-'+RIGHT((LEFT([Value],6)),2)+'-'+RIGHT((LEFT([Value],8)),2)+' '+RIGHT((LEFT([Value],10)),2)+':'+RIGHT((LEFT([Value],12)),2)+':'+RIGHT((LEFT([Value],14)),2)

 

An example would be 20170703142800 to 2017-07-03 14:28:00

 

Then adjust your formula output field type to be DateTime.

mborriero
11 - Bolide

You can change your timestamp from a number to a string with a select, then use the DateTime tool.

 

See attachment.

Claudio
5 - Atom

Tks Joe!

Labels