5 comments
Comment actions Permalink

Could you describe the case in more details?

Thank you,
Alexander.

0
Comment actions Permalink

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

0
Comment actions Permalink

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.

0
Comment actions Permalink

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)

0
Comment actions Permalink

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|
+---------------------------------------+
*/
  
0

Please sign in to leave a comment.