# SQL Timestamp with Microseconds

Key: `datetime.timestamp.sql_microseconds`

Space-separated date and time with 6-digit microsecond precision. Default output of Python str(datetime.now()) and PostgreSQL TIMESTAMP. Distinct from iso\_8601\_microseconds by space (not T) separator.

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

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

Samples: `2024-01-15 14:30:00.123456`, `2019-12-31 23:59:59.000000`, `2023-06-01 08:15:30.789012`

## Background

### Origin

RFC 3339, Date and Time on the Internet: Timestamps, was written by G. Klyne and C. Newman and published in July 2002.[[1]](https://www.rfc-editor.org/rfc/rfc3339) RFC 3339 notes that ISO 8601 defines date and time separated by "T".[[1]](https://www.rfc-editor.org/rfc/rfc3339) RFC 3339 adds that applications may choose, for the sake of readability, to separate the full date and the full time with a space character instead.[[1]](https://www.rfc-editor.org/rfc/rfc3339)

PostgreSQL's documentation states that the SQL standard requires the use of the ISO 8601 format for date and time output.[[2]](https://www.postgresql.org/docs/current/datatype-datetime.html) PostgreSQL accepts the uppercase T separator on input but writes a space rather than T on output.[[2]](https://www.postgresql.org/docs/current/datatype-datetime.html) PostgreSQL's documentation gives readability and consistency with RFC 3339, as well as with some other database systems, as the reasons for the space.[[2]](https://www.postgresql.org/docs/current/datatype-datetime.html) PostgreSQL's timestamp type has a resolution of 1 microsecond.[[2]](https://www.postgresql.org/docs/current/datatype-datetime.html) The MySQL reference manual describes the standard SQL default fractional seconds precision as 6.[[3]](https://dev.mysql.com/doc/refman/8.4/en/fractional-seconds.html) A space-separated timestamp with six fractional digits is best read as the readable variant RFC 3339 allows, written out to the full microsecond. (Meridian's view)

### Use

Python's documentation states that, for a datetime instance d, str(d) is equivalent to d.isoformat(' ').[[4]](https://docs.python.org/3/library/datetime.html) In Python's strftime, the %f directive is the microsecond as a decimal number, zero-padded to 6 digits.[[4]](https://docs.python.org/3/library/datetime.html)

MySQL's DATETIME and TIMESTAMP values can include a trailing fractional seconds part in up to microseconds (6 digits) precision.[[5]](https://dev.mysql.com/doc/refman/8.4/en/datetime.html) MySQL's default fractional seconds precision, when none is given, is 0.[[3]](https://dev.mysql.com/doc/refman/8.4/en/fractional-seconds.html) PostgreSQL allows a precision of 0 to 6 fractional digits for its timestamp type.[[2]](https://www.postgresql.org/docs/current/datatype-datetime.html) Load this format into a column declared with microsecond precision, such as DATETIME(6) or timestamp(6), so the sixth digit survives the trip. (Meridian's view)

When Python's strptime parses %f, the directive accepts from one to six digits and zero pads on the right.[[4]](https://docs.python.org/3/library/datetime.html) Python's datetime.fromisoformat allows the T separator to be replaced by any single unicode character.[[4]](https://docs.python.org/3/library/datetime.html) The value carries no time zone or offset, so find out from the producing system which clock it was written in before you compare it with UTC data. (Meridian's view) Read the six digits as microseconds, not milliseconds, and keep all of them rather than truncating to three. (Meridian's view)

## Sources

1. [RFC 3339: Date and Time on the Internet: Timestamps](https://www.rfc-editor.org/rfc/rfc3339) — standard
2. [PostgreSQL Documentation: 8.5. Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html) — documentation
3. [MySQL 8.4 Reference Manual :: 13.2.6 Fractional Seconds in Time Values](https://dev.mysql.com/doc/refman/8.4/en/fractional-seconds.html) — documentation
4. [datetime — Basic date and time types](https://docs.python.org/3/library/datetime.html) — documentation
5. [MySQL 8.4 Reference Manual :: 13.2.2 The DATE, DATETIME, and TIMESTAMP Types](https://dev.mysql.com/doc/refman/8.4/en/datetime.html) — documentation