Can you help me come up with a brutal but clever Date and Time Parsing?

[morethanyell]

I'm working with a date and time field that's causing a headache. I need to parse it to epoch but using strptime(MyInconsistentDateTimeField, "%d/%m/%Y %l:%M:%S") would only work with some of the values in this field. Say I have a date of "January 2, 2019 9:10:10 a.m." below are the possible formats of my field.

MyInconsistentDateTimeField
02/01/2019 09:10:10
2/01/2019 09:10:10
02/1/2019 09:10:10
2/1/2019 09:10:10
02/01/2019 09:10
2/01/2019 09:10
02/1/2019 09:10
2/1/2019 09:10
02/01/2019 9:10:10
2/01/2019 9:10:10
02/1/2019 9:10:10
2/1/2019 9:10:10
02/01/2019 9:10
2/01/2019 9:10
02/1/2019 9:10
2/1/2019 9:10

As you can see, only the first item can be parsed using | eval parsedDate = strptime(MyInconsistentDateTimeField, "%d/%m/%y %l:%M:%S") How do I brute force parsing this one? I'm thinking of separating every element of the date using regex, treat them, and then concatenate later on before parsing (e.g. day, month, year, hour, minute would be separate fields). But I don't know how to do it.

Thanks in advance!

p.s. It's okay to ignore the 10 seconds in this date.

[somesoni2]

Try like this: (everything before | rex field=MyInconsistentDateTimeField ... is to generate sample data)

| gentimes start=-1 | eval MyInconsistentDateTimeField="02/01/2019 09:10:10#2/01/2019 09:10:10#02/1/2019 09:10:10#2/1/2019 09:10:10#02/01/2019 09:10#2/01/2019 09:10#02/1/2019 09:10#2/1/2019 09:10#02/01/2019 9:10:10#2/01/2019 9:10:10#02/1/2019 9:10:10#2/1/2019 9:10:10#02/01/2019 9:10#2/01/2019 9:10#02/1/2019 9:10#2/1/2019 9:10" | table MyInconsistentDateTimeField | makemv MyInconsistentDateTimeField delim="#" | mvexpand MyInconsistentDateTimeField | eval orig=MyInconsistentDateTimeField  
| rex field=MyInconsistentDateTimeField  mode=sed "s/(^|\s|\/|\:)(\d)(\/|$|:)/\1[0]\2\3/g s/(\[|\])//g s/(\s\d\d\:\d\d)$/\1:00/" | eval epoch=strptime(MyInconsistentDateTimeField, "%m/%d/%Y %H:%M:%S")

[samhays · Accepted solution]

Your solution seems okay to me... maybe something like this:

| rex field=MyInconsistentDateTimeField "(?<d>[^//]*)/(?<m>[^//]*)/(?<y>[^\s]*)\s+(?<h>[^:]*):(?<min>[^:]*)(:(?<sec>.*))?"
| eval y = substr(y, len(y)-1,2)
| fillnull value="00" sec
| eval newTime=printf("%02d/%02d/%02d %02d:%02d:%02d", d,m, y,h,min,sec)
| eval parsed=strptime(newTime,"%d/%m/%y %l:%M:%S")

There may be a better way - but this I think this'd work to normalize the field.

[morethanyell]

Works like a charm. Thank you so, so, very much.

[samhays]

Glad to help and glad it worked out!

[auraria1]

I think you may be on the right track by breaking down the values.

| rex field=MyInconsistentDateTimeField "^(?[\d]+)\/.*"

| rex field=MyInconsistentDateTimeField "^[\d]+\/(?[\d]+)\/.*"

| rex field=MyInconsistentDateTimeField "^[\d]+\/[\d]+\/(?[\d]+)\/.*"

| rex field=MyInconsistentDateTimeField ".(?[\d]+):."

| rex field=MyInconsistentDateTimeField ".:(?[\d]+)."

| fields MyInconsistentDateTimeField, day1, month1, year1, hour1, minute1

check that and see if it grabbed the right values, from that you can build on it as you need.