BUG #17573: timestamptz casting precision goes down as the year goes up
От | PG Bug reporting form |
---|---|
Тема | BUG #17573: timestamptz casting precision goes down as the year goes up |
Дата | |
Msg-id | 17573-816a53478f1c08e2@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17573: timestamptz casting precision goes down as the year goes up
|
Список | pgsql-bugs |
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| +-------------------------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+
В списке pgsql-bugs по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: BUG #17572: Different behaviour in different versions of postgresql details as in email
Следующее
От: Tom LaneДата:
Сообщение: Re: BUG #17573: timestamptz casting precision goes down as the year goes up