MDY 24-Hour Datetime

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

Shape

Background

Origin

FineType defines this type as month/day/year date ordering (MM/DD/YYYY) with 24-hour time.^[1] FineType's label definition gives the type the format string %m/%d/%Y %H:%M:%S.^[1]

In the United States, dates are traditionally written in month-day-year order.^[2] The slash is the most commonly used separator in the all-numeric form of the American month-day-year date.^[2] The 24-hour clock is used in military, public safety and scientific contexts in the United States.^[2] In the United States, 24-hour notation is also widely used by astronomers, hospitals and public safety personnel.^[2] Wikipedia's article on American date and time notation explains hospital use of the 24-hour clock by the danger of mistaking 5:00 AM for 5:00 PM when medication is due.^[2] The type is best read as a meeting of two American habits: the civilian month-first date and the unambiguous 24-hour time of settings where AM and PM must not be confused. (Meridian's view)

Use

According to Wikipedia, the Chicago Manual of Style discourages all-numeric dates other than the ISO 8601 year-month-day form, because they are not comprehensible to readers outside the United States.^[2] In a file that travels, the order is the weak point: a value such as 03/04/2024 10:00:00 reads sensibly as month-first or day-first, and nothing in the string says which was meant. (Meridian's view) Before trusting a column, look for a value whose first field is above 12, or whose second field is, to settle which order the file uses. (Meridian's view)

In Python's strftime() and strptime() format codes, %m is the month as a zero-padded decimal number.^[3] In Python's format codes, %H is the hour on the 24-hour clock as a zero-padded decimal number.^[3] Python's documentation says that when strptime() is used, the leading zero is optional for formats including %d, %m, %H, %M and %S.^[3] Python's datetime.strptime() raises ValueError if the date string and format cannot be parsed by time.strptime().^[3]

DuckDB's strptime function throws an error on failure.^[4] DuckDB's try_strptime function returns NULL on failure.^[4] DuckDB lets a date or timestamp format be specified during CSV parsing, in a COPY statement or in the read_csv function.^[4] Parse with the explicit format string rather than a guessing parser, and prefer the variant that returns NULL so that rows which fail can be counted rather than silently lost. (Meridian's view) Unless the source documents a time zone, treat the values as local wall-clock time. (Meridian's view)

Where you'll meet it

Klaviyo's developer documentation lists 09/15/2021 13:34:08 among the supported formats for dates and timestamps in profile and event properties.^[5] Klaviyo's documentation says dates must be formatted as either YYYY-MM-DD or MM/DD/YYYY.^[5] Klaviyo's documentation says dates cannot be formatted in the DD-MM-YYYY format.^[5]

Sources

1. [1]finetype/labels/definitions_datetime.yaml at main · meridian-online/finetype · GitHubFineType
2. [2]Date and time notation in the United States - Wikipediasecondary source
3. [3]datetime — Basic date and time types — Python 3.15.0 documentationdocumentation
4. [4]Date Format Functions – DuckDBdocumentation
5. [5]Acceptable date and timestamp formats for profile and event propertiesorganisation

Detection notes

Use it

Schema

Aliases