Timestamp issue

[tiboun]

In bigquery, timestamp is implicitly set to UTC.

Given

WITH finishers AS
         (SELECT 'Sophia Liu' as name,
                 TIMESTAMP '2016-10-18 2:51:45' as finish_time,
                 'F30-34' as division
          UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
          UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
          UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
          UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
          UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
          UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
          UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
          UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
          UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
       finish_time,
       division,
       LAG(name)
           OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers
order by 3, 2, 1;

It gets translated to:

WITH finishers AS (SELECT 'Sophia Liu' AS name, TIMESTAMP '2016-10-18 2:51:45' AS finish_time, 'F30-34' AS division UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39' UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34' UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39' UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39' UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39' UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34' UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34' UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29' UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34') SELECT name, finish_time, division, LAG(name) OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner FROM finishers ORDER BY 3, 2, 1

But output differs.

Bigquery:

"name","finish_time","division","preceding_runner"
"Carly Forte","2016-10-18 05:08:58.0","F25-29","JSQL_NULL"
"Sophia Liu","2016-10-18 04:51:45.0","F30-34","JSQL_NULL"
"Nikki Leith","2016-10-18 04:59:01.0","F30-34","Sophia Liu"
"Jen Edwards","2016-10-18 05:06:36.0","F30-34","Nikki Leith"
"Meghan Lederer","2016-10-18 05:07:41.0","F30-34","Jen Edwards"
"Lauren Reasoner","2016-10-18 05:10:14.0","F30-34","Meghan Lederer"
"Lisa Stelzner","2016-10-18 04:54:11.0","F35-39","JSQL_NULL"
"Lauren Matthews","2016-10-18 05:01:17.0","F35-39","Lisa Stelzner"
"Desiree Berry","2016-10-18 05:05:42.0","F35-39","Lauren Matthews"
"Suzy Slane","2016-10-18 05:06:24.0","F35-39","Desiree Berry"

duckdb

"name","finish_time","division","preceding_runner"
"Carly Forte","2016-10-18 03:08:58.0","F25-29","JSQL_NULL"
"Sophia Liu","2016-10-18 02:51:45.0","F30-34","JSQL_NULL"
"Nikki Leith","2016-10-18 02:59:01.0","F30-34","Sophia Liu"
"Jen Edwards","2016-10-18 03:06:36.0","F30-34","Nikki Leith"
"Meghan Lederer","2016-10-18 03:07:41.0","F30-34","Jen Edwards"
"Lauren Reasoner","2016-10-18 03:10:14.0","F30-34","Meghan Lederer"
"Lisa Stelzner","2016-10-18 02:54:11.0","F35-39","JSQL_NULL"
"Lauren Matthews","2016-10-18 03:01:17.0","F35-39","Lisa Stelzner"
"Desiree Berry","2016-10-18 03:05:42.0","F35-39","Lauren Matthews"
"Suzy Slane","2016-10-18 03:06:24.0","F35-39","Desiree Berry"

Expected transpiled query:

WITH finishers AS (
SELECT
	'Sophia Liu' AS name,
	TIMESTAMPTZ '2016-10-18 2:51:45+00' AS finish_time,
	'F30-34' AS division
UNION ALL
SELECT
	'Lisa Stelzner',
	TIMESTAMPTZ '2016-10-18 2:54:11+00',
	'F35-39'
UNION ALL
SELECT
	'Nikki Leith',
	TIMESTAMPTZ '2016-10-18 2:59:01+00',
	'F30-34'
UNION ALL
SELECT
	'Lauren Matthews',
	TIMESTAMPTZ '2016-10-18 3:01:17+00',
	'F35-39'
UNION ALL
SELECT
	'Desiree Berry',
	TIMESTAMPTZ '2016-10-18 3:05:42+00',
	'F35-39'
UNION ALL
SELECT
	'Suzy Slane',
	TIMESTAMPTZ '2016-10-18 3:06:24+00',
	'F35-39'
UNION ALL
SELECT
	'Jen Edwards',
	TIMESTAMPTZ '2016-10-18 3:06:36+00',
	'F30-34'
UNION ALL
SELECT
	'Meghan Lederer',
	TIMESTAMPTZ '2016-10-18 3:07:41+00',
	'F30-34'
UNION ALL
SELECT
	'Carly Forte',
	TIMESTAMPTZ '2016-10-18 3:08:58+00',
	'F25-29'
UNION ALL
SELECT
	'Lauren Reasoner',
	TIMESTAMPTZ '2016-10-18 3:10:14+00',
	'F30-34')
SELECT
	name,
	finish_time,
	division,
	LAG(name) OVER (PARTITION BY division
ORDER BY
	finish_time ASC) AS preceding_runner
FROM
	finishers
ORDER BY
	3,
	2,
	1

Notice that we changed TIMESTAMP to TIMESTAMPTZ by suffixing timestamp with UTC offset.

[tiboun]

Same for

SELECT MAKE_TIMESTAMP(1230219000000000) AS timestamp_value

Expected query is

SELECT MAKE_TIMESTAMP(1230219000000000) AT TIME ZONE 'UTC' AS timestamp_value

Bigquery output is:

"timestamp_value"
"2008-12-25 16:30:00.0"

whereas duckdb is:

"timestamp_value"
"2008-12-25 15:30:00.0"

[tiboun]

Same applies for epoch_ms / epoch_us / epoch when no timezone is provided

[tiboun]

SELECT PARSE_TIMESTAMP('%c', 'Thu Dec 25 07:30:00 2008') AS parsed;

should be transpiled as

SELECT CAST(strptime('Thu Dec 25 07:30:00 2008', '%a %b %-d %-H:%M:%S %Y') AS TIMESTAMP) AT TIME ZONE 'UTC' AS parsed

[tiboun]

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%SZ', TIMESTAMP '2050-12-25 15:30:55', 'UTC') AS formatted,
  FORMAT_TIMESTAMP('%Y-%m-%dT%H:%M:%SZ', TIMESTAMP '2050-12-25 15:30:55', 'Europe/Paris') AS paris;

should be transpiled as

SELECT StrfTime(TIMESTAMPTZ '2050-12-25 15:30:55+00' AT TIME ZONE 'UTC', '%Y-%m-%dT%H:%M:%SZ') AS formatted, StrfTime(TIMESTAMPTZ '2050-12-25 15:30:55+00' AT TIME ZONE 'Europe/Paris', '%Y-%m-%dT%H:%M:%SZ') AS paris