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

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

Aleksander Alekseev <aleksander@timescale.com> writes:
> =# set datestyle to 'Postgres';
> SET
> =# 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"

> If I understand correctly, text->timestamptz doesn't understand the
> 'LMT' timezone.

So it seems.  In a quick experiment, it seemed like just treating LMT
as a noise word on input might be enough, but I don't have time today
to poke at it further.

            regards, tom lane

Hi Tom,

> > If I understand correctly, text->timestamptz doesn't understand the
> > 'LMT' timezone.
>
> So it seems.  In a quick experiment, it seemed like just treating LMT
> as a noise word on input might be enough, but I don't have time today
> to poke at it further.

Thanks for the hint. I've found the corresponding piece of code and
will submit a proper patch shortly.

-- 
Best regards,
Aleksander Alekseev