What are the pitfalls when setting the timezone for a MySQL session?

Question

PHP

There is a database, the time is specified by UNIX TIMESTAMP, the default is the system time zone (Europe / Moscow)
At the input of the application: Everything works fine, the sampling results match the real data / time. There was a need to make it possible to change the time zone depending on the user's settings, for example, this is Europe / Kiev: At the entrance of the application:
date_default_timezone_set('Europe/Moscow');

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')

Different settings generate different conditions for BETWEEN, but isn't that how it's supposed to be? The sample results in the second case become unpredictable:

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

Tried like this, but the sample results are similar to those listed above:

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'))

So similarly:

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'))

Table structure:

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;

The table is populated using triggers with the default time zone
date = UNIX_TIMESTAMP(CURDATE());
What are the possible pitfalls? Where to dig?
Tried to update tzdata but it doesn't help:

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

[[email protected] ~]# 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

You can convert the time zone:
But this will not help because a mistake was made in the design of the database. You need to store statistics by the hour, not by the day, so that it works out as intended.

Answer 2

Поле

`date` int(10) unsigned

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

`date` BETWEEN '1510693200' AND '1510779599'

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