RFC 9557 / IXDTF

On 11/16/25 11:43, pgmis@posteo.net wrote:

Hello,

does postgres consider some sort of implementation of rfc9557 IXDTF?
This is new time internet time format which extends timestamps with
timezone info together with the offset
- https://datatracker.ietf.org/doc/html/rfc9557
- https://en.wikipedia.org/wiki/RFC_9557#Usage

If not, is there some suggestion on how to best store information like
this?

select '1996-12-19T16:39:57-08:00[America/Los_Angeles]'::timestamp;
ERROR: invalid input syntax for type timestamp:
"1996-12-19T16:39:57-08:00[America/Los_Angeles]"

select '1996-12-19T16:39:57-08:00[America/Los_Angeles]'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone:
"1996-12-19T16:39:57-08:00[America/Los_Angeles]"

select '1996-12-19T16:39:57-08:00[America/Los_Angeles]'::varchar;
varchar
------------------------------------------------
1996-12-19T16:39:57-08:00[America/Los_Angeles]

Your only option at this point would be to store as a string. That means
though you lose out on using any of the Postgres datetime operators or
functions, unless you do some regex to pull out the timezone portion and
then use that with the datetime string to create a timestamp or
timestamptz. I could see that being wrapped into a function.

Thanks,
Miroslav

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, 17 Nov 2025 at 08:44, <pgmis@posteo.net> wrote:

If not, is there some suggestion on how to best store information like
this?

You could just store the time zone name separately, e.g:

create table ts (ts timestamptz, tz text);
insert into ts values(now(), 'America/Los_Angeles');
select ts at time zone tz from ts;
timezone
----------------------------
2025-11-16 13:18:14.075491

David

pgmis@posteo.net writes:

does postgres consider some sort of implementation of rfc9557 IXDTF?
This is new time internet time format which extends timestamps with
timezone info together with the offset
- https://datatracker.ietf.org/doc/html/rfc9557
- https://en.wikipedia.org/wiki/RFC_9557#Usage

It's not on the radar at the moment. If this standard gets any
real uptake (too soon to tell), somebody might build an extension
datatype that works with it.

AFAICT from a quick read of 9557, the intent is precisely to represent
*more* information than just a point in time, or even just a point in
time and an intended timezone. So IMO it wouldn't make any sense for
the built-in timestamptz type to deal with this. Yeah we could make
timestamptz_in read this format, but it'd be throwing away all of the
additional information over an RFC 3339 timestamp, which seems like
not the right thing.

regards, tom lane