12-Hour Time with AM/PM

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

Shape

Background

Origin

The 12-hour clock divides the 24 hours of the day into two periods, a.m. and p.m.^[1] The abbreviation a.m. comes from the Latin ante meridiem, meaning before midday, and p.m. from post meridiem, meaning after midday.^[1] The 12-hour clock can be traced back as far as Mesopotamia and ancient Egypt.^[1]

The 12-hour clock is the dominant written and spoken system of time in several countries, predominantly nations that were part of the former British Empire.^[1] The United States, Canada outside Quebec, Australia, India and the Philippines are among the countries Wikipedia lists as following the 12-hour convention.^[1] In most countries the 24-hour clock is the standard system, especially in writing.^[1]

Use

NIST's Times of Day FAQs say that 12 a.m. and 12 p.m. are ambiguous and should not be used.^[2] NIST explains that noon is neither before noon nor after noon, so neither a.m. nor p.m. is a correct designation for it.^[2] By convention, 12 a.m. denotes midnight and 12 p.m. denotes noon.^[1] Historically, the style manual of the United States Government Printing Office used 12 a.m. for noon and 12 p.m. for midnight, though this was reversed in its 2008 editions.^[1] When a column holds 12:00:00 AM or 12:00:00 PM, check how the source system meant them before trusting a midnight or a noon, especially in older records. (Meridian's view)

In Python's strptime, the %p directive only affects the parsed hour if the %I directive is used to parse the hour.^[3]

DuckDB's strptime function throws an error when a value does not match the format.^[4] DuckDB's try_strptime function returns NULL on failure instead of raising an error.^[4] Parsing with a function that returns NULL on failure, then counting the NULLs, is a quick way to find the rows that do not fit before you cast the whole column. (Meridian's view)

Python's documentation gives AM and PM as the en_US values of %p, and am and pm as the de_DE values.^[3] The Unicode LDML standard says the AM and PM day period may be upper or lowercase depending on the locale and other options.^[5] Normalise the case of the marker, and strip any dots from forms like a.m., before parsing a column gathered from more than one system. (Meridian's view)

In the Unicode LDML date patterns, the symbol h is the hour from 1 to 12 written with the minimum number of digits, while hh is zero-padded to two digits.^[5] CLDR's English data gives the medium time format, in its ASCII variant, as h:mm:ss a.^[6]

Unicode's CLDR time patterns have used a narrow no-break space between the time and AM/PM since CLDR 42.^[7] CLDR includes optional variants of the English time formats with AM/PM that use ASCII spaces.^[7] JDK 20 upgraded its locale data to CLDR version 42, which replaces regular spaces with no-break or narrow no-break spaces in places including time formats between the a field and the time.^[8] Inside.java warns that after the change, code parsing a string built with a regular space can fail because the parser expects an NBSP or NNBSP.^[8] If a 12-hour time that looks right refuses to parse, look for an invisible U+202F or U+00A0 before the AM or PM, and replace it with a plain space first. (Meridian's view)

Sources

1. [1]12-hour clocksecondary source
2. [2]Times of Day FAQsdocumentation
3. [3]datetime — Basic date and time typesdocumentation
4. [4]Date Format Functionsdocumentation
5. [5]Unicode Locale Data Markup Language (LDML) Part 4: Datesstandard
6. [6]ca-gregorian.jsonstandard
7. [7]The Unicode Blog: ICU 73.2 & CLDR 43.1 released: GB18030 compliance updates & compatibility fixessecondary source
8. [8]Quality Outreach Heads-up - JDK 20-23: Support for Unicode CLDR Version 42documentation

Detection notes

Use it

Schema