Database Reference
In-Depth Information
mysql>
SELECT ts FROM t;
+---------------------+
| ts |
+---------------------+
| 2014-06-01 10:30:00 |
+---------------------+
The client time zone also affects the values displayed from functions that return the
current date and time (see
Recipe 6.6
).
See Also
To convert individual date-and-time values from one time zone to another, use the
CONVERT_TZ()
function (see
Recipe 6.5
).
6.5. Shifting Temporal Values Between Time Zones
Problem
You have a date-and-time value, but need to know what it would be in a different time
zone. For example, you're having a teleconference with people in different parts of the
world and you must tell them the meeting time in their local time zones.
Solution
Use the
CONVERT_TZ()
function.
Discussion
The
CONVERT_TZ()
function converts temporal values between time zones. It takes three
arguments: a date-and-time value and two time zone indicators. The function interprets
the date-and-time value as a value in the first time zone and returns the value shifted
into the second time zone.
Suppose that I live in Chicago, Illinois in the US, and that I have a meeting with people
in several other parts of the world. The following table shows the location of each meet‐
ing participant and the time zone name for each:
Location Time zone name
Chicago, Illinois, US
US/Central
Berlin, Germany
Europe/Berlin
London, United Kingdom
Europe/London
Edmonton, Alberta, Canada
America/Edmonton
Brisbane, Australia
Australia/Brisbane