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

Try running GET_FORMAT for various standards in order to become familiar with the dif-
ferent layouts — or check the documentation . After you've done that, execute the follow-
ing SQL statement to see how this function works in conjunctionwith
DATE_FORMAT() :
SELECT DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'EUR'))
AS 'Date in Europe',
DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'USA'))
AS 'Date in U.S.',
REPLACE(DATE_FORMAT(CURDATE(), GET_FORMAT(DATE,'USA')), '.', '-')
AS 'Another Date in U.S.';
+----------------+--------------+----------------------+
| Date in Europe | Date in U.S. | Another Date in U.S. |
+----------------+--------------+----------------------+
| 18.02.2014 | 02.18.2014 | 02-18-2014 |
+----------------+--------------+----------------------+
Because I don't agree that U.S. dates should use periods, the last field shows how to use
the REPLACE() functionto replace the periods with dashes. GET_FORMAT() isn't a
function you'll use often, but it's good to know about it. A more useful and somewhat
similarfunction is CONVERT_TZ() .
CONVERT_TZ() converts atime to a given time zone. Before we can convert to a given
time zone, though, we need to know which time zone our server is using. We can determ-
ine this by entering the following from the mysql client:
SHOW VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+
This shows that my server is using the filesystem time, which is probably the same time
zone where it's located. Suppose the server we use for our bird-watching site is located in
Boston, Massachusetts, which is in the U.S. Eastern Time Zone. If a member enters in-
formation in the morning about a bird sighting in Rome, Italy, which is in the Central
European Time Zone, we don't want them to see the time in Boston after they save the
entry. We would want the time adjusted for the time zone in which the bird was sighted.
Otherwise people in the United States might think that Italians often see birds during the
night and nocturnal birds such as owls during the day. So we'll use CONVERT_TZ() to
adjust the times appropriately.