Clarify: default precision on timestamps is 6
От | PG Doc comments form |
---|---|
Тема | Clarify: default precision on timestamps is 6 |
Дата | |
Msg-id | 169719333062.169913.3076985245923767377@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Clarify: default precision on timestamps is 6
("David G. Johnston" <david.g.johnston@gmail.com>)
|
Список | pgsql-docs |
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) ```
В списке pgsql-docs по дате отправления: