[mysql] Can MySQL convert a stored UTC time to local timezone?

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.

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')

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                     |
+---------------------+-----------------------------------------+

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

For get the time only use:

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

I am not sure what math can be done on a DATETIME data type, but if you are using PHP, I strongly recommend using the integer-based timestamps. Basically, you can store a 4-byte integer in the database using PHP's time() function. This makes doing math on it much more straightforward.

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 statement and to compare timestamp i would use the following in Where clause:

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

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.