Recent test depends from local host timezone and fails if it is not UTC

[klirichek]

Bug Description:

clause from test 446, pushed in 973703b8

    INSERT into rt1 (id, tid) values (1, 1568577600), (2, '2019-10'),(3, '2019-10-15T20:00');
    SELECT id, tid, date_format(tid, '%Y-%m-%dT%H:%M') as d from rt1 order by id asc;

expected result is

sphinxql-31> SELECT id, tid, date_format(tid, '%Y-%m-%dT%H:%M') as d from rt1 order by id asc;
	id	tid	d
	1	1568577600	2019-09-15T20:00
	2	1569888000	2019-10-01T00:00
	3	1571169600	2019-10-15T20:00
3 rows in set

real result is

sphinxql-31> SELECT id, tid, date_format(tid, '%Y-%m-%dT%H:%M') as d from rt1 order by id asc;
	id	tid	d
	1	1568577600	2019-09-16T03:00
	2	1569888000	2019-10-01T07:00
	3	1571169600	2019-10-16T03:00
3 rows in set

The most prominent is that just inserted 2019-10-15T20:00 immediately got parsed as 2019-10-16T03:00, i.e. 7 hours later. Looks, like date_format implicitly uses local timezone or something like.

While it is possible to suppress such difference by assigning a timezone, the fact that parse/render are not synced together and produces different result looks not very friendly. I would at least expect that just inserted value will be rendered the same way to produce same result as inserted.

Manticore Search Version:

since 973703b8

Operating System Version:

linux, macos

Have you tried the latest development version?

• [X] Yes

Internal Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

• [ ] Task estimated
• [ ] Specification created, reviewed, and approved
• [ ] Implementation completed
• [ ] Tests developed
• [x] Documentation updated
• [x] Documentation proofread
• [ ] Changelog updated

[sanikolaev]

Looks like it considers the entered time is in UTC instead of converting it to UTC internally from the current time zone.

MRE:

mysql> set global timezone='Asia/Novosibirsk'; drop table if exists rt1; create table rt1(tid timestamp); INSERT into rt1 (id, tid) values (3, '2022-01-01T00:00'); SELECT id, date_format(tid, '%Y-%m-%dT%H:%M') from rt1;
--------------
set global timezone='Asia/Novosibirsk'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists rt1
--------------

Query OK, 0 rows affected (0.08 sec)

--------------
create table rt1(tid timestamp)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
INSERT into rt1 (id, tid) values (3, '2022-01-01T00:00')
--------------

Query OK, 1 row affected (0.00 sec)

--------------
SELECT id, date_format(tid, '%Y-%m-%dT%H:%M') from rt1
--------------

+------+------------------------------------+
| id   | date_format(tid, '%Y-%m-%dT%H:%M') |
+------+------------------------------------+
|    3 | 2022-01-01T07:00                   |
+------+------------------------------------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---

[sanikolaev]

Same with hour():

select hour(tid) from rt1
--------------

+-----------+
| hour(tid) |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

[sanikolaev]

Another failing test is col_434 when run in the columnar mode - https://github.com/manticoresoftware/columnar/runs/24873252849

[glookka]

These are actually two unrelated issues. One issue is using UTC instead on local timezone on inserts and another issue is treating quoted timestamps as strings that need to be parsed via cctz

[glookka]

Fixed in f6af49ba12cd36892b12135569dfc3369038434a