timestamp
31 timestamp types: 29 with a format string, 1 a sibling also accepts and reads alike.
The 31 timestamp types
2024-03-04 05:06:07 in each type's format, against every sibling's rule
| Type | Format | Writes it as | Also accepted by |
|---|---|---|---|
SQL Timestamp with Microseconds and Offset sql_microseconds_offset · TIMESTAMPTZ | %Y-%m-%d %H:%M:%S.%f%z | 2024-03-04 05:06:07.123456+00 | pg_short_offset |
Common Log Format (Apache/Nginx) clf · TIMESTAMPTZ | %d/%b/%Y:%H:%M:%S %z | 04/Mar/2024:05:06:07 +00 | — |
C ctime / asctime Format ctime · TIMESTAMP | %a %b %-d %H:%M:%S %Y | Mon Mar 4 05:06:07 2024 | — |
DMY Hour-Minute Datetime dmy_hm · TIMESTAMP | %d/%m/%Y %H:%M | 04/03/2024 05:06 | — |
European Dot Timestamp (DMY 24h) dot_dmy_24h · TIMESTAMP | %d.%m.%Y %H:%M:%S | 04.03.2024 05:06:07 | — |
Dot YMD Timestamp (24h) dot_ymd_24h · TIMESTAMP | %Y.%m.%d %H:%M:%S | 2024.03.04 05:06:07 | — |
Unix Epoch Nanoseconds epoch_nanoseconds · TIMESTAMP | — | 1705325400000000000sample | — |
ISO 8601 iso_8601 · TIMESTAMP | %Y-%m-%dT%H:%M:%SZ | 2024-03-04T05:06:07Z | — |
ISO 8601 Compact iso_8601_compact · TIMESTAMP | %Y%m%dT%H%M%S | 20240304T050607 | — |
ISO 8601 with Microseconds and Offset iso_8601_micros_offset · TIMESTAMPTZ | %Y-%m-%dT%H:%M:%S.%f%z | 2024-03-04T05:06:07.123456+00 | — |
ISO 8601 with Microseconds iso_8601_microseconds · TIMESTAMP | %Y-%m-%dT%H:%M:%S.%fZ | 2024-03-04T05:06:07.123456Z | — |
ISO 8601 with Milliseconds and Offset iso_8601_millis_offset · TIMESTAMPTZ | %Y-%m-%dT%H:%M:%S.%g%z | 2024-03-04T05:06:07.123+00 | — |
ISO 8601 with Milliseconds iso_8601_milliseconds · TIMESTAMP | %Y-%m-%dT%H:%M:%S.%gZ | 2024-03-04T05:06:07.123Z | — |
ISO 8601 with Timezone Offset iso_8601_offset · TIMESTAMPTZ | %Y-%m-%dT%H:%M:%S%z | 2024-03-04T05:06:07+00 | — |
ISO Datetime with Microseconds (no timezone) iso_microseconds · TIMESTAMP | %Y-%m-%dT%H:%M:%S.%f | 2024-03-04T05:06:07.123456 | — |
ISO Datetime with Milliseconds (no timezone) iso_milliseconds · TIMESTAMP | %Y-%m-%dT%H:%M:%S.%g | 2024-03-04T05:06:07.123 | — |
ISO Datetime, minute precision (no timezone) iso_minutes · TIMESTAMP | %Y-%m-%dT%H:%M | 2024-03-04T05:06 | — |
ISO Datetime, second precision (no timezone) iso_seconds · TIMESTAMP | %Y-%m-%dT%H:%M:%S | 2024-03-04T05:06:07 | — |
ISO with Space Separator and Z iso_space_zulu · TIMESTAMP | %Y-%m-%d %H:%M:%SZ | 2024-03-04 05:06:07Z | — |
MDY 12-Hour Datetime mdy_12h · TIMESTAMP | %m/%d/%Y %I:%M %p | 03/04/2024 05:06 AM | — |
MDY 24-Hour Datetime mdy_24h · TIMESTAMP | %m/%d/%Y %H:%M:%S | 03/04/2024 05:06:07 | — |
PostgreSQL Short Offset pg_short_offset · TIMESTAMPTZ | %Y-%m-%d %H:%M:%S.%f%z | 2024-03-04 05:06:07.123456+00 | — |
RFC 2822 rfc_2822 · TIMESTAMPTZ | %a, %d %b %Y %H:%M:%S %z | Mon, 04 Mar 2024 05:06:07 +00 | — |
RFC 2822 with Ordinal Indicators rfc_2822_ordinal · TIMESTAMPTZ | — | 15th Jan 2024 10:30:00 +0000sample | — |
RFC 3339 (Space Separator) rfc_3339 · TIMESTAMPTZ | %Y-%m-%d %H:%M:%S%z | 2024-03-04 05:06:07+00 | — |
Slash YMD Timestamp (24h) slash_ymd_24h · TIMESTAMP | %Y/%m/%d %H:%M:%S | 2024/03/04 05:06:07 | — |
SQL Timestamp with Microseconds sql_microseconds · TIMESTAMP | %Y-%m-%d %H:%M:%S.%f | 2024-03-04 05:06:07.123456 | — |
SQL Timestamp with Milliseconds sql_milliseconds · TIMESTAMP | %Y-%m-%d %H:%M:%S.%g | 2024-03-04 05:06:07.123 | — |
SQL Minute-Precision Datetime sql_minutes · TIMESTAMP | %Y-%m-%d %H:%M | 2024-03-04 05:06 | — |
SQL Standard sql_standard · TIMESTAMP | %Y-%m-%d %H:%M:%S | 2024-03-04 05:06:07 | — |
BSD Syslog (RFC 3164) syslog_bsd · VARCHAR | %b %-d %H:%M:%S | Mar 4 05:06:07 | — |
Which timestamp type is it?
1 of 31 timestamp types accepts it.
| Type | Reads it as |
|---|---|
PostgreSQL Short Offset pg_short_offset | 2024-03-04 05:06:07.123456+00 |
Use it
CLI · DuckDB
$ printf '%s\n' '2024-03-04 05:06:07.123456+00' '2024-03-25 05:06:07.123456+00' > column.txt
$ finetype infer -f column.txt --mode column
SELECT ft_profile(v) FROM (VALUES ('2024-03-04 05:06:07.123456+00'), ('2024-03-25 05:06:07.123456+00')) t(v);After 2024-03-25 05:06:07.123456+00, one type remains: pg_short_offset.