Select by DateTime

[romanmatyus]

Hello,

I use this repository method:

$now = new DateTime;
return $this->findBy([
    'workShiftFrom<=' => $now,
    'workShiftTo>=' => $now,
])->orderBy('workShiftFrom', ICollection::ASC);

On localhost i receive correct SQL, but on production server is time modified -2 hours.

localhost

SELECT `work_days`.* FROM `work_days` AS `work_days`
WHERE (`work_days`.`work_shift_from` <= '2018-07-17 06:28:48.771743') AND
(`work_days`.`work_shift_to` >= '2018-07-17 06:28:48.771743')
ORDER BY `work_days`.`work_shift_from`

production

SELECT `work_days`.* FROM `work_days` AS `work_days`
WHERE (`work_days`.`work_shift_from` <= '2018-07-17 04:28:48.771743') AND
(`work_days`.`work_shift_to` >= '2018-07-17 04:28:48.771743')
ORDER BY `work_days`.`work_shift_from`

dbal:
    connectionTz: +00:00
    driver: mysqli

PHP: date.timezone = Europe/Bratislava

What all can be cause? I don't know how find problem :disappointed:

Thx

[hrach]

This is mainly "problem" in the Dbal and how it handles timezones. See docs (be aware changes in 3.0, so switch to 2.0, if needed) https://nextras.org/dbal/docs/3.0/datetime.

To see what's happening:

• what's your Orm version?
• what's is server's MySQL working timezone? (probably the same as the server's)

[romanmatyus]

• nextras/orm 3
• How I detect MySQL working timezone?

SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

return 2

Screenshot from php app:

Production database: Server version: 10.0.32-MariaDB-0+deb8u1 - (Debian)

@hrach Thanks

[hrach]

Oh, I see, I missed your config, you manually set the connection tz to offset 0.

Are you really sure you don't set this on localhost? IMO this is the issue.

(I guess you correctly use timestamp column type and Dbal converts the time to the connection working timezone, which is different to php's default - the diff is 2 hours.)

Also, please, check and provide info about the column type on server and on localhost. Eventually what returns SHOW FULL COLUMNS FROM work_days

[romanmatyus]

I'm sure, on production server it's connectionTz: +00:00. I try too auto, but it did not help.

On localhost (without problems) it's connectionTz: auto.

At 99%, I'm sure this is a hosting problem. But I can't identify where exactly. On localhosts and other hostings it's everything ok.

[hrach]

Ok, you use column without support for timezone handling. As for that, Dbal doesn't do any timezone handling for you during formatting Datetime. So the only questions is if the column is correctly detected as "datetime" (and gets dts modifier) here.

Also, if you set the "auto" for productions, what exactly is run in SET time_zone query??? Is it your PHP timezone?

[romanmatyus]

When I set auto for production, I receive Exception.

It's possible fix it without reconfiguring server? App running on shared hosting :disappointed:

[hrach]

For this we have auto-offset

[hrach]

So this behavior was pretty expected since you were using different connection timezone.