I try to do time calculation in SQL on windows XAMP running PHP 8.x and MariaDB 10.x. I need to get the seconds between two datetimes.
SELECT
TIME_TO_SEC(TIMEDIFF(NOW(),'2023-12-05 21:19:22')) AS td,
TIMESTAMPDIFF(SECOND,NOW(),'2023-12-05 21:19:22') AS tsd
The ‘2023-12-05 21:19:22’ part is the fixed time component which start as now. Every time I run this query it gives me the difference in seconds of the fixed datetime and NOW();
Both calculation give te right amount of seconds.
But at a random point de difference will de off by thousands of seconds. It looks like the time retrieved is sometimes daylight savingtime and sometimes not?
Is this a known bug in the combinations of software?
If so than I expect that this will run fine on the production server (linux)
Is there another method I can use?
2
Answers
I think this behaviour is due to the timeserver. It sometimes gives time with daylight saving and some times without. In PHP and mysql I defined the default_time_zone. And hope this will solve the problem.
It did not solve the problem, 2 datetime entered witin minutes:
2023-12-05 22:00:21
2023-12-05 23:57:27
I think its solved now, NTP service on ESXI was stopped. The XAMPP machine was running in vmware and was syncing with te Host. Bizarre how this influinced down the chain.