MeridianMERIDIAN

SQL Standard

datetime.timestamp.sql_standard

Common SQL datetime format used in PostgreSQL, MySQL, and DuckDB. Space-separated date and time without timezone.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

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

Shape

Pattern
^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$
ValueLengthShape
2024-01-15 10:30:0019matches
2019-12-31 23:59:5919matches
2024-01-15 10:30:018rejected: one character short

Background

Origin

SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987.[1]

The SQL-92 draft's grammar writes the timestamp string as a date value, then a space, then a time value, with an optional time zone interval after the time.[2] The SQL-92 draft writes the date value as years, months and days separated by minus signs.[2] The SQL-92 draft writes the time value as hours, minutes and seconds separated by colons.[2]

The PostgreSQL manual notes that the SQL standard requires writing just timestamp to be equivalent to timestamp without time zone.[3] According to the PostgreSQL manual, the SQL standard tells a timestamp without time zone literal from a timestamp with time zone literal by whether a + or - sign and an offset follow the time.[3] The PostgreSQL manual gives TIMESTAMP '2004-10-19 10:23:54' as a timestamp without time zone according to the standard.[3]

The PostgreSQL manual notes that ISO 8601 specifies an uppercase letter T to separate the date and the time.[3]

Use

PostgreSQL accepts the ISO 8601 T separator on input but uses a space rather than T on output.[3] The PostgreSQL manual says the space is used for readability and for consistency with RFC 3339 and some other database systems.[3] PostgreSQL's default date/time output style is the ISO format.[3] The PostgreSQL manual's example of the ISO output style, 1997-12-17 07:37:16-08, carries a time zone offset after the time.[3]

MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format.[4] The MySQL manual gives the supported DATETIME range as '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.[4] The MySQL manual writes DATETIME and TIMESTAMP values with a fractional part as 'YYYY-MM-DD hh:mm:ss[.fraction]'.[4]

The DuckDB documentation describes timestamps as formatted according to the ISO 8601 format, YYYY-MM-DD hh:mm:ss[.zzzzzzzzz][+-TT[:tt]].[5] DuckDB's TIMESTAMP type, with the aliases DATETIME and TIMESTAMP WITHOUT TIME ZONE, is a naive timestamp with microsecond precision.[5] SQLite's datetime() function returns the date and time formatted as YYYY-MM-DD HH:MM:SS.[6] In Python, str(d) for a datetime instance d is equivalent to d.isoformat(' ').[7]

A value in this shape says nothing about its time zone, so treat it as local time in whatever zone the source system ran and find out which zone before you compare it with data from elsewhere. (Meridian's view) Expect neighbouring forms in the same column, such as fractional seconds, a trailing offset or a T in place of the space, and decide up front whether those belong to the column or mark rows to look at. (Meridian's view) Parsing with the exact pattern %Y-%m-%d %H:%M:%S is a sensible default, so that a row in a neighbouring form stands out for a look rather than being quietly coerced. (Meridian's view)

Sources

  1. [1]SQL - Wikipediasecondary source
  2. [2]Information Technology - Database Language SQL (Proposed revised text of DIS 9075)standard
  3. [3]PostgreSQL: Documentation: 18: 8.5. Date/Time Typesdocumentation
  4. [4]MySQL :: MySQL 8.4 Reference Manual :: 13.2.2 The DATE, DATETIME, and TIMESTAMP Typesdocumentation
  5. [5]Timestamp Types – DuckDBdocumentation
  6. [6]Date And Time Functionsdocumentation
  7. [7]datetime — Basic date and time typesdocumentation

Use it

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

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

Schema

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

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

Type Registry