Hi, Can anyone help to understand how I can covert my am/pm time to 24hrs time in SQL lite

Thanks in advance

Kind regards, Jaydeb

Parsing of time strings other than ISO formatted ones isn't really SQLite's strong suit. If you want to feed am/pm time strings into the SQLite shell, you're probably better off converting the time strings using external means, maybe a Python program or some other programming language that you feel comfortable with. If you are using SQLite embedded in a C program, maybe you can use the strptime function to do what you want.

If the time strings in question follow a very rigid format, you may be able to do it combining SQLite's substring function along with some CASE expressions, but that would be cumbersome and quite fragile.

There was recently a thread on this forum with subject SQL query help for between times and grouping times which discussed the conversion from AM/PM to ISO-8601 notation.

Perhaps convert your data to 24-hour format like this:

UPDATE yourtable SET Ts = CASE WHEN Ts LIKE '%PM' THEN datetime(trim(Ts,'PM '), '+12 hours') ELSE datetime(trim(Ts,'AM ') END;

—above is copy paste of a reply by Dr Hipp from 2017–

Be careful! That produces the wrong result for the first hour after midnight, as well as the first hour past noon.

Somewhat tangentially, see also Confusion at noon and midnight (wikipedia).

That is an interesting article. I had no idea there was that level of "confusion".

To me, it seems intuitive that 12:00:01 am is one second after midnight, and 12:00:01 pm is one second after noon. By extension, it would be silly to consider 12:00:00 am as being offset by more than one second from 12:00:01 am. Never underestimate the ability of people to foul things up.

I do agree that when using a 12-hour clock in prose, it is probably best to use "midnight" or "noon" instead of "12 am" or "12 pm". Given the unlikely nature of any event happening exactly at the stroke of the instant of midnight or noon (instead of being offset by at least some fraction of a second), I feel people infer a precision by the term "12 am" or "12 pm" that doesn't exist, just as they infer a precision in binary floating-point arithmetic.

Thanks for sharing that. It's good to know government and legal sources don't understand (or haven't historically understood) it "intuitively" the way I do so that I can stop assuming it is as obvious as I think it is.

Scott says:

...it seems intuitive that 12:00:01 am is one second after midnight, and 12:00:01 pm is one second after noon. By extension, it would be silly to consider 12:00:00 am as being offset by more than one second from 12:00:01 am.

Someone else might say:

...it seems intuitive that 11:59:59 am is one second before noon, and 11:59:59 pm is one second before midnight. By extension, it would be silly to consider 12:00:00 am as being offset by more than one second from 11:59:59 am.

I'm not claiming one view is more intuitive than the other: just highlighting that there's an "intuitive" view for both positions. And note in passing that the ambiguity is such that in 2008 the United States Government Printing Office reversed their designations: before 2008 they had "12 a.m. = noon" and "12 p.m. = midnight" (matching the second "intuition") and from then switched designations (to match Scott's intuition).

Though I will extend my "intuition" by this example:

12:00:01 pm
12:00:00.1 pm
12:00:00.01 pm
12:00:00.001 pm
12:00:00.0001 pm
12:00:00.00[...]001 pm

This leads me to the intuitive understanding that as we get closer and closer and closer to exactly 12 noon from the right side, we are on the pm side of the line, so I think the 2008 change to make 12 pm be noon was the right move.

Given that "0.999..." is exactly equal to "1.0" (https://en.wikipedia.org/wiki/0.999...) it is also intuitive to me that "11:59:59.999... am" is equal to "12:00:00 pm".

But we're talking about time standards that are not universal constants, which is why we have leap days, leap seconds, daylight time, summer time, standard time, all sorts of different legal standards for them by various governments, time zones, epochs. None of it makes "sense" in that regard.

"11:59:59 am {tick} 12:00:00 am {tick} 12:00:01 pm" hurts my OCD.

Ultimately I agree with the idea that "exactly" noon cannot be AM or PM by the very meaning of the words. Some years ago I worked on software for a company that supported radio station programming. In building a format clock, I had a list of hours of the day that were labeled "1P", "2P", ... "11P", and I used "12M" as the hour that came between "11P" and "1A" just to avoid the ambiguity. "12P" or "12N" doesn't have as much of a problem in my mind since it occurs in the middle of the day, but it is always confusing trying to talk to people about which day "12A" belongs to (but really it is the same problem when referring to "12M"). Until 2019 ISO 8601 allowed using 24:00:00 to refer to the end of the day but now that is forbidden.

Logically, 1:00 A.M. is 1 hour before noon, or 11:00 on a 24-hour clock. Since we don't count backwards on the clock face, A.M/P.M. designates which cycle on a 12-hour clock we are in. 12:00:01 A.M. is 1 second after midnight.

"11:59:59 am {tick} 12:00:00 am {tick} 12:00:01 pm" hurts my OCD.

Same here. To put it in other words, the visual change from 11:59:59 to 12:00:00 is so much greater than the change from 12:00:00 to 12:00:01 (visually and using the Hamming distance), it is only reasonable that the am/pm change should happen at the same time.

Until 2019 ISO 8601 allowed using 24:00:00 to refer to the end of the day but now that is forbidden.

I wasn't aware of that change. It makes sense, though, at least when you wish for machine readable time strings. I might still want to use it for human consumption, though.

I have also noticed that in the US, any insurance contract will start at 12:01 am, in order to avoid ambiguity. It would not help to say 12:00 midnight, since the question is still open whether that is the midnight before or after the stated date. But a slightly disconcerting side effect is that, if you switch insurance providers, your house will be uninsured for one minute after midnight. Better hope it is not struck by lightning at that moment.