GlareDB
why is microseconds the default for `timestamp` fields?
Description
> create table timestamps (t timestamp);
Table created
> describe timestamps;
┌─────────────┬────────────────────┬─────────────┐
│ column_name │ data_type │ is_nullable │
│ ── │ ── │ ── │
│ Utf8 │ Utf8 │ Boolean │
╞═════════════╪════════════════════╪═════════════╡
│ t │ Timestamp<µs, UTC> │ true │
└─────────────┴────────────────────┴─────────────┘
I'd kind of expect it to be nanoseconds considering our usage of nanos elsewhere
Mainly because it's the default for Postgres.
| Name | Storage Size | Description | Low Value | High Value | Resolution |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond |
| timestamp [ (p) ] with time zone | 8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond |
| date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
| time [ (p) ] [ without time zone ] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond |
| time [ (p) ] with time zone | 12 bytes | time of day (no date), with time zone | 00:00:00+1559 | 24:00:00-1559 | 1 microsecond |
| interval [ fields ] [ (p) ] | 16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
- For those of you playing along at home, Janurary 1, 4713 BC is the epoch of the current Julian period used in Julian Day Number system.
- @vrongmeal I suspect that the second row of this chart is incorrect, given that the timezone should take up some amount of space.
@vrongmeal I suspect that the second row of this chart is incorrect, given that the timezone should take up some amount of space.
Postgres stores timezone info in the datatype and the value consists of a UTC timestamp. So this is OK.
Postgres stores timezone info in the datatype and the value consists of a UTC timestamp. So this is OK.
Indeed!
Looks like Postgres stores everything with a timestamp, and the "without timetamp" variant of the type is just "timestamp ignored" and exists for formal compliance with the SQL standard... (so "no timestamp" is just "UTC").