This is how my profile looks like. Every field is a date field without the time part, and they're set to the 25th of each month.
The problem is on the DB side, FROM_UNIXTIME doesn't give me the desired results.
I know that FROM_UNIXTIME default is to return the date in the server timezone, but I did override it to use UTC.
It seems like the timestamps moodle writes to the database are not UTC/GMT values, and they differ by daylight savings.
Hi David,
I know that FROM_UNIXTIME default is to return the date in the server timezone, but I did override it to use UTC.
How did you do it?
You need to properly set timezone in PHP and then playing with Moodle configuration setting, timezone in /admin/settings.php?section=locationsettings, as described in https://docs.moodle.org/34/en/Location#Default_time_zone.
Beware that when using docker the timezone in the container would be UTC unless you'll mount it from the host e.g. /etc/localtime:/etc/localtime:ro.
HTH,
Matteo
Hello Matteo,
> How did you do it?
I played with the time_zone variable in MySQL.
I would like to find a way to accurately convert the timestamps to MySQL date on DB side.
I found the FROM_UNIXTIME function unreliable. It's as if moodle would calculate DST into the persisted timestamp.
Another test I did:
FROM_UNIXTIME expects the timestamp to be in UTC/GMT.
I found this code.
When setting a date/time profile field, the value of $applydst is true.
(Altough if the condition check is removed, then it overcorrects the timestamp by applying the difference two times. Saving a date/time profile field calls this function three times actually.)
