MySQL timestamp timezone handling

MySQLtimestamptimezone

Does the timestamp data type represent an instant-in-time or a year-month-date-hour-minute-second value? An instant in time is a moment, like the moment you started reading this, and it can be described with many different y-m-d-h-m-s+timezone values. Without timezone, it's ambiguous, the same moment can be 17:25 for me and 13:25 for someone else.

Here's what I think I get. The current_timestamp function returns the current time in the current timezone, so this is an unambiguous instant, the real now. Inside a table, it is stored as UTC, so with this assumed timezone, it should also be unambiguous.

Now here's what I don't get: it's the existence of the utc_timestamp function. I stored a current_timestamp and an utc_timestamp inside the same column, and they were different. This function shouldn't even exist, I don't understand why it represents a different instant in time.

Best Answer

TIMESTAMP stores the number of seconds from 1970-01-01 00:00:01 to now. It automatically converts to the date and time format when you retrieve the data.

CURRENT_TIMESTAMP(): Returns the current date time with your timezone configured. UTC_TIMESTAMP(): Returns the current date and time using UTC timezone.

If you're using a timezone that's not UTC, these functions always return different values, equal otherwise.

MySQL always stores the date and time in UTC so it can be easily converted to different timezones. NOW() or CURRENT_TIMESTAMP() return the date/time plus your timezone configured.

UTC_TIMESTAMP() just returns the date/time ignoring your timezone setting.

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

Mysql – Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

MySQL is behaving correctly – your test is invalid.

If you round-trip through a time zone with DST, you will not have a lossless conversion if you hit a transition. The timestamp in question occurs during a DST transition in "CET" and "Europe/Berlin".

There are two wall clock times in Asia/Bangkok that correspond to a single wall clock time in Europe/Berlin.

mysql> SELECT CONVERT_TZ('2013-10-27 08:15:33','Asia/Bangkok','Europe/Berlin');
+------------------------------------------------------------------+
| CONVERT_TZ('2013-10-27 08:15:33','Asia/Bangkok','Europe/Berlin') |
+------------------------------------------------------------------+
| 2013-10-27 02:15:33                                              |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ('2013-10-27 07:15:33','Asia/Bangkok','Europe/Berlin');
+------------------------------------------------------------------+
| CONVERT_TZ('2013-10-27 07:15:33','Asia/Bangkok','Europe/Berlin') |
+------------------------------------------------------------------+
| 2013-10-27 02:15:33                                              |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

Check this with a conversion to UTC...

mysql> select convert_tz('2013-10-27 02:59:59','Europe/Berlin','UTC');
+---------------------------------------------------------+
| convert_tz('2013-10-27 02:59:59','Europe/Berlin','UTC') |
+---------------------------------------------------------+
| 2013-10-27 00:59:59                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Two seconds later...

mysql> select convert_tz('2013-10-27 03:01:01','Europe/Berlin','UTC');
+---------------------------------------------------------+
| convert_tz('2013-10-27 03:01:01','Europe/Berlin','UTC') |
+---------------------------------------------------------+
| 2013-10-27 02:01:01                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

...it is one hour and two seconds later.

Or, flip it around.

mysql> SET @@time_zone = 'CET';

mysql> SELECT FROM_UNIXTIME(1382825733) AS zero,  
              FROM_UNIXTIME(1382825733 + 3600) AS one, 
              FROM_UNIXTIME(1382825733 + 3600 + 3600) as two, 
              FROM_UNIXTIME(1382825733 + 3600 + 3600 + 3600) as three,
              FROM_UNIXTIME(1382825733 + 3600 + 3600 + 3600 + 3600) as four;
+---------------------+---------------------+---------------------+---------------------+---------------------+
| zero                | one                 | two                 | three               | four                |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2013-10-27 00:15:33 | 2013-10-27 01:15:33 | 2013-10-27 02:15:33 | 2013-10-27 02:15:33 | 2013-10-27 03:15:33 |
+---------------------+---------------------+---------------------+---------------------+---------------------+
                                                         ^^ ... wait, what? .. ^^
1 row in set (0.00 sec)

If you do a timezone conversion on an ambiguous value during the transition hour, the conversion is not lossless.

Manipulation of timestamps needs to be UTC end-to-end. Using FROM_UNIXTIME() or UNIX_TIMESTAMP() works with native UTC values on one side or the other, but the value is still converted to and from your session time zone (or the server time zone if the session time zone is not set) on the other side -- on the way to or from being a value in a TIMESTAMP column (which is actually stored as UTC, and converted to/from your session time zone).

This is one reason why your server clock should always use UTC.

Best Answer

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

Mysql – Why does MySQL calculate a wrong timestamp when using FROM_UNIXTIME

Related Question