European Dot Timestamp (DMY 24h)
datetime.timestamp.dot_dmy_24hDot-separated Day.Month.Year with space and 24-hour time. Standard in German, Central European, and Russian systems. SAP and German banking exports frequently use this format.
Operations on this type: Parse a date or time
Try a value
European Dot Timestamp (DMY 24h)'s rule accepts it.
Reads it as 2024-03-04 05:06:07
Use it
finetype infer -i '15.01.2024 14:30:00' --mode column
# → datetime.timestamp.dot_dmy_24hSELECT ft_infer('15.01.2024 14:30:00');
-- → 'datetime.timestamp.dot_dmy_24h'strptime({col}, '%d.%m.%Y %H:%M:%S')
-- Format: %d.%m.%Y %H:%M:%SHow finetype reads the samples
DuckDB 1.5.5 · finetype 0.6.66
| Value | As TIMESTAMP |
|---|---|
| 15.01.2024 14:30:00 | 2024-01-15 14:30:00 |
| 31.12.2023 23:59:59 | 2023-12-31 23:59:59 |
| 01.06.2024 08:15:30 | 2024-06-01 08:15:30 |
- %d
- day of month, two digits
- %m
- month, two digits
- %Y
- four-digit year
- %H
- hour, 24-hour clock
- %M
- minutes
- %S
- seconds
Pattern
^\d{2}\.\d{2}\.\d{4} \d{2}:\d{2}:\d{2}$| Value | Length | Shape |
|---|---|---|
| 15.01.2024 14:30:00 | 19 | matches |
| 31.12.2023 23:59:59 | 19 | matches |
| 01.06.2024 08:15:30 | 19 | matches |
| 15.01.2024 14:30:0 | 18 | rejected: one character short |
Background
Origin
The traditional all-numeric form of writing dates in German is the day.month.year order, using a dot on the line as the separator.[1] The German dot separator matches the convention of pronouncing the day and the month as ordinal numbers, because ordinal numbers are written in German followed by a dot.[1] In 1995 the DIN 5008 standard replaced the traditional German date notation with the ISO 8601 notation, such as 1991-12-31.[1] The traditional day.month.year format was re-introduced to DIN 5008 in 2001 as an alternative to the standard yyyy-mm-dd format.[1]
In written German, time is expressed almost exclusively in the 24-hour notation.[1] DIN 1355 and DIN 5008 had laid down the dot as the standard separator between hours, minutes and seconds in Germany.[1] In 1995 the German standard time separator was changed to the colon in the interest of compatibility with ISO 8601.[1]
In most post-Soviet states, the DD.MM.YYYY date format is used with dots as separators and with leading zeros.[1] In Russia, the 24-hour notation is used in writing, with a colon as the standardised and recommended separator.[2]
Use
DuckDB's strptime function throws an error when a string cannot be parsed according to its format.[3] DuckDB's read_csv function accepts a timestampformat option, which is used for converting timestamps while the file is read.[3] The pandas documentation warns that dayfirst=True is not strict, but will prefer to parse with day first.[4] When the day is 12 or under, the digits alone cannot tell this format from a month-first one, so treat the dots as a clue and confirm the source's locale rather than assuming it. (Meridian's view) Validate every row rather than a sample, because a column that is mostly this shape can still hold values written without seconds or without leading zeros. (Meridian's view)
Sources
- [1]Date and time notation in Europesecondary source
- [2]Date and time notation in Russiasecondary source
- [3]Date Format Functions – DuckDBdocumentation
- [4]pandas.to_datetime — pandas 3.0.6 documentationdocumentation
How it's detected
Common in DACH region (Germany, Austria, Switzerland) and Eastern European ERP exports.
Schema
https://meridian.online/schemas/datetime.timestamp.dot_dmy_24h · Frictionless type datetime · %d.%m.%Y %H:%M:%S
finetype taxonomy datetime.timestamp.dot_dmy_24h -o json-schema