pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17573: timestamptz casting precision goes down as the year goes up
2+ messages / 2 participants
[nested] [flat]
* BUG #17573: timestamptz casting precision goes down as the year goes up
@ 2022-08-04 21:12 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2022-08-04 21:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: luc.lavoie@usherbrooke.ca
The following bug has been logged on the website:
Bug reference: 17573
Logged by: Luc Lavoie
Email address: luc.lavoie@usherbrooke.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|
+-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17573: timestamptz casting precision goes down as the year goes up
@ 2022-08-04 22:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-08-04 22:37 UTC (permalink / raw)
To: luc.lavoie@usherbrooke.ca; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
The issue here is not with the timestamp, nor even with the extract().
It's with your use of to_timestamp(), which takes float8, which has
limited precision. The precision is disappearing in the implicit
cast to float8.
Prior to v14, there was also an issue with extract() returning float8,
but nowadays it returns numeric. If you look directly at the extract()
results you'll see no precision is lost.
Maybe we should invent a variant of to_timestamp that takes numeric, but
the use-case seems pretty niche ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-08-04 22:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-04 21:12 BUG #17573: timestamptz casting precision goes down as the year goes up PG Bug reporting form <noreply@postgresql.org>
2022-08-04 22:37 ` Tom Lane <tgl@sss.pgh.pa.us>
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