Bug #60675 timestamps for time zone databases
Submitted: 28 Mar 2011 13:16 Modified: 28 Mar 2011 13:19
Reporter: Daniël van Eeden Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: timezone

[28 Mar 2011 13:16] Daniël van Eeden 
Description:
mysql_tzinfo_to_sql generates data for the timezone databases.

The zoneinfo databases comes from the frequently updated tzdata package.

It's not easily to determine how old the imported data is.

How to repeat:
Load timezone info.
Try to determine how old the imported data is.

Suggested fix:
Easy fix:
1. Add a timestamp column to the system table time_zone_transition
ALTER TABLE mysql.time_zone_transition ADD COLUMN `Last_Update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. Better fix: Let mysql_tzinfo_to_sql determine the version of the data which it imports and add that to the database. I couldn't determine the version of the data by reading the zoneinfo database, so a change to the tzdata package could be needed. Using rpm/dpkg/pkginfo could be a way to determine the version, but that's not portable.
[28 Mar 2011 13:19] Valeriy Kravchuk 
Thank you for the feature request.
[10 Jun 2015 12:35] Simon Mudd 
As with the new SYS schema in 5.7 add an extra table which is a view with a version number.  Then it's easy to do a select on this version "view/table" to figure out if it's the right one.
[23 Oct 9:17] Daniël van Eeden 
I think this still applies to MySQL 8.0, 8.4, and 9.x
[23 Oct 9:20] Daniël van Eeden 
Another way to do this is to record a version.

Note that the `tzdb` struct returned by `get_tzdb()` has a version field.

- https://en.cppreference.com/w/cpp/chrono/tzdb