Can MySQL convert a stored UTC time to local timezone?

Question

Can MySQL convert a stored UTC time to local time-zoned time directly in a normal select statement?

Let's say you have some data with a timestamp (UTC).

CREATE TABLE `SomeDateTable` (
  `id`    int(11) NOT NULL auto_increment,
  `value` float NOT NULL default '0',
  `date`  datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
)

Then when I

"select value, date from SomeDateTable";

I of course get all the dates as in their stored UTC form.

But let's say that I would like to have them in another timezone (with DST), can I then add some magic to the select query so that I get all the dates back in the selected timezone?

"select value, TIMEZONE(date, "Europe/Berlin") from SomeDateTable";

Or must I do this in some other layer on top, like in some php code? (it seems to be how most people have solved this problem).

---

If your MySQL installation allows you to use CONVERT_TZ it is a very clean solution, this example shows how to use it.

SELECT CONVERT_TZ( '2010-01-01 12:00', 'UTC', 'Europe/Stockholm' )

However I don't know if this is a good way since some MySQL installation is missing this function, use with care.

Answer 1

Yup, there's the convert_tz function.

Answer 2

For those unable to configure the mysql environment (e.g. due to lack of SUPER access) to use human-friendly timezone names like "America/Denver" or "GMT" you can also use the function with numeric offsets like this:

CONVERT_TZ(date,'+00:00','-07:00')

Answer 3

One can easily use

CONVERT_TZ(your_timestamp_column_name, 'UTC', 'your_desired_timezone_name')

For example:

CONVERT_TZ(timeperiod, 'UTC', 'Asia/Karachi')

Plus this can also be used in WHERE clause to compare after the timezone is converted (but its always better to just compare in UTC timezones and convert to local timezone while displaying the timestamp)

WHERE CONVERT_TZ(timeperiod, 'UTC', {$this->timezone}) NOT BETWEEN {$timeperiods['today_start']} AND {$timeperiods['today_end']}

Answer 4

select convert_tz(now(),@@session.time_zone,'+03:00')

For get the time only use:

time(convert_tz(now(),@@session.time_zone,'+03:00'))

Answer 5

1. Correctly setup your server:

On server, su to root and do this:

# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

(Note that the command at the end is of course mysql , and, you're sending it to a table which happens to have the same name: mysql.)

Next, you can now # ls /usr/share/zoneinfo .

Use that command to see all the time zone info on ubuntu or almost any unixish server.

(BTW that's the convenient way to find the exact official name of some time zone.)

2. It's then trivial in mysql:

For example

mysql> select ts, CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') from example_table ;
+---------------------+-----------------------------------------+
| ts                  | CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') |
+---------------------+-----------------------------------------+
| 2020-10-20 16:59:57 | 2020-10-20 06:59:57                     |
| 2020-10-20 17:02:59 | 2020-10-20 07:02:59                     |
| 2020-10-20 17:30:08 | 2020-10-20 07:30:08                     |
| 2020-10-20 18:36:29 | 2020-10-20 08:36:29                     |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20                     |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20                     |
| 2020-10-20 19:00:18 | 2020-10-20 09:00:18                     |
+---------------------+-----------------------------------------+

Answer 6

I propose to use

SET time_zone = 'proper timezone';

being done once right after connect to database. and after this all timestamps will be converted automatically when selecting them.