Parsing variable-length fractional seconds using strptime

[Alch-Emi]

Howdy! I'm trying to parse a whole bunch of timestamps formatted like this: 2022-04-03 17:38:20.028 UTC. To make matters worse, the number of decimals in the timestamp varies between lines, always being between 0-3. I'm struggling to figure out how to appropriately parse variable-length fractional seconds.

I found #125 and of course the docs on fractional seconds, but neither seemed to provide much insight on how to handle this. Any help is greatly appreciated. Thank you so much!

[aborruso]

You could extract the length of the decimals, and use it as variable

echo "a=2022-04-03 17:38:20.028 UTC" | mlr put '$count=strlen(regextract_or_else($a,"\.[0-9]+",""))-1'

[aborruso]

Or using simply strptime

echo stamp="2022-04-03 17:38:20.028 UTC" | mlr put '$unixtime = strptime($stamp, "%Y-%m-%d %H:%M:%S.%f UTC")'

[johnkerl]

@aborruso this is very nice! :)

Really though we should have a way to handle mixed modes:

$ cat dat.csv
stamp
2022-04-03 17:38:20 UTC
2022-04-03 17:38:20. UTC
2022-04-03 17:38:20.1 UTC
2022-04-03 17:38:20.12 UTC
2022-04-03 17:38:20.123 UTC
2022-04-03 17:38:20.1234 UTC

$ mlr --csv put '$unixtime = strptime($stamp, "%Y-%m-%d %H:%M:%S.%f UTC")' dat.csv
stamp,unixtime
2022-04-03 17:38:20 UTC,(error)
2022-04-03 17:38:20. UTC,(error)
2022-04-03 17:38:20.1 UTC,1649007500.1
2022-04-03 17:38:20.12 UTC,1649007500.12
2022-04-03 17:38:20.123 UTC,1649007500.1230001
2022-04-03 17:38:20.1234 UTC,1649007500.1234

[aborruso]

Hi @Alch-Emi did you solve it?

[Alch-Emi]

Yes, thank you so much! Part of the problem was that I was using an outdated version of mlr (I was using 5.10 from Fedora's repo), and I think I got the impression that %f only parsed 6-digit padded microseconds in any version, and so when I encountered an error, I blamed it on %f without thinking to check my version. Thank you so much for your help!

I might suggest that the docs be updated to show an example of %f be updated a little for recent versions of miller though? For example, the docs for %f in the context of strptime read:

Microsecond as a decimal number, zero-padded on the left.

Which I interpreted to mean that it must be zero-padded to six digits, or at least, that zeros on the left would be interpreted as padding zeros, not zeros which adjust the magnitude of the number (i.e. i thought it meant 013 was equal to 13)

And all examples shown for strptime still use the 6-digit padded microseconds, even in the section explicitly about fractional seconds, where multiple lengths of fraction second are shown for strftime, but only 6-digit examples are shown for strptime. Obviously it's a valid use, but if it mislead me, it might mislead others too.

Either way, I really appreciate your help! Thank you so much!

[johnkerl]

@Alch-Emi thank you -- this is a great idea!!