Disable applying local time zone

Created July 01, 2014 18:49

Could you describe the case in more details?

Thank you,
Alexander.

Permanently deleted user

Created August 29, 2014 11:19

Hey, off course.

When you querying data from column with type: "timestamp with timezone" it applies local timezone to resultset(i think it is related to jvm running params).

Alexander Chernikov

Created August 29, 2014 19:56

Thank you. Looks like http://youtrack.jetbrains.com/issue/DBE-614 (or am I wrong?). Welcome to vote to make it fix earlier.

Regards,
Alexander.

Permanently deleted user

Created February 16, 2015 23:43

I'm running into the same issue as well.

Using 0xDBE:

My MySQL instance is configured for the

UTC

timezone:

SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP);

/*
+----------------------------------------------------------------------------------------+
|@@global.time_zone|@@session.time_zone|@@system_time_zone|TIMEDIFF(NOW(), UTC_TIMESTAMP)|
+----------------------------------------------------------------------------------------+
|SYSTEM            |SYSTEM             |UTC               |00:00:00                      |
+----------------------------------------------------------------------------------------+
*/

My expectation is that date math (such as using

TIMESTAMPADD

) would be done in UTC.

As an example, Sun, Mar 8, 2:00 AM is the time change from PST → PDT, which will move PT +1 hour (DST start): from UTC-8h to UTC-7h.

So when I add 1 hour to

2015-03-08 01:00:00

I expect to get

2015-03-08 02:00:00

, but I get

2015-03-08 03:00:00

instead:

SELECT TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s'));

/*
+------------------------------------------------------------------------------+
|TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s'))|
+------------------------------------------------------------------------------+
|2015-03-08 03:00:00                                                           |
+------------------------------------------------------------------------------+
*/

Using the mysql cli, I get the correct results:

mysql> SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone, TIMEDIFF(NOW(), UTC_TIMESTAMP);
+--------------------+---------------------+--------------------+--------------------------------+
| @@global.time_zone | @@session.time_zone | @@system_time_zone | TIMEDIFF(NOW(), UTC_TIMESTAMP) |
+--------------------+---------------------+--------------------+--------------------------------+
| SYSTEM | SYSTEM | UTC | 00:00:00 |
+--------------------+---------------------+--------------------+--------------------------------+
1 row in set (0.04 sec)

mysql> SELECT TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s'));
+--------------------------------------------------------------------------------+
| TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s')) |
+--------------------------------------------------------------------------------+
| 2015-03-08 02:00:00 |
+--------------------------------------------------------------------------------+
1 row in set (0.04 sec)

Permanently deleted user

Created February 16, 2015 23:54

More concise repro:

Using MySQL cli:

mysql> SELECT
    ->   TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s')) AS ResultAsDateType,
    ->   DATE_FORMAT(TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s')), '%Y-%m-%d %H:%i:%s') AS ResultAsStringType;
+---------------------+---------------------+
| ResultAsDateType    | ResultAsStringType  |
+---------------------+---------------------+
| 2015-03-08 02:00:00 | 2015-03-08 02:00:00 |
+---------------------+---------------------+
1 row in set (0.03 sec)

Using 0xDBE:

SELECT
  TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s')) AS ResultAsDateType,
  DATE_FORMAT(TIMESTAMPADD(HOUR, 1, STR_TO_DATE('2015-03-08 01:00:00', '%Y-%m-%d %H:%i:%s')), '%Y-%m-%d %H:%i:%s') AS ResultAsStringType;
/*
+---------------------------------------+
|ResultAsDateType   |ResultAsStringType |
+---------------------------------------+
|2015-03-08 03:00:00|2015-03-08 02:00:00|
+---------------------------------------+
*/