SQLite Forum

How to convert am/pm time to 24hrs time format
Login

How to convert am/pm time to 24hrs time format

(1) By Jaydeb (Jaydeb1991) on 2021-05-11 16:02:16 [link]

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

(2) By Harald Hanche-Olsen (hanche) on 2021-05-11 19:36:35 in reply to 1 [link]

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.

(3) By Kees Nuyt (knu) on 2021-05-11 20:46:34 in reply to 1

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

[1]: https://sqlite.org/forum/forumpost/0723a15de3

(4) By anonymous on 2021-05-12 03:05:50 in reply to 1 [link]

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–

(5) By Harald Hanche-Olsen (hanche) on 2021-05-12 12:20:32 in reply to 4 [link]

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](https://en.wikipedia.org/wiki/12-hour_clock#Confusion_at_noon_and_midnight) (wikipedia).

(6) By Scott Robison (casaderobison) on 2021-05-13 03:02:54 in reply to 5 [link]

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.

(7) By TripeHound on 2021-05-13 09:12:16 in reply to 6 [link]

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).

(8) By Scott Robison (casaderobison) on 2021-05-13 09:21:37 in reply to 7 [link]

All true. This is why I labelled it "intuition" instead of "logic". I really didn't mean to come off as talking down about others views (though clearly they are wrong when they do not conform to mine). #haha

In my mind, it makes more sense that the "am pm" designation goes with the hour of the day. That being said, it is already non-intuitive that 12 maps to 0 and 12 and 24.

Intuition and logic are different things, even though people tend to conflate them. Hence my sincere thanks for pointing out that my intuition doesn't match others and thus that I need to be more precise when communicating the "round hours" of "exactly" noon and midnight.

(9.2) By Scott Robison (casaderobison) on 2021-05-13 09:53:14 edited from 9.1 in reply to 7 [link]

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.

(10) By David Jones (vman59) on 2021-05-13 10:26:58 in reply to 7 [link]

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) By Harald Hanche-Olsen (hanche) on 2021-05-13 11:47:11 in reply to 9.2 [link]

> "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.