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

DateTimeParse conversion to three decimal places within a second

CHawk
6 - Meteoroid

I have transactional text data with date formats like "2014-07-18 14:53:09.854" that I need to convert to date/time. Using DateTimeParse([LOGN_TS],"%Y-%m-%d %H:%M:%S") gets me to the second. Is there any format or process to preserve the decimal portions of the second? These transactions occur many times per second and that level of data is important.

4 REPLIES 4
RodL
Alteryx Alumni (Retired)

I'm not sure that in Alteryx there is a method that would preserve the decimal seconds. (Someone else from Alteryx might chime in and correct me if my assumption is incorrect. Smiley Embarassed)

But assuming you need them to calculate differences in times, a thought might be to convert to UNIX epoch time (which is the number of seconds since 1/1/1970). Even that doesn't seem to account for decimal seconds, but you could easily parse out the decimal seconds from your string, convert to epoch time, and then add the decimal seconds back in.

Then you could do your date/time calculations as simple math of the seconds, and then convert back to hours/minutes (or whatever level you are looking for).

To convert to seconds, you could a formula like...

DateTimeDiff([DateTime_Out],'1970-01-01 00:00:00',"Seconds")

 

 

jack_morgan
9 - Comet

Hey CHawk,

The datetime field type limits any data to 19 characters, which is enough for YYYY-MM-DD HH:MM:SS. If you want to preserve that last .854, you'll either need to parse your data out into one field for YYYY-MM-DD HH:MM:SS AND one field for your MS. Take a look at the attached sample workflow to see if that'll help. With that workflow, you'd be able to get number of transactions by second (if that's something that you need by using oa summarize tool and grouping AND counting on the datetime field. I'm with Rod though, I don't think there's a good native solution.

 

Take a look, let me know if you need more assistance. Also, if you are able to provide what you're going to be doing with this data, that'll help identify the best solution for your need!

Jack

jdunkerley79
ACE Emeritus
ACE Emeritus

I think this is a good approach.

 

I would suggest you could create a long field holding milliseconds since an epoc, which would give you something you could calculate off.

 

 Hopefully v11 will have new datetime type

CHawk
6 - Meteoroid

The use of a second field containing the decimal portions of the second will solve the transaction sequencing issue. And honestly I cannot find a business case where time calculations (age/cycle time) need to be any more precise than seconds. So converting to 2 fields will be sufficient for me.

 

I was hoping for single field ease of use for my users though.

Labels