BQ timestamp_trunc with tz

[tiboun]

Translating

SELECT
    TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00+00', DAY, 'UTC') AS utc,
    TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00+00', DAY, 'America/Los_Angeles') AS la;

outputs:

SELECT CAST(DATE_TRUNC('DAY', TIMESTAMPTZ '2008-12-25 15:30:00+00' AT TIME ZONE 'UTC') AS TIMESTAMPTZ) AS utc, CAST(DATE_TRUNC('DAY', TIMESTAMPTZ '2008-12-25 15:30:00+00' AT TIME ZONE 'America/Los_Angeles') AS TIMESTAMPTZ) AS la

The query output is not the same.

Bigquery:

"utc","la"
"2008-12-25 01:00:00.0","2008-12-25 09:00:00.0"

Duckdb

"utc","la"
"2008-12-25T00:00Z","2008-12-25T00:00Z"

I think that it should be translated to:

SELECT
	CAST(DATE_TRUNC('DAY',
	TIMESTAMPTZ '2008-12-25 15:30:00+00' AT TIME ZONE 'UTC') AS TIMESTAMPTZ) AS utc,
	DATE_TRUNC('DAY',
	TIMESTAMPTZ '2008-12-25 15:30:00+00' AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'America/Los_Angeles' AS la

Interesting test case may be:

SELECT
    TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00+00', MINUTE, 'UTC') AS utc,
    TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 21:30:00+00', DAY, 'America/Los_Angeles') AS la,
    TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 21:45:30+00', DAY, 'Asia/Kolkata') AS truncated_hour,
    TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 21:45:30+00', DAY, 'Europe/Paris') AS truncated_hour_paris_winter,
    TIMESTAMP_TRUNC(TIMESTAMP '2008-07-25 21:45:30+00', DAY, 'Europe/Paris') AS truncated_hour_paris_summer,
    TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 18:30:30+00', MINUTE, 'Australia/Lord_Howe') AS truncated_minute_summer,
    TIMESTAMP_TRUNC(TIMESTAMP '2008-08-25 18:30:30+00', MINUTE, 'Australia/Lord_Howe') AS truncated_minute_winter;

I could not find something interesting related to minutes.

Output is:

[{
  "utc": "2008-12-25 15:30:00.000000 UTC",
  "la": "2008-12-25 08:00:00.000000 UTC",
  "truncated_hour": "2008-12-25 18:30:00.000000 UTC",
  "truncated_hour_paris_winter": "2008-12-24 23:00:00.000000 UTC",
  "truncated_hour_paris_summer": "2008-07-24 22:00:00.000000 UTC",
  "truncated_minute_summer": "2008-12-25 18:30:00.000000 UTC",
  "truncated_minute_winter": "2008-08-25 18:30:00.000000 UTC"
}]