MeridianMERIDIAN

SQL Timestamp with Milliseconds

datetime.timestamp.sql_milliseconds

Space-separated date and time with 3-digit millisecond precision. MySQL DATETIME(3), Java Timestamp.toString() output. DuckDB uses %g specifier for 3-digit milliseconds.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs TIMESTAMP
2024-01-15 14:30:00.1232024-01-15 14:30:00.123
2019-12-31 23:59:59.0002019-12-31 23:59:59
2023-06-01 08:15:30.7892023-06-01 08:15:30.789
%Y
four-digit year
%m
month, two digits
%d
day of month, two digits
%H
hour, 24-hour clock
%M
minutes
%S
seconds
%g
milliseconds
Standard

Shape

Pattern
^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3}$
ValueLengthShape
2024-01-15 14:30:00.12323matches
2019-12-31 23:59:59.00023matches
2023-06-01 08:15:30.78923matches
2024-01-15 14:30:00.1222rejected: one character short

Background

Origin

ISO 8601 specifies the use of the uppercase letter T to separate the date and the time.[1] RFC 3339, Date and Time on the Internet: Timestamps, was written by G. Klyne and C. Newman and published in July 2002.[2] RFC 3339 notes that applications may choose, for the sake of readability, to separate the full date and the full time with a space character.[2] PostgreSQL accepts the T separator on input but uses a space rather than T on output.[1] PostgreSQL's documentation gives readability and consistency with RFC 3339 and some other database systems as the reasons for the space.[1]

Microsoft SQL Server's CONVERT function calls the pattern yyyy-MM-dd HH:mm:ss.fff the ODBC canonical (with milliseconds) style.[3] SQL Server offers the ODBC canonical (with milliseconds) style under the style numbers 21, 25 and 121.[3] SQL Server's documentation describes an ODBC timestamp escape sequence whose constant takes the form yyyy-MM-dd HH:mm:ss[.fff].[4]

Use

In SQL Server, CONVERT(NVARCHAR, GETDATE(), 121) is documented as returning a value such as 2019-08-23 13:39:17.090.[3] Under compatibility level 110 and higher, SQL Server's CAST and CONVERT operations on the time and datetime2 types always use style 121 as the default.[3] SQL Server's datetime type is not time zone offset aware.[4]

MySQL defines a column with a fractional seconds part using the syntax type_name(fsp), where type_name is TIME, DATETIME or TIMESTAMP.[5] MySQL requires the fsp value to be in the range 0 to 6.[5] MySQL's default fractional seconds precision is 0, which the manual notes differs from the standard SQL default of 6.[5] MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format.[6] MySQL gives no warning or error when it rounds a fractional seconds part on insert.[5]

Python's datetime.isoformat() takes an optional sep argument, a one-character separator placed between the date and time portions.[7] Python's isoformat() timespec value 'milliseconds' truncates the fractional second part to milliseconds in HH:MM:SS.sss format.[7] Python's documentation states that excluded time components are truncated, not rounded.[7]

In DuckDB's format specifiers, %g is the millisecond as a decimal number, zero-padded on the left, from 000 to 999.[8] DuckDB's strptime function throws an error on failure.[8] DuckDB's try_strptime function returns NULL on failure.[8] Parse these values with an explicit format rather than letting a loader guess, and run the try variant first to find the rows that do not fit. (Meridian's view)

Sources

  1. [1]PostgreSQL: Documentation: 18: 8.5. Date/Time Typesdocumentation
  2. [2]Date and Time on the Internet: Timestampsstandard
  3. [3]CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learndocumentation
  4. [4]datetime (Transact-SQL) - SQL Server | Microsoft Learndocumentation
  5. [5]MySQL :: MySQL 8.0 Reference Manual :: 13.2.6 Fractional Seconds in Time Valuesdocumentation
  6. [6]MySQL :: MySQL 8.0 Reference Manual :: 13.2.2 The DATE, DATETIME, and TIMESTAMP Typesdocumentation
  7. [7]datetime — Basic date and time types — Python 3.15.0 documentationdocumentation
  8. [8]Date Format Functionsdocumentation

Detection notes

New in format coverage expansion. MySQL DATETIME(3), Java Timestamp.toString().

Use it

CLI · DuckDB
$ finetype infer -i "2024-01-15 14:30:00.123" --mode column
→ datetime.timestamp.sql_milliseconds

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

Schema

https://meridian.online/schemas/datetime.timestamp.sql_milliseconds · Frictionless type datetime · any

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

Aliases

sql_millis

Type Registry