bigquery hour timestamp_diff

[tiboun]

Current transpilation of

SELECT
    TIMESTAMP('2010-07-07 10:20:00+00') AS later_timestamp,
    TIMESTAMP('2008-12-25 15:30:00+00') AS earlier_timestamp,
    TIMESTAMP_DIFF(TIMESTAMP '2010-07-07 10:20:00+00', TIMESTAMP '2008-12-25 15:30:00+00', HOUR) AS hours;

is great and gives:

SELECT
	Cast('2010-07-07 10:20:00+00' AS TIMESTAMPTZ) AS later_timestamp,
	Cast('2008-12-25 15:30:00+00' AS TIMESTAMPTZ) AS earlier_timestamp,
	DATE_DIFF('HOUR',
	TIMESTAMPTZ '2008-12-25 15:30:00+00',
	TIMESTAMPTZ '2010-07-07 10:20:00+00') AS hours;

But the output is not the same, 13411 vs 13410.

In order to solve this issue, it is required to use MINUTES diff. Here is the difference.

SELECT
	Cast('2010-07-07 10:20:00+00' AS TIMESTAMPTZ) AS later_timestamp,
	Cast('2008-12-25 15:30:00+00' AS TIMESTAMPTZ) AS earlier_timestamp,
	DATE_DIFF('HOUR',
	TIMESTAMPTZ '2008-12-25 15:30:00+00',
	TIMESTAMPTZ '2010-07-07 10:20:00+00') AS hours,
	cast(FLOOR(DATE_DIFF('MINUTE',
	TIMESTAMPTZ '2008-12-25 15:30:00+00',
	TIMESTAMPTZ '2010-07-07 10:20:00+00')/60) as int) AS hours

In bigquery's page, we can notice that they state hour diff as equivalent to 60 minutes.

They state the same for a day which contains 24 hours. I suspect DST to be the problem.

In fact, given this query

SELECT TIMESTAMP_DIFF(TIMESTAMP '2024-07-07 10:20:00+00', TIMESTAMP '1900-12-25 15:30:00+00', DAY) AS days;

we get 45119

If we transpile blindly we can notice that the output is wrong the same way

SELECT
	Cast('2010-07-07 10:20:00+00' AS TIMESTAMPTZ) AS later_timestamp,
	Cast('2008-12-25 15:30:00+00' AS TIMESTAMPTZ) AS earlier_timestamp,
	DATE_DIFF('DAY',
	TIMESTAMPTZ '1900-12-25 15:30:00+00',
	TIMESTAMPTZ '2024-07-07 10:20:00+00') AS hours,
	cast(FLOOR(DATE_DIFF('MINUTE',
	TIMESTAMPTZ '1900-12-25 15:30:00+00',
	TIMESTAMPTZ '2024-07-07 10:20:00+00')/60/24) as int) AS hours

Same technique to get the right result.