MeridianMERIDIAN

MDY 12-Hour Datetime

datetime.timestamp.mdy_12h

Month/Day/Year datetime with 12-hour clock and AM/PM indicator. Month-first ordering (MM/DD/YYYY).

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMP
01/15/2024 10:30 AM2024-01-15 10:30:00
12/31/2019 11:59 PM2019-12-31 23:59:00
%m
month, two digits
%d
day of month, two digits
%Y
four-digit year
%I
hour, 12-hour clock
%M
minutes
%p
AM or PM
Standard

Shape

Pattern
^\d{2}/\d{2}/\d{4} \d{2}:\d{2} [AP]M$
ValueLengthShape
01/15/2024 10:30 AM19matches
12/31/2019 11:59 PM19matches
01/15/2024 10:30 A18rejected: one character short

Background

Origin

In traditional American usage, dates are written in the month–day–year order.[1] The slash is the most commonly used separator in the all-numeric form of an American date.[1] The United States uses the 12-hour clock almost exclusively, in writing as well as in speech.[1] In American written usage the suffixes a.m. and p.m., often represented as AM and PM, are appended universally.[1] The 24-hour clock is used in military, public safety and scientific contexts in the United States.[1] The mdy_12h timestamp is best read as these everyday American habits written down together, rather than as the product of any one standard. (Meridian's view)

The abbreviation a.m. comes from the Latin ante meridiem, before midday, and p.m. from post meridiem, after midday.[2] NIST's Times of Day FAQs say that 12 a.m. and 12 p.m. are ambiguous and should not be used.[3] NIST suggests specifying an event as occurring on a particular day at 11:59 p.m. or 12:01 a.m. to avoid ambiguity.[3] By convention, 12 a.m. denotes midnight and 12 p.m. denotes noon.[2] The style manual of the United States Government Printing Office once used 12 a.m. for noon and 12 p.m. for midnight, though this was reversed in its 2008 editions.[2]

Use

In .NET, the general date short time (g) format for the en-US culture displays 6:30 in the morning of 10 April 2008 as 4/10/2008 6:30 AM.[4]

Python's documentation notes that when used with strptime, the %p directive only affects the output hour field if the %I directive is used to parse the hour.[5] Python's documentation gives AM and PM as the en_US values of %p and am and pm as the de_DE values.[5] Parse the hour with %I and %p together rather than with %H. (Meridian's view)

Sources

  1. [1]Date and time notation in the United Statessecondary source
  2. [2]12-hour clocksecondary source
  3. [3]Times of Day FAQsorganisation
  4. [4]Standard date and time format strings - .NETdocumentation
  5. [5]datetime — Basic date and time typesdocumentation

Use it

CLI · DuckDB
$ finetype infer -i "01/15/2024 10:30 AM" --mode column
→ datetime.timestamp.mdy_12h

SELECT ft_infer('01/15/2024 10:30 AM');
-- → 'datetime.timestamp.mdy_12h'
Cast expression
strptime({col}, '%m/%d/%Y %I:%M %p')
-- Format: %m/%d/%Y %I:%M %p

Schema

https://meridian.online/schemas/datetime.timestamp.mdy_12h · Frictionless type datetime · %m/%d/%Y %I:%M %p

finetype taxonomy datetime.timestamp.mdy_12h -o json-schema

Aliases

middle_endian

Type Registry