From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: him@nathanmlong.com
Subject: Clarify: default precision on timestamps is 6
Date: Fri, 13 Oct 2023 10:35:30 +0000
Message-ID: <169719333062.169913.3076985245923767377@wrigleys.postgresql.org> (raw)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/datatype-datetime.html
Description:
https://www.postgresql.org/docs/current/datatype-datetime.html says:
> time, timestamp, and interval accept an optional precision value p which
specifies the number of fractional digits retained in the seconds field. By
default, there is no explicit bound on precision. The allowed range of p is
from 0 to 6.
I think it would be more accurate to say "By default, the bound on precision
is 6 (that is, microsecond precision). The allowed range of p is from 0 to
6. Values with greater precision than the type allows will be rounded to its
maximum precision, and values with less precision than the type allows will
be stored as given."
The following experiment confirms that 'timestamp with time zone' behaves
exactly like 'timestamp(6) with time zone'; both of them round any precision
beyond microseconds, and neither returns timestamps with greater precision
than the value that was inserted.
```sql
# CREATE TABLE tstest (a timestamp with time zone, b timestamp(6) with time
zone, c timestamp(2) with time zone);
CREATE TABLE
# \d tstest
Table "public.tstest"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
a | timestamp with time zone | | |
b | timestamp(6) with time zone | | |
c | timestamp(2) with time zone | | |
-- microsecond precision
# INSERT INTO tstest (a, b, c) VALUES ('2024-01-01 00:00:00.123456Z',
'2024-01-01 00:00:00.123456Z', '2024-01-01 00:00:00.123456Z');
INSERT 0 1
-- nanosecond precision
# INSERT INTO tstest (a, b, c) VALUES ('2024-01-01 00:00:00.123456789Z',
'2024-01-01 00:00:00.123456789Z', '2024-01-01 00:00:00.123456789Z');
INSERT 0 1
-- millisecond precision
# INSERT INTO tstest (a, b, c) VALUES ('2024-01-01 00:00:00.123Z',
'2024-01-01 00:00:00.123Z', '2024-01-01 00:00:00.123Z');
INSERT 0 1
-- second precision
# INSERT INTO tstest (a, b, c) VALUES ('2024-01-01 00:00:00Z', '2024-01-01
00:00:00Z', '2024-01-01 00:00:00Z');
INSERT 0 1
--- results
# SELECT * FROM tstest;
a | b |
c
-------------------------------+-------------------------------+---------------------------
2024-01-01 00:00:00.123456+00 | 2024-01-01 00:00:00.123456+00 | 2024-01-01
00:00:00.12+00
2024-01-01 00:00:00.123457+00 | 2024-01-01 00:00:00.123457+00 | 2024-01-01
00:00:00.12+00
2024-01-01 00:00:00.123+00 | 2024-01-01 00:00:00.123+00 | 2024-01-01
00:00:00.12+00
2024-01-01 00:00:00+00 | 2024-01-01 00:00:00+00 | 2024-01-01
00:00:00+00
(4 rows)
```
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, him@nathanmlong.com
Subject: Re: Clarify: default precision on timestamps is 6
In-Reply-To: <169719333062.169913.3076985245923767377@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox