General developer forum

Weird timezone values in the database

 
Picture of Juho Jaakkola
Weird timezone values in the database
Group Particularly helpful MoodlersGroup Plugin developers

I was checking the 'timezone' column of the 'mdl_user' table on an old Moodle site, and found the following values:

  • Europe/Helsinki
  • Europe/Stockholm
  • 1.0
  • 2.0
  • 99

The first two are valid formats supported by PHP: http://php.net/manual/en/timezones.php. All new values get saved to that format.

Also I was able to find out that Moodle uses "99" internally to signify the server's default timezone.

Can someone however tell, what the values "1.0" and "2.0" are, and how they've ended up in the table? (There are LOTS of them.)

I'm using Moodle 3.1.6.

 
Average of ratings: -
C'est moi :-)
Re: Weird timezone values in the database
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Hi,

I think that 1.0 and 2.0 means UTC+1 and UTC+2 respectively.

I also have values like UTC, 13.0 or -8.0.

I think that Moodle used such values in previous versions...

HTH,
Séverin

 
Average of ratings: -
Picture of Juho Jaakkola
Re: Weird timezone values in the database
Group Particularly helpful MoodlersGroup Plugin developers

I looked at one of the values on profile editing page, and it confirms that they indeed represent the difference to UTC:


Representation of the invalid timezone.


So now the question is, why haven't the old values been converted to the new format.

I will report this to the Moodle issue tracker.

 
Average of ratings: -
Picture of Juho Jaakkola
Re: Weird timezone values in the database
Group Particularly helpful MoodlersGroup Plugin developers

I found some existing issues. Here are the most relevant ones:

From the latter I found this comment:

All the old numerics and the Etc/GMT ones are considered legacy (only used for calculations) at all effects, not only by us by PHP itself (ref. http://php.net/manual/en/timezones.others.php). And that's what we tried to do with the table above, not automatic migration to any UTC anymore, just guarantee calculations till the user picks a good one.

So apparently the old formats have been left to the database deliberately. I don't really understand why they haven't been migrated, but I'm happy just to know that the data apparently isn't broken despite still using the old formats.

 
Average of ratings: Useful (1)
C'est moi :-)
Re: Weird timezone values in the database
Group Documentation writersGroup Particularly helpful MoodlersGroup TestersGroup Translators

Thanks for these information.

I suppose that no conversion was made because, for one numerical value, several "text" possibilities exists, and you have no possibility to guess which one is the good one...

I don't know how are handled missing (void) values ; i think i'll replace them by 99.

Séverin

 
Average of ratings: -
Picture of Juho Jaakkola
Re: Weird timezone values in the database
Group Particularly helpful MoodlersGroup Plugin developers

Ah, of course! That's it. I didn't think that far.

I suppose theoretically some timezone names could be deduced from the value of the user's country field, but it wouldn't be very reliable.

Additionally some countries have more than one timezone, so it wouldn't be possible to deduce it right even if the user's country is known.

 
Average of ratings: -