# SQL Timestamp with Milliseconds

Key: `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.

Page: <https://meridian.online/docs/projects/finetype/types/datetime/timestamp/sql_milliseconds>

Format: `%Y-%m-%d %H:%M:%S.%g`

Samples: `2024-01-15 14:30:00.123`, `2019-12-31 23:59:59.000`, `2023-06-01 08:15:30.789`

## Background

### Origin

ISO 8601 specifies the use of the uppercase letter T to separate the date and the time.[[1]](https://www.postgresql.org/docs/current/datatype-datetime.html) RFC 3339, Date and Time on the Internet: Timestamps, was written by G. Klyne and C. Newman and published in July 2002.[[2]](https://www.rfc-editor.org/rfc/rfc3339) 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]](https://www.rfc-editor.org/rfc/rfc3339) PostgreSQL accepts the T separator on input but uses a space rather than T on output.[[1]](https://www.postgresql.org/docs/current/datatype-datetime.html) PostgreSQL's documentation gives readability and consistency with RFC 3339 and some other database systems as the reasons for the space.[[1]](https://www.postgresql.org/docs/current/datatype-datetime.html)

Microsoft SQL Server's CONVERT function calls the pattern yyyy-MM-dd HH:mm:ss.fff the ODBC canonical (with milliseconds) style.[[3]](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) SQL Server offers the ODBC canonical (with milliseconds) style under the style numbers 21, 25 and 121.[[3]](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) SQL Server's documentation describes an ODBC timestamp escape sequence whose constant takes the form yyyy-MM-dd HH:mm:ss\[.fff\].[[4]](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql)

### Use

In SQL Server, CONVERT(NVARCHAR, GETDATE(), 121) is documented as returning a value such as 2019-08-23 13:39:17.090.[[3]](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) 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]](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) SQL Server's datetime type is not time zone offset aware.[[4]](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql)

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

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

In DuckDB's format specifiers, %g is the millisecond as a decimal number, zero-padded on the left, from 000 to 999.[[8]](https://duckdb.org/docs/current/sql/functions/dateformat.html) DuckDB's strptime function throws an error on failure.[[8]](https://duckdb.org/docs/current/sql/functions/dateformat.html) DuckDB's try\_strptime function returns NULL on failure.[[8]](https://duckdb.org/docs/current/sql/functions/dateformat.html) 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. [PostgreSQL: Documentation: 18: 8.5. Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html) — documentation
2. [Date and Time on the Internet: Timestamps](https://www.rfc-editor.org/rfc/rfc3339) — standard
3. [CAST and CONVERT (Transact-SQL) - SQL Server \| Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) — documentation
4. [datetime (Transact-SQL) - SQL Server \| Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql) — documentation
5. [MySQL :: MySQL 8.0 Reference Manual :: 13.2.6 Fractional Seconds in Time Values](https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html) — documentation
6. [MySQL :: MySQL 8.0 Reference Manual :: 13.2.2 The DATE, DATETIME, and TIMESTAMP Types](https://dev.mysql.com/doc/refman/8.0/en/datetime.html) — documentation
7. [datetime — Basic date and time types — Python 3.15.0 documentation](https://docs.python.org/3/library/datetime.html) — documentation
8. [Date Format Functions](https://duckdb.org/docs/current/sql/functions/dateformat.html) — documentation