MeridianMERIDIAN

DMY Hour-Minute Datetime

datetime.timestamp.dmy_hm

Day/Month/Year datetime with 24-hour clock. Day-first ordering (DD/MM/YYYY). Used across the EU and many Commonwealth countries.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMP
15/01/2024 10:302024-01-15 10:30:00
31/12/2019 23:592019-12-31 23:59:00
%d
day of month, two digits
%m
month, two digits
%Y
four-digit year
%H
hour, 24-hour clock
%M
minutes
Standard

Shape

Pattern
^\d{2}/\d{2}/\d{4} \d{2}:\d{2}$
ValueLengthShape
15/01/2024 10:3016matches
31/12/2019 23:5916matches
15/01/2024 10:315rejected: one character short

Background

Origin

In the United Kingdom, dates are traditionally and most commonly written in day–month–year order.[1] In British usage, the time can be written using either the 24-hour clock, as in 23:59, or the 12-hour clock, as in 11:59 p.m.[1] The 24-hour clock is the most commonly used time notation in the world today.[2] The most commonly used separator between hours, minutes and seconds is the colon, which is also the symbol used in ISO 8601.[2] Read this format as two national writing habits joined by a space, a day-first date and a 24-hour clock time, rather than as a data standard in its own right. (Meridian's view)

In the Unicode CLDR locale data, the short date pattern for French (fr) is dd/MM/y.[3] In the Unicode CLDR locale data, the short time pattern for French (fr) is HH:mm.[3]

Use

Day-first notation contrasts with date notation in the United States, where the month is placed first, leading to confusion in international communications.[1] A value such as 05/04/2024 10:30 reads as a valid timestamp in either order, so one value on its own cannot tell you whether a file is day-first or month-first. (Meridian's view) Look across the whole column for a first number above 12, and check the publisher's documentation when none turns up. (Meridian's view)

Python's strftime and strptime directives define %H as the hour on the 24-hour clock as a zero-padded decimal number.[4] Python's strftime and strptime directives define %M as the minute as a zero-padded decimal number.[4] DuckDB's strptime(text, format) function converts strings to timestamps according to the specified pattern.[5] The DuckDB documentation gives strptime('02/03/1992', '%d/%m/%Y') as an example, returning 1992-03-02 00:00:00.[5] DuckDB lets a timestamp format be specified during CSV parsing with the TIMESTAMPFORMAT option, in the COPY statement or in the read_csv function.[5]

Where you'll meet it

Transport for London's cycle hire journey data extract for 7 to 13 March 2018 writes its Start Date and End Date columns in the form 10/03/2018 12:14.[6]

Sources

  1. [1]Date and time notation in the United Kingdom - Wikipediasecondary source
  2. [2]24-hour clock - Wikipediasecondary source
  3. [3]cldr-json/cldr-dates-full/main/fr/ca-gregorian.jsondocumentation
  4. [4]datetime — Basic date and time types — Python 3.15.0 documentationdocumentation
  5. [5]Date Format Functions – DuckDBdocumentation
  6. [6]100JourneyDataExtract07Mar2018-13Mar2018.csvdataset

Use it

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

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

Schema

https://meridian.online/schemas/datetime.timestamp.dmy_hm · Frictionless type datetime · %d/%m/%Y %H:%M

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

Aliases

little_endian

Type Registry