Date and Time Functions - Learning MySQL and MariaDB - page 256

The syntax for CONVERT_TZ() requires three arguments: the date and time to convert,
the time zone from whence the time came, and the time zone to which to convert. Let's
look at an example:
SELECT common_name AS 'Bird',
CONCAT(name_first, SPACE(1), name_last) AS 'Birdwatcher',
DATE_FORMAT(time_seen, '%r') AS 'System Time Spotted',
DATE_FORMAT(CONVERT_TZ(time_seen, 'US/Eastern', 'Europe/Rome'),
'%r')
AS 'Birder Time Spotted'
FROM bird_sightings
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
JOIN rookery.conservation_status USING(conservation_status_id) LIMIT
3;
+----------------+------------------+-------------------+---------------------+
| Bird | Birdwatcher |System Time Spotted| Birder
Time Spotted |
+----------------+------------------+-------------------+---------------------+
| Whimbrel | Richard Stringer | 04:57:12 AM | 10:57:12
AM |
| Eskimo Curlew | Elena Bokova | 05:09:27 AM | 11:09:27
AM |
| Marbled Godwit | Rusty Osborne | 05:13:25 AM | 11:13:25
AM |
+----------------+------------------+-------------------+---------------------+
Notice that the time zones on the system are six hours earlier than the converted times. Of
course, this is assuming that everyone is located in the same time zone as Rome. What we
could do is add a column to the humans table to include the time zone in which the user
is located or prefers. When a user registers, we can guess at their time zone based on what
their web browser tells us or some other clever method. But then we could give the user
an option of choosing another time zone in case we guessed wrong. However you determ-
ine and store the time zone, you would modify the preceding SQL statement to change the
time to which CONVERT_TZ() converts to that value.
Notice that the time zones we're giving for CONVERT_TZ() are not limited to three-
character code (e.g., CET for Central European time). They're based on the time zone
names in MySQL, which include CET . If you ran the preceding SQL statement and it re-
turned null values for the field containing CONVERT_TZ() , it may be because the time
zone information hasn't been loaded. When MySQL or MariaDB are installed, on Unix-
type systems you will find the time zone files in the /usr/share/zoneinfo directory. If you
get a listing of that directory, you'll see the names that may be used for the time zone ar-