From: | Aleksander Alekseev <aleksander(at)timescale(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | TimestampTz->Text->TimestampTz casting fails with DateStyle 'Postgres' |
Date: | 2024-11-07 08:32:30 |
Message-ID: | CAJ7c6TOATjJqvhnYsui0=CO5XFMF4dvTGH+skzB--jNhqSQu5g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi hackers,
We discovered one strange edge case with TimestampTz:
```
=# set datestyle to 'Postgres';
SET
=# SELECT '1000-01-01'::timestamptz::text;
text
------------------------------
Wed Jan 01 00:00:00 1000 LMT
=# SELECT '1000-01-01'::timestamptz::text::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "Wed
Jan 01 00:00:00 1000 LMT"
```
When DateStyle is set to 'ISO' everything works fine:
```
=# set datestyle to 'ISO';
SET
=# SELECT '1000-01-01'::timestamptz::text;
text
------------------------------
1000-01-01 00:00:00+02:30:17
=# SELECT '1000-01-01'::timestamptz::text::timestamptz;
timestamptz
------------------------------
1000-01-01 00:00:00+02:30:17
```
If I understand correctly, text->timestamptz doesn't understand the
'LMT' timezone. Until 1879 it doesn't work, but in 1880 we switch to
'MMT' and then it works:
```
eax=# SELECT '1879-01-01'::timestamptz::text::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "Wed
Jan 01 00:00:00 1879 LMT"
eax=# SELECT '1880-01-01'::timestamptz::text::timestamptz;
timestamptz
------------------------------
Wed Dec 31 20:00:17 1879 LMT
(1 row)
eax=# SELECT '1880-01-01'::timestamptz::text;
text
------------------------------
Thu Jan 01 00:00:00 1880 MMT
```
It seems to me that in the first case we should either accept "Wed Jan
01 00:00:00 1000 LMT" (since we just generated it) or alternatively
produce something else when casting timestamptz to text.
Thoughts?
--
Best regards,
Aleksander Alekseev
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2024-11-07 09:10:31 | BUG #18694: DISCARD ALL does not reset execution counters for plpgsql functions |
Previous Message | Tender Wang | 2024-11-07 06:47:42 | Re: BUG #18692: Segmentation fault when extending a varchar column with a gist index with custom signal length |