DateTimeParse conversion to three decimal places within a second

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.

Date Time

Preparation

Accepted answers

jack_morgan

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

DT to MS.yxmd

All comments

RodL

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

Hey CHawk,

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

DT to MS.yxmd

jdunkerley79

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703