Обсуждение: Clarify: default precision on timestamps is 6
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) ```
On Friday, October 13, 2023, PG Doc comments form <noreply@postgresql.org> wrote:
both of them round any precision
beyond microseconds, and neither returns timestamps with greater precision
than the value that was inserted.
That is precisely what a no default with maximum of six means. If we say the default is six that would imply storage of less precise values pads significant zeros until there are six.
David J.
The expected behavior was not obvious to me. "By default, there is no explicit bound on precision" sounds more like "unlimited variable length", as on the 'text' character type.
"By default, the bound on precision is 6 (that is, microsecond precision)" conveys that 'timestamp with timezone' means exactly the same as 'timestamp(6) with timezone', which my experiments indicate is the case, but which was unclear to me, as was the expected behavior around rounding and padding or lack thereof.
-- Nathan
On Fri, Oct 13, 2023 at 10:32 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, October 13, 2023, PG Doc comments form <noreply@postgresql.org> wrote:both of them round any precision
beyond microseconds, and neither returns timestamps with greater precision
than the value that was inserted.That is precisely what a no default with maximum of six means. If we say the default is six that would imply storage of less precise values pads significant zeros until there are six.David J.
On Fri, Oct 13, 2023 at 7:32 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, October 13, 2023, PG Doc comments form <noreply@postgresql.org> wrote:both of them round any precision
beyond microseconds, and neither returns timestamps with greater precision
than the value that was inserted.That is precisely what a no default with maximum of six means. If we say the default is six that would imply storage of less precise values pads significant zeros until there are six.David J.
Not sure that last statement is correct. In 13 (only system I have access to at the moment) it doesn't look like casting to a precision greater than the value originally had causes any padding:
some_system=# select CURRENT_TIMESTAMP(0)::timestamp(6) with time zone;
current_timestamp
------------------------
2023-10-13 17:19:00+00
(1 row)
some_system=# select CURRENT_TIMESTAMP(0)::timestamp(6) with time zone;
current_timestamp
------------------------
2023-10-13 17:19:00+00
(1 row)
some_system=# select CURRENT_TIMESTAMP(1)::timestamp(6) with time zone;
current_timestamp--------------------------
2023-10-13 17:23:04.2+00
(1 row)
Kirk Parker <khp@equatoria.us> writes: > On Fri, Oct 13, 2023 at 7:32 AM David G. Johnston < > david.g.johnston@gmail.com> wrote: >> That is precisely what a no default with maximum of six means. If we say >> the default is six that would imply storage of less precise values pads >> significant zeros until there are six. > Not sure that last statement is correct. In 13 (only system I have access > to at the moment) it doesn't look like casting to a precision greater than > the value originally had causes any padding: The timestamp types don't have any explicit notion of precision (unlike, say, numeric). The stored value is an integer number of microseconds, nothing else. I've not checked the output function recently but it makes sense to me that it'd just drop trailing zeroes from the display, independently of any claimed precision for the column. Meanwhile, when casting a timestamp value to a declared precision, we handle that by just rounding off the microseconds count. This doesn't buy any space savings or anything like that, it's just for pro-forma compliance with the spec. I don't see anything particularly wrong with the existing docs. The limitation to 1-microsecond precision is spelled out in the table just above the para you quote. regards, tom lane