Database date problem

Database date problem

by David Csiki -
Number of replies: 8

Hi!

The way moodle stores dates in the db is weird.

On the Time API wiki it is said that "Moodle always stores all times in unixtime format (number of seconds since epoch) which is independent of timezones.". So I assumed that it stores the date in UTC.

But that doesn't seem to be the case (?). We have a birth date field on our site and we use the MySQL function FROM_UNIXTIME() to get the actual dates in some DB reports. We observed that with some users the birth date is wrong by one day in said reports.

I created a test on two moodle installation (details at the bottom). I added 12 Date/Time profile field without the Include time? option on both installation. Then I filled those up on my profile with dates. Then I ran the following query on both databases:

SET time_zone='UTC';
SELECT data, from_unixtime(data) UTC FROM mdl_user_info_data; -- the table doesn't have any other values

The results are quite strange:

Moodle 3.3.5 Win
data UTC
1516834800 2018-01-24 23:00:00
1519513200 2018-02-24 23:00:00
1521932400 2018-03-24 23:00:00
1524607200 2018-04-24 22:00:00
1527199200 2018-05-24 22:00:00
1529877600 2018-06-24 22:00:00
1532469600 2018-07-24 22:00:00
1535148000 2018-08-24 22:00:00
1537826400 2018-09-24 22:00:00
1540418400 2018-10-24 22:00:00
1543100400 2018-11-24 23:00:00
1545692400 2018-12-24 23:00:00

Moodle 3.4.4 Linux
data UTC
1516838400 2018-01-25 00:00:00
1519516800 2018-02-25 00:00:00
1521936000 2018-03-25 00:00:00
1524610800 2018-04-24 23:00:00
1527202800 2018-05-24 23:00:00
1529881200 2018-06-24 23:00:00
1532473200 2018-07-24 23:00:00
1535151600 2018-08-24 23:00:00
1537830000 2018-09-24 23:00:00
1540422000 2018-10-24 23:00:00
1543104000 2018-11-25 00:00:00
1545696000 2018-12-25 00:00:00


Somehow, daylight savings are affecting this. But why? Shouldn't be the result 2018-12-25 00:00:00 in all cases? 

And why are they different between the two systems?
On both installation, running <?php echo time(); at the same time prints the same timestamp.


Installation details:

Moodle3.3.5
OSWindows 7 x64
Apache2.4.33 (Win64)
PHP 7.0.29
DB MariaDB 10.2.13

Moodle3.4.4
OSUbuntu 18.04 (host)
ApacheApache/2.4.25 (Debian) - from docker image: php:7.1-apache 
PHP 7.1.19
DB MySQL 5.7.22

Average of ratings: -
In reply to David Csiki

Re: Database date problem

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Which fields in Moodle tables are you looking at?

Average of ratings: -
In reply to Howard Miller

Re: Database date problem

by David Csiki -

I'm looking at the data field in user_info_data, which stores all custom profile field values.

In the test case, it contains only Date/Time values.

Average of ratings: -
In reply to Howard Miller

Re: Database date problem

by David Csiki -

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.

Attachment Screenshot_2018-07-18_13-20-52.png
Average of ratings: -
In reply to David Csiki

Re: Database date problem

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

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

Average of ratings: -
In reply to Matteo Scaramuccia

Re: Database date problem

by David Csiki -

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:

  • Created a custom date/time profile field with the Include time? option checked.
  • Went to my profile settings and filled up the new field with this datetime: 24 March 2018, 2:00 PM
  • The value in the DB: 1524661200 which checks out.
  • Changed to field again to 25 April 2018, 2:00 PM (the day when DST kicked in)
  • The value in the DB: 1521982800 which is now one hour behind!

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

Average of ratings: -
In reply to David Csiki

Re: Database date problem

by M G -

Hi,

In my setup, I have a date of birth user profile field. When I am uploading users, it storing date of birth with some other date and year. My timezone is Asia/Kolkatta. 

For example, if in csv file date of birth is kept as 2/5/11 (dd/mm/yy format), on profile page it is displayed as 30 November 2017

Also, both the below dates are stored as the same dates

CSV File                                    On profile page

7-September-2011                 3 June 1955

2-May-2011                             3 June 1955


Any suggestion, in which format I should be keeping date in csv file, so that it is uploaded properly. 


Average of ratings: -
In reply to M G

Re: Database date problem

by M G -

Any suggestion?

Average of ratings: -
In reply to M G

Re: Database date problem

by M G -

I got the answer. 

Date format in csv file should be yyyy-mm-dd. It will save the correct date in the DB. 

Average of ratings: -