Another date / timestamp conversion

Content Authoring
1

We have a number of registry keys from various internal processes that put the date/timestamp as MM/DD/YYYY HH:MM:SS AM/PM or M/D/YYYY H:M:S AM/PM, and I have been tasked with converting those to YYYY-MM-DD HH:MM:SS (with hour being military / 24 hr format) when pulling back that data for analyses.

It ended up being waaay more complex than I thought. I was hoping I could use the format command, but I couldn’t get it to do what I wanted. And most forum posts seem to be converting from the BigFix timestamp format to something else, so they didn’t help much.

I also tried using regular expressions, but that just complicated things and didn’t really help since I still had to do the AM/PM -> military time conversion and leading zero fixes.

So here’s what I have so far - relevance gurus, please review to help me improve or simplify it.

q: ((/*year*/preceding text of first " " of following text of last "/" of it) & "-" & (/*month*/day_of_month (preceding text of first "/" of it) as two digits) & "-" & (/*day*/day_of_month (preceding text of first "/" of following text of first "/" of it) as two digits) & " " & (/*hour*/((if (it as lowercase ends with "pm") then (if ((it < 12)) then ((it + 12) as string) else (it as string) of it) of (preceding text of first ":" of it as integer) else (if (it as lowercase ends with "am") then (if ((it < 12)) then (day_of_month (it) as two digits) else ("00") of it) of (preceding text of first ":" of it as integer) else (it))) & (":" & /*minutes*/(if (length of it = 1 and it as integer < 10) then ("0" & it) else (it)) of (preceding text of first ":" of following text of first ":" of it) of it & ":" & /*second*/((if (length of it = 1 and it as integer < 10) then ("0" & it) else (it)) of it of (preceding text of first " " of following text of last ":" of it)) of it) of it) of following text of first " " of it)) of "5/09/2017 1:04:55 pm"
A: 2017-05-09 13:04:55
T: 0.388 ms
I: singular string

Thanks in advance!!

2

I think an inspector should be added for 12-Hour to 24-Hour conversion

try this but according to relevance evaluation time yours is better

Q:(((last 4 of preceding text of first " " of it) & “-” & ((preceding text of first “/” of it) as integer as month as two digits) & “-” & (preceding text of first “/” of following text of first “/” of it) as day_of_month as two digits) & " " & ( ((((if (it as lowercase ends with “pm”) then ((((preceding text of first “:” of it) as integer mod 12) + 12 ) as string) else (((preceding text of first “:” of it) as integer mod 12) as string)) & “:” & preceding text of last " " of following text of first “:” of it) of ( following text of first " " of it) as string) as time_of_day) as string)) of (“1/2/2017 12:04:55 AM”)
A: 2017-01-02 00:04:55
T: 0.614 ms

3

That’s nice how casting as time_of_day automatically handles the leading zero issue. And I think yours is definitely more readable. The evaluation time difference is negligible I think.

Thanks @selimgoksu!

4

Another way of doing the same thing I guess (just highlights a slightly different approach):

((concatenation "/" of (tuple string item 2 of it; tuple string item 0 of it; tuple string item 1 of it ) of concatenation ", " of (if length of it = 1 then("0" & it) else it) of substrings separated by "/" of tuple string item 0 of it ) & " " & (it as string) of (((if hour_of_day of it = 12 then it - 12*hour else it) of ( ((concatenation ":" of (if length of it = 1 then("0" & it) else it)  of substrings separated by ":" of it) of tuple string item 1 of it) as time_of_day)) + (if tuple string item 2 of it as lowercase = "am" then 0*hour else 12*hour )) )of  concatenation ", " of substrings separated by " " whose (length of it > 1) of ("1/2/2017 12:04:55 AM")

One quick note here though. You mentioned that the format might be M/D/YYYY H:M:S AM/PM . Other statements will fail if you have a single digit for minutes or seconds.

1 Like
5

Thanks Marjan. Guess there doesn’t seem to be a very concise way of doing this conversion.

My original code handles single digit minutes or seconds. Here’s @selimgoksu’s corrected code that should do the same:

q: (((last 4 of preceding text of first " " of it) & "-" & ((preceding text of first "/" of it) as integer as month as two digits) & "-" & (preceding text of first "/" of following text of first "/" of it) as day_of_month as two digits) & " " & (((((if (it as lowercase ends with "pm") then ((((preceding text of first ":" of it) as integer mod 12) + 12 ) as string) else (((preceding text of first ":" of it) as integer mod 12) as string)) & ":" & (if length of it = 1 then("0" & it) else it) of (preceding text of first ":" of following text of first ":" of it) & ":" & (if length of it = 1 then("0" & it) else it) of (preceding text of first " " of following text of last ":" of it)) of ( following text of first " " of it) as string) as time_of_day) as string)) of ("1/2/2017 2:4:5 AM")
A: 2017-01-02 02:04:05
T: 0.371 ms
I: singular string
6

To pile on to the thread, for those of you who (A) really dislike awkwardness like “hour_of_day” and “day_of_month as two digits”, or (B) are really itching for another regular expression, I think this is a little more readable

q: (tuple string item 2 of it & "-" & tuple string item 0 of it & "-" & tuple string item 1 of it & " " & last 2 of ("0" &( if tuple string item 6 of it as lowercase = "pm" then ((tuple string item 3 of it as integer + 12) as string) else (tuple string item 3 of it))) & ":" & last 2 of ("0" & tuple string item 4 of it) & ":" & last 2 of ("0" & tuple string item 5 of it)) of concatenation ", " of parenthesized parts of matches(regex("^([[:digit:]]{1,2})[/]([[:digit:]]{1,2})[/]([[:digit:]]{4})[[:space:]]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[[:space:]]([aApP]m)")) of ("2/3/2017 03:40:34 pm")

A: 2017-2-3 15:40:34
T: 0.386 ms
I: singular string
edit: fixed the padding to two digits that I left off earlier. Still didn’t pad the date, but anyway I like the next version better anyway:

Here’s a version only slightly modified to have fewer words, by the use of more concatenations by “-” and “:” and putting the two-digit pads together

q: (concatenation "-" of (tuple string item 2 of it; lasts 2 of ("0" & it) of (tuple string item 0 of it; tuple string item 1 of it)) & " " & concatenation ":" of lasts 2 of ("0" & it) of ((if tuple string item 6 of it as lowercase = "pm" then ((tuple string item 3 of it as integer + 12) as string) else (tuple string item 3 of it)) ; tuple string item 4 of it; tuple string item 5 of it)) of concatenation ", " of parenthesized parts of matches(regex("^([[:digit:]]{1,2})[/]([[:digit:]]{1,2})[/]([[:digit:]]{4})[[:space:]]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[[:space:]]([aApP]m)")) of ("2/3/2017 03:40:34 pm")
A: 2017-02-03 15:40:34
T: 0.466 ms
I: singular string

I could have avoided the “tuple string items” by using “parenthesized parts X” everywhere, but I think that is only a little shorter and slightly less readable:

q: (concatenation "-" of (parenthesized part 3 of it; lasts 2 of ("0" & it) of (parenthesized part 1 of it; parenthesized part 2 of it)) & " " & concatenation ":" of lasts 2 of ("0" & it) of (( (parenthesized part 4 of it as integer + (if parenthesized part 7 of it as lowercase = "pm" then 12 else 0)) as string) ; parenthesized part 5 of it; parenthesized part 6 of it))  of matches(regex("^([[:digit:]]{1,2})[/]([[:digit:]]{1,2})[/]([[:digit:]]{4})[[:space:]]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[[:space:]]([aApP]m)")) of ("2/3/2017 03:40:34 pm")

A: 2017-02-03 15:40:34
T: 0.363 ms
I: plural string

1 Like
7

Thanks Jason, these are helpful. Though please correct me if I’m wrong, but I don’t think your examples handle the 12pm -> 12 and 12am -> 00 conversions correctly. I am instead getting 12pm -> 24 and 12am -> 12.

8

Quite right, here’s a correction, and as a bonus a set of test cases.

q: (it, (concatenation "-" of (parenthesized part 3 of it; lasts 2 of ("0" & it) of (parenthesized part 1 of it; parenthesized part 2 of it)) & " " & concatenation ":" of lasts 2 of ("0" & it) of (( (parenthesized part 4 of it as integer + (if (parenthesized part 7 of it as lowercase = "pm" and parenthesized part 4 of it as integer != 12) then 12 else if parenthesized part 7 of it as lowercase = "am" and parenthesized part 4 of it as integer = 12 then -12 else 0)) as string) ; parenthesized part 5 of it; parenthesized part 6 of it))  of matches(regex("^([[:digit:]]{1,2})[/]([[:digit:]]{1,2})[/]([[:digit:]]{4})[[:space:]]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[:]([[:digit:]]{1,2})[[:space:]]([aApP]m)")) of it)of (("2/3/2017 " & item 1 of it & ":40:34 " & item 0 of it) of (("am";"pm"), integers in (1,12) as string ))
A: 2/3/2017 1:40:34 am, 2017-02-03 01:40:34
A: 2/3/2017 2:40:34 am, 2017-02-03 02:40:34
A: 2/3/2017 3:40:34 am, 2017-02-03 03:40:34
A: 2/3/2017 4:40:34 am, 2017-02-03 04:40:34
A: 2/3/2017 5:40:34 am, 2017-02-03 05:40:34
A: 2/3/2017 6:40:34 am, 2017-02-03 06:40:34
A: 2/3/2017 7:40:34 am, 2017-02-03 07:40:34
A: 2/3/2017 8:40:34 am, 2017-02-03 08:40:34
A: 2/3/2017 9:40:34 am, 2017-02-03 09:40:34
A: 2/3/2017 10:40:34 am, 2017-02-03 10:40:34
A: 2/3/2017 11:40:34 am, 2017-02-03 11:40:34
A: 2/3/2017 12:40:34 am, 2017-02-03 00:40:34
A: 2/3/2017 1:40:34 pm, 2017-02-03 13:40:34
A: 2/3/2017 2:40:34 pm, 2017-02-03 14:40:34
A: 2/3/2017 3:40:34 pm, 2017-02-03 15:40:34
A: 2/3/2017 4:40:34 pm, 2017-02-03 16:40:34
A: 2/3/2017 5:40:34 pm, 2017-02-03 17:40:34
A: 2/3/2017 6:40:34 pm, 2017-02-03 18:40:34
A: 2/3/2017 7:40:34 pm, 2017-02-03 19:40:34
A: 2/3/2017 8:40:34 pm, 2017-02-03 20:40:34
A: 2/3/2017 9:40:34 pm, 2017-02-03 21:40:34
A: 2/3/2017 10:40:34 pm, 2017-02-03 22:40:34
A: 2/3/2017 11:40:34 pm, 2017-02-03 23:40:34
A: 2/3/2017 12:40:34 pm, 2017-02-03 12:40:34
T: 5.442 ms
I: plural ( string, string )

Can’t say it’s any shorter than the others, but illustrates another approach.

2 Likes
9

Thanks Jason, that’s really nice having the test cases. I look forward to when our admins upgrade our version so I can start using the random values inspectors @jgstew outlined here. We could build up some nice semi-random test cases for various patterns.

1 Like
10

to me, the correct way to do this is write something that parses the given date/time into a bigfix native time object, then convert that to the format required. This may seem more complicated, but it actually can make things easier in the long run, especially when it comes to handling time zones and similar things.

This gives the time:

( ( (if it as lowercase contains "pm" then 12 else 0) * hour ) + ( unique value of (it as local zoned time_of_day) of (it & " " & local time zone as string ) of preceding texts of firsts " " of (it as trimmed string) of following texts of firsts " " of it ) ) of "5/09/2017 1:04:55 pm"

parse the date and combine it with this: https://developer.bigfix.com/relevance/reference/time.html#date-concat-time-of-day-with-time-zone-time

Then convert to desired format.