Incorrect responses with timezone offsets that are not whole hours #27079
Description
You have to provide the following information whenever possible.
Describe the bug
Appeared at #26978 CI (random timezone)
If the timezone offset isn't a whole hour, like Africa/Monrovia which used to be -2670 ( -0:44:30), several functions like formatDateTime or toStartOfMinute won't work correctly.
Does it reproduce on recent release?
Yes, and probably all stable releases are affected.
How to reproduce
WITH toDateTime('1970-06-17 07:39:21', 'Africa/Monrovia') AS t
SELECT
toUnixTimestamp(t),
timeZoneOffset(t),
formatDateTime(t, '%F %T', 'Africa/Monrovia'),
toString(t, 'Africa/Monrovia'),
toStartOfMinute(t),
toStartOfFiveMinute(t),
toStartOfFifteenMinutes(t),
toStartOfTenMinutes(t),
toStartOfHour(t)
FORMAT Vertical
Query id: 8c580961-9679-406f-8555-5d0ad5a29d36
Row 1:
──────
toUnixTimestamp(t): 14459031
timeZoneOffset(t): -2670
formatDateTime(t, '%F %T', 'Africa/Monrovia'): 1970-06-17 07:39:51
toString(t, 'Africa/Monrovia'): 1970-06-17 07:39:21
toStartOfMinute(t): 1970-06-17 07:38:30
toStartOfFiveMinute(t): 1970-06-17 07:35:30
toStartOfFifteenMinutes(t): 1970-06-17 07:30:30
toStartOfTenMinutes(t): 1970-06-17 07:30:00
toStartOfHour(t): 1970-06-17 07:00:00
1 rows in set. Elapsed: 0.002 sec.
Expected behavior
toUnixTimestamp(t): 14459031
timeZoneOffset(t): -2670
formatDateTime(t, '%F %T', 'Africa/Monrovia'): 1970-06-17 07:39:21
toString(t, 'Africa/Monrovia'): 1970-06-17 07:39:21
toStartOfMinute(t): 1970-06-17 07:39:00
toStartOfFiveMinute(t): 1970-06-17 07:35:00
toStartOfFifteenMinutes(t): 1970-06-17 07:30:00
toStartOfTenMinutes(t): 1970-06-17 07:30:00
toStartOfHour(t): 1970-06-17 07:00:00
It seems that DateLut needs to take into account that offsets can change not only full hours, but minutes and even seconds. I'll create a PR with some suggested changes to improve the situation.