Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

feat(expr): fix PostgreSQL timestamptz subtraction error on Daylight Saving #12829

Open
xiangjinwu opened this issue Oct 13, 2023 · 1 comment

Comments

@xiangjinwu
Copy link
Contributor

https://github.com/postgres/postgres/blob/REL_16_0/src/backend/utils/adt/timestamp.c#L2714

	 *	This is wrong, but removing it breaks a lot of regression tests.
	 *	For example:
	 *
	 *	test=> SET timezone = 'EST5EDT';
	 *	test=> SELECT
	 *	test-> ('2005-10-30 13:22:00-05'::timestamptz -
	 *	test(>	'2005-10-29 13:22:00-04'::timestamptz);
	 *	?column?
	 *	----------------
	 *	 1 day 01:00:00
	 *	 (1 row)
	 *
	 *	so adding that to the first timestamp gets:
	 *
	 *	 test=> SELECT
	 *	 test-> ('2005-10-29 13:22:00-04'::timestamptz +
	 *	 test(> ('2005-10-30 13:22:00-05'::timestamptz -
	 *	 test(>  '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
	 *		timezone
	 *	--------------------
	 *	2005-10-30 14:22:00
	 *	(1 row)

The idea is to introduce a correct expr variant timestamptz_sub (any internal name), and a session config DST_BUGFIX (visible to user, name TBD):

  • When DST_BUGFIX is set to false (or postgresql), the frontend binds - operator between 2 timestamptz to existing variant subtract.
  • When DST_BUGFIX is set to true (or precise), the frontend binds to the new variant timestamptz_sub.
PostgreSQL-compatible variant new variant
'2005-10-30 13:22:00-05'::timestamptz - '2005-10-29 13:22:00-04'::timestamptz 1 day 01:00:00 25:00:00
'2005-04-03 13:22:00-04'::timestamptz - '2005-04-02 13:22:00-05'::timestamptz 23:00:00 23:00:00

In this way, the interval returned always have no month or day. It also allows lossless transformation into Duration in rust and protobuf.

Additionally, tumble / hop shall also bind to either compatible variant or strict variant based on the session config.

Just writing the idea down without plan to implement it soon. We may prioritize it if we get any complains after the upcoming 25-hour day (Oct 29 in Europe and Nov 5 in the US)

@xiangjinwu xiangjinwu self-assigned this Oct 13, 2023
@github-actions github-actions bot added this to the release-1.4 milestone Oct 13, 2023
@xiangjinwu xiangjinwu removed this from the release-1.4 milestone Oct 13, 2023
Copy link
Contributor

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant