Какие могут быть подводные камни при установке часового пояса для сессии MySQL?

Question

[entermix]

Есть БД, время указывается UNIX TIMESTAMP, по умолчанию стоит системный часовой пояс (Europe/Moscow)

На входе приложения:
date_default_timezone_set('Europe/Moscow');

Все работает отлично, результаты выборки совпадают с реальными данными/временем. Появилась необходимость сделать возможность менять часовой пояс в зависимости от настроек пользователя, к примеру это Europe/Kiev:

На входе приложения:

date_default_timezone_set('Europe/Kiev');

DB::query(NULL, DB::query(NULL, 'SET time_zone = :tz'))
    ->parameters([
    ':tz' => 'Europe/Kiev',
])->execute();

$start = strtotime('this day midnight');
$end = strtotime('next day midnight -1 second')

При разных настройках генерируются разные условия для BETWEEN, но так ведь и должно быть? Результаты выборки во втором случае становятся непредсказуемыми:

SET time_zone = 'Europe/Moscow';
SELECT SUM(`visits`) AS `visits`, @@session.time_zone AS `tzx` FROM `statistics` WHERE `date` BETWEEN '1510693200' AND '1510779599'

visits 	tzx 	
67 	Europe/Moscow

SET time_zone = 'Europe/Kiev';
SELECT SUM(`visits`) AS `visits`, @@session.time_zone AS `tzx` FROM `statistics` WHERE `date` BETWEEN '1510696800' AND '1510783199'

visits 	tzx 	
NULL	Europe/Kiev

Пробовал так, но результаты выборки аналогичные тем, которые указаны выше:

SET time_zone = 'Europe/Moscow';
SELECT SUM(`visits`) AS `visits`, @@session.time_zone AS `tzx` FROM `statistics` WHERE `date` BETWEEN  UNIX_TIMESTAMP(FROM_UNIXTIME('1510693200', '%Y-%m-%d')) AND UNIX_TIMESTAMP(FROM_UNIXTIME('1510779599', '%Y-%m-%d'))

SET time_zone = 'Europe/Kiev';
SELECT SUM(`visits`) AS `visits`, @@session.time_zone AS `tzx` FROM `statistics` WHERE `date` BETWEEN UNIX_TIMESTAMP(FROM_UNIXTIME('1510696800', '%Y-%m-%d')) AND UNIX_TIMESTAMP(FROM_UNIXTIME('1510783199', '%Y-%m-%d'))

Так аналогично:

SET time_zone = 'Europe/Kiev';
SELECT SUM(`visits`) AS `visits`, @@session.time_zone AS `tzx` FROM `statistics` WHERE  UNIX_TIMESTAMP(FROM_UNIXTIME(`date`, '%Y-%m-%d')) BETWEEN UNIX_TIMESTAMP(FROM_UNIXTIME('1510696800', '%Y-%m-%d')) AND UNIX_TIMESTAMP(FROM_UNIXTIME('1510783199', '%Y-%m-%d'))

Структура таблицы:

CREATE TABLE IF NOT EXISTS `user_advert_statistics` (
  `date` int(10) unsigned NOT NULL,
  `visits` int(10) unsigned NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Таблица заполняется при помощи триггеров с часовым поясом по умолчанию
date = UNIX_TIMESTAMP(CURDATE());

Какие подводные камни могут быть? Куда копать?

Пробовал обновить tzdata, но это не помогает:

yum update tzdata
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

[root@user ~]# rpm -qa | grep mariadb 
mariadb-libs-5.5.44-2.el7.centos.x86_64
mariadb-devel-5.5.44-2.el7.centos.x86_64
mariadb-5.5.44-2.el7.centos.x86_64
mariadb-server-5.5.44-2.el7.centos.x86_64

Answer 1

[bkosun]

Можно конвертировать часовой пояс:

SELECT SUM(`visits`) AS `visits` FROM `statistics` WHERE `date` BETWEEN UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME('1510693200'), @@global.time_zone, @@session.time_zone)) AND UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME('1510779599'), @@global.time_zone, @@session.time_zone))

SELECT SUM(`visits`) AS `visits`  FROM `statistics` HAVING UNIX_TIMESTAMP(CONVERT_TZ(FROM_UNIXTIME(`date`), @@session.time_zone, @@global.time_zone)) BETWEEN '1510693200' AND '1510779599'

Но это не поможет поскольку при проектировании БД была допущена ошибка. Нужно хранить статистику почасово, а не посуточно, чтобы получилось так, как задумано.

Answer 2

[Boris Köln]

Поле

`date` int(10) unsigned

, а сравниваете со строками

`date` BETWEEN '1510693200' AND '1510779599'

.
Автоприведение типа, конечно, сработает, но без него гораздо лучше.

Comments

[entermix]

Да не, исключено. Первый запрос работает нормально даже с кавычками.