From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | luc(dot)lavoie(at)usherbrooke(dot)ca |
Subject: | BUG #17573: timestamptz casting precision goes down as the year goes up |
Date: | 2022-08-04 21:12:37 |
Message-ID: | 17573-816a53478f1c08e2@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17573
Logged by: Luc Lavoie
Email address: luc(dot)lavoie(at)usherbrooke(dot)ca
PostgreSQL version: 14.4
Operating system: macOS 12.5
Description:
timestamptz casting on year 2022 values returns a precision of one
microsecond (as it is expected).
As the year goes up, the precision goes down as the script below will show
for years 2022, 23621, 2713, 4022, and 8022.
select
'Exact p = 1 microsecond' as "message",
to_timestamp (extract (epoch from timestamptz '2022-08-01 00:00:00.000000
+00:00')) as "2022-08-01 00:00:00.000000",
to_timestamp (extract (epoch from timestamptz '2022-08-01 00:00:00.000001
+00:00')) as "2022-08-01 00:00:00.000001",
to_timestamp (extract (epoch from timestamptz '2022-08-01 00:00:00.000002
+00:00')) as "2022-08-01 00:00:00.000002",
to_timestamp (extract (epoch from timestamptz '2022-08-01 00:00:00.000003
+00:00')) as "2022-08-01 00:00:00.000003" ;
select
'Error p ~ 2 microseconds' as "message",
to_timestamp (extract (epoch from timestamptz '2361-08-01 00:00:00.000000
+00:00')) as "2361-08-01 00:00:00.000000",
to_timestamp (extract (epoch from timestamptz '2361-08-01 00:00:00.000001
+00:00')) as "2361-08-01 00:00:00.000001",
to_timestamp (extract (epoch from timestamptz '2361-08-01 00:00:00.000002
+00:00')) as "2361-08-01 00:00:00.000002",
to_timestamp (extract (epoch from timestamptz '2361-08-01 00:00:00.000003
+00:00')) as "2361-08-01 00:00:00.000003";
select
'Error p ~ 4 microseconds' as "message",
to_timestamp (extract (epoch from timestamptz '2713-08-01 00:00:00.000000
+00:00')) as "2713-08-01 00:00:00.000000",
to_timestamp (extract (epoch from timestamptz '2713-08-01 00:00:00.000001
+00:00')) as "2713-08-01 00:00:00.000001",
to_timestamp (extract (epoch from timestamptz '2713-08-01 00:00:00.000002
+00:00')) as "2713-08-01 00:00:00.000002",
to_timestamp (extract (epoch from timestamptz '2713-08-01 00:00:00.000003
+00:00')) as "2713-08-01 00:00:00.000003";
select
'Error p ~ 8 microseconds' as "message",
to_timestamp (extract (epoch from timestamptz '4022-08-01 00:00:00.000000
+00:00')) as "4022-08-01 00:00:00.000000",
to_timestamp (extract (epoch from timestamptz '4022-08-01 00:00:00.000010
+00:00')) as "4022-08-01 00:00:00.000010",
to_timestamp (extract (epoch from timestamptz '4022-08-01 00:00:00.000020
+00:00')) as "4022-08-01 00:00:00.000020",
to_timestamp (extract (epoch from timestamptz '4022-08-01 00:00:00.000030
+00:00')) as "4022-08-01 00:00:00.000030";
select
'Error p ~ 32 microseconds' as "message",
to_timestamp (extract (epoch from timestamptz '8022-08-01
00:00:00.000000')) as "8022-08-01 00:00:00.000000",
to_timestamp (extract (epoch from timestamptz '8022-08-01
00:00:00.000010')) as "8022-08-01 00:00:00.000010",
to_timestamp (extract (epoch from timestamptz '8022-08-01
00:00:00.000020')) as "8022-08-01 00:00:00.000020",
to_timestamp (extract (epoch from timestamptz '8022-08-01
00:00:00.000030')) as "8022-08-01 00:00:00.000030";
Results obtained
+-----------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|message |2022-08-01 00:00:00.000000 |2022-08-01
00:00:00.000001 |2022-08-01 00:00:00.000002 |2022-08-01
00:00:00.000003 |
+-----------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|Exact p = 1 microsecond|2022-08-01 00:00:00.000000 +00:00|2022-08-01
00:00:00.000001 +00:00|2022-08-01 00:00:00.000002 +00:00|2022-08-01
00:00:00.000003 +00:00|
+-----------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
+------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|message |2361-08-01 00:00:00.000000 |2361-08-01
00:00:00.000001 |2361-08-01 00:00:00.000002 |2361-08-01
00:00:00.000003 |
+------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|Error p ~ 2 microseconds|2361-08-01 00:00:00.000000 +00:00|2361-08-01
00:00:00.000002 +00:00|2361-08-01 00:00:00.000002 +00:00|2361-08-01
00:00:00.000004 +00:00|
+------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
+------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|message |2713-08-01 00:00:00.000000 |2713-08-01
00:00:00.000001 |2713-08-01 00:00:00.000002 |2713-08-01
00:00:00.000003 |
+------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|Error p ~ 4 microseconds|2713-08-01 00:00:00.000000 +00:00|2713-08-01
00:00:00.000000 +00:00|2713-08-01 00:00:00.000004 +00:00|2713-08-01
00:00:00.000004 +00:00|
+------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
+-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|message |4022-08-01 00:00:00.000000 |4022-08-01
00:00:00.000010 |4022-08-01 00:00:00.000020 |4022-08-01
00:00:00.000030 |
+-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|Error p ~ 8 microseconds|4022-08-01 00:00:00.000000 +00:00|4022-08-01
00:00:00.000008 +00:00|4022-08-01 00:00:00.000024 +00:00|4022-08-01
00:00:00.000032 +00:00|
+-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
+-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|message |8022-08-01 00:00:00.000000 |8022-08-01
00:00:00.000010 |8022-08-01 00:00:00.000020 |8022-08-01
00:00:00.000030 |
+-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
|Error p ~ 32 microseconds|8022-08-01 00:00:00.000000 +00:00|8022-08-01
00:00:00.000000 +00:00|8022-08-01 00:00:00.000032 +00:00|8022-08-01
00:00:00.000032 +00:00|
+-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-08-04 22:37:01 | Re: BUG #17573: timestamptz casting precision goes down as the year goes up |
Previous Message | David G. Johnston | 2022-08-04 18:14:53 | Re: BUG #17572: Different behaviour in different versions of postgresql details as in email |