Special handling for timestamp and timestamptz 'infinity'

[Loreith]

What happens?

When using the postgres scanner on a tuple that contains an 'infinity' value in a TIMESTAMPTZ column, the value returned is "290273-12-10 (BC) 06:11:13.224191+10:12" (Not sure why my timezone is +10:12). The same result happens when setting duckdb TimeZone to UTC, though obviously with a different displayed value.

Postgres implementation: https://github.com/postgres/postgres/blob/master/src/include/datatype/timestamp.h#L150C28-L150C40
Duckdb implementation: https://github.com/duckdb/duckdb/blob/master/src/include/duckdb/common/types/timestamp.hpp#L71

To Reproduce

On postgres (reproduced locally and on remote postgres server running postgres 14.5 on rds graviton)

create table thing_state (
thing_id int not null,
valid_from timestamptz not null,
valid_till timestamptz not null,
unique(thing_id, valid_from)
);
insert into thing_state (thing_id, valid_from, valid_till) select 1, now(), 'infinity'::timestamptz;

In duckdb:

load postgres;
create schema pg;
call postgres_attach('dbname=test', sink_schema=pg);
select * from pg.thing_state;

OS:

MacOS 13.4.1

PostgreSQL Version:

14.8

DuckDB Version:

0.8.1

DuckDB Client:

CLI

Full Name:

Wren

Affiliation:

Australian Greens

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[Loreith]

Maybe also useful:

Postgres' definition of int64 max/min : https://doxygen.postgresql.org/c_8h_source.html#l00576

Unfortunately I don't have any C++ dev stuff set up and the last thing I built in it was a segfaulting Uni assignment, but hopefully that's enough information for this to be an easy thing to action! Please let me know if I can help in any other way

[Mytherin]

Thanks for the report! This should be fixed now in #111.