TimestampTz->Text->TimestampTz casting fails with DateStyle 'Postgres'

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

Responses

Browse pgsql-bugs by date

  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