PostgreSQL - AT TIME ZONE isn't supported

OfekTeken commented 2 months ago

SQLDelight Version

2.0.2

SQLDelight Dialect

PostgreSQL

Describe the Bug

I'm trying to write a query which utilizes the AT TIME ZONE functionality.

SELECT CAST('2024-05-10T00:28:36+03' AS TIMESTAMPTZ) AT TIME ZONE 'America/Chicago';

If I were to query that, I would expect to get: 2024-05-09 16:28:36

But unfortunately, SQLDelight has a compile error:

',', <compound operator real>, FOR, FROM, GROUP, HAVING, LIMIT, OFFSET, ORDER or WHERE expected, got 'TIME'

Of course that my actual intent is to replace the 2024-05-10... and America/Chicago with actual selectable fields

Stacktrace

No response

griffio commented 2 months ago

I am looking into 🦅 and should be able to add some support for this

AT TIME ZONE is a binary operator resulting in a timestamp with/without time zone that is inverted
and zone is a valid zone identifier - effectively a text type

timestamp without time zone AT TIME ZONE zone → timestamp with time zone
timestamp with time zone AT TIME ZONE zone → timestamp without time 
*time with time zone AT TIME ZONE zone → time

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';

Binds As well as Timestamp column types and literals - it maybe possible to support bind arguments - as the types are TIMESTAMP|TIMESTAMPTZ and zone (Text)

SELECT CAST(? AS TIMESTAMPTZ) AT TIME ZONE ?;

*SqlDelight currently doesn't have a TIME WITH A TIMEZONE type that would map to java.OffsetTime

The function timezone(zone, timestamp) is equivalent to timestamp AT TIME ZONE zone - also not currently supported

OfekTeken commented 2 months ago

Hey @griffio, Thanks for this - Sounds interesting :)

cashapp / sqldelight