MySQL Bugs: #43798: Respect timezone for Interval etc

Bug #43798	Respect timezone for Interval etc
Submitted:	23 Mar 2009 8:59	Modified:	23 Mar 2009 11:34
Reporter:	Sergei Kulakov (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4/5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc, timezone

Description:
I have a cache table where I store data like this: 

insert into cache set data='...', DT=Now()

and the DT column is timestamp. On 2009.08.03 during 03:00-04:00 I got a lot of erros like this: 

Warning:1292:Truncated incorrect datetime value: '2009-03-08 02:00:07'

The errors were caused by simple conditions like this: 

DT>Now()-Interval 1 Hour

That's I tried to get a cached item and checked if it was not older than an hour. In MySql, Now() does respect the current timezone so it returns 01:59:59 before the switch to DST (CDT in this case, from CST), and 03:00:00 right after it. The point is INTERVAL does not consider the timezone. So 

Now()-Interval 1 Hour may return an invalid (for the zone) time like 
2009-03-08 02:30:00

I know 2 workarounds for this: 
1. Use the datetime type because it does not check the timezone and may store incorrect values. This may lead to slightly incorrect behavior of the cache but it doesn't matter. 
2. Store timestamps directly in an int column. 

Still I wonder - maybe INTERVAL and other date/time handling function should respect the current timezone as well? This is a complex question because in some cases people really might not expect considering the timezone and perform date/time manipulations on some kind of 'abstract' date/time, like GMT but for their local zone.

How to repeat:
1. 
CREATE TABLE `timetest` (
  `id` int(11) default NULL,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
)

2.
insert into timetest set id=2, ts=nnow()-interval (to_days(curdate())-to_days(20090308)) day-interval (left(current_time(), 2)-2) hour;
Query OK, 1 row affected, 1 warning (0.00 sec)

3.
show warnings;
| Warning | 1299 | Invalid TIMESTAMP value in column 'ts' at row 1 |
And the inserted value is 2009-03-08 03:00:00. 

Note that query 2 tries to get some time within the invalid period, it may need manual tuning. 

Suggested fix:
See Description

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html "The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP()  or values in DATE, TIME, or DATETIME columns." You use TO_DAYS, so it is expected what result value is not affected by timezone.

I appreciate the quick reply, but I'd also like to get a comprehensive one. 
I understand the behavior is almost correct and it's not a bug (perhaps I should have selected a feature request). 
But when you mention "You use TO_DAYS" you seem to be completely missing the point. 
The idea of the test insert query was to insert a value like 2009-08-03 02:30:00, but instead of supplying it directly I tried to make an expression that yields it naturally, by use of the INTERVAL operand, like Now()-Interval 1 Hour. The problem is by now too much time has passed since 03.08 hence I have to either manually make expressions like Now()- Interval 15 Day -Interval 3 Hour
but they only work for the current hour, or use a universal expression like what I provided, and that is why I used to_days(). That seemingly complicated expression merely results in something like 2009-03-08 02:53:48, while my use of to_days() has nothing to do with the situation.

Thank you for the feedback.

Please look into output below:

$mysql51 --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 637
Server version: 5.1.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select now();
Field   1:  `now()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   6
Flags:      NOT_NULL BINARY 

+---------------------+
| now()               |
+---------------------+
| 2009-03-23 13:02:29 | 
+---------------------+
1 row in set (0.10 sec)

mysql> select now() - interval 1 day;
Field   1:  `now() - interval 1 day`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY 

+------------------------+
| now() - interval 1 day |
+------------------------+
| 2009-03-22 13:02:36    | 
+------------------------+
1 row in set (0.10 sec)

mysql> create temporary table t1(f1 timestamp);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.11 sec)

mysql> select f1, f1 - interval 1 day from t1;
Field   1:  `f1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       TIMESTAMP
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL UNSIGNED ZEROFILL BINARY TIMESTAMP ON_UPDATE_NOW 

Field   2:  `f1 - interval 1 day`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY 

+---------------------+---------------------+
| f1                  | f1 - interval 1 day |
+---------------------+---------------------+
| 2009-03-23 13:04:32 | 2009-03-22 13:04:32 | 
+---------------------+---------------------+
1 row in set (0.33 sec)

So it is still DATETIME.

If you wish I could verify this as feature request though.

I'm not sure why you demonstarted that. I think what you provided is correct and expected. When you apply an operation like INTERVAL to a timestamp it is converted to a datetime. I'm not sure it's about types. 

In order to explain, let me show you how this is implemented in PHP, even though you may be unfamiliar with the language, the example is easy: 

//Get unix timestamp for the time
$TS=strtotime('2009-03-23 03:30:00'); echo($TS."\n"); //1237797000
//Test back
echo(date("Y-m-d H:i:s", $TS)."\n"); //2009-03-23 03:30:00
//Go 15 days back
$TS1=strtotime('-15 day', $TS); 
echo(date("Y-m-d H:i:s", $TS1)."\n"); //2009-03-08 03:30:00

So far everything's fine. Now this:

//Get unix timestamp for the time
$TS=strtotime('2009-03-23 02:30:00'); echo($TS."\n"); //1237793400
echo(date("Y-m-d H:i:s", $TS)."\n"); //2009-03-23 02:30:00
//15 days back
$TS1=strtotime('-15 day', $TS); 
echo(date("Y-m-d H:i:s", $TS1)."\n"); //2009-03-08 03:30:00

Note that this time it's not the expected '2009-03-08 02:30:00' - the moment is skipped because it does not exist in the CDT timezone. There are only 2009-03-08 01:59:59 and 2009-03-08 03:00:00 with nothing between because of the DST switch! This way PHP (by means of the function strtotime()) always considers the current timezone. While if I try to do the same thing in MySql I do get that moment: 

select '2009-03-23 02:51:41'-Interval 15 Day;
2009-03-08 02:51:41

It is very questionable whether or not to consider this result as incorrect for the zone, and it is definitely correct for the concept of abstract time. 

My proposal was to consider the timezone ALWAYS. I repeat, this is a very complex and intrinsic question. It is hard to consider all possible related aspects and effects of such a change, be it implemented. But it may be worth discussing at least.

Thank you for the feedback.

I provided output above to show MySQL treats expression TIMESTAMP + INTERVAL as DATETIME, so it behaves as documented.

But I agree with you and verify this report as "feature request".

Ok, and there was no problem for me with MySql's treating 

TIMESTAMP + INTERVAL as DATETIME

or anyhow.