#At file:///misc/mysql/forest/40329/50-40329/
2722 Tatiana A. Nurnberg 2008-11-24
Bug#40329: TIMESTAMPADD does not take in to account Daylight Savings Time
TIMESTAMPADD()/TIMESTAMPDIFF() took (local-) times at face value.
This broken on days where DST began or ended as it ignored the
missing/extra hour.
We now do our calculations on normalized (UTC) dates when hours
matter.
modified:
mysql-test/r/func_time.result
mysql-test/r/timezone.result
mysql-test/t/timezone.test
sql/item_timefunc.cc
sql/item_timefunc.h
sql/sql_yacc.yy
per-file messages:
mysql-test/r/func_time.result
thanks to our new magic out-of-bounds timestamps no longer
work in TIMESTAMPDIFF(), but then the docs always said you
shouldn't rely on that.
mysql-test/r/timezone.result
documented results of new test case
mysql-test/t/timezone.test
Show that anomalies (11-hour/13-hour days thanks to DST) are
handled; show that while we get new pathological cases (with
some date/TZ-combinations converting to UTC will change the
month, and "add 1 month" may be something different at that
point), we didn't break those too badly.
sql/item_timefunc.cc
add helper functions to convert a MY_TIME between UTC and local time.
preserve micro-second part.
In TIMESTAMPADD/DIFF() convert implicitly to/from UTC before/after
calculations where hours matter, so we don't break as badly on
DST-anomalies (11-hour/13-hour day)
sql/item_timefunc.h
Optional parameter: do Item_date_add_interval() calculations
on UTC dates and implicitly convert to/from if needed?
Used by TIMESTAMPADD()
sql/sql_yacc.yy
When using Item_date_add_interval() for TIMESTAMPADD(),
request implicit conversion to and from UTC, the same
as when storing/retrieving a timestamp to/from a field.
=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result 2008-02-25 10:25:57 +0000
+++ b/mysql-test/r/func_time.result 2008-11-24 14:10:40 +0000
@@ -708,7 +708,7 @@ timestampdiff(SQL_TSI_DAY, '1900-02-01',
timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;
a1 a2 a3 a4
-28 28 29 29
+28 0 29 29
SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');
TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27')
0
=== modified file 'mysql-test/r/timezone.result'
--- a/mysql-test/r/timezone.result 2006-11-01 13:47:40 +0000
+++ b/mysql-test/r/timezone.result 2008-11-24 14:10:40 +0000
@@ -45,3 +45,54 @@ unix_timestamp('2038-01-19 04:14:07'),
unix_timestamp('2038-01-19 04:14:08');
unix_timestamp('1970-01-01 01:00:00') unix_timestamp('1970-01-01 01:00:01') unix_timestamp('2038-01-19 04:14:07') unix_timestamp('2038-01-19 04:14:08')
0 1 2147483647 0
+End of 4.1 tests
+DST - there IS no 2:30, needs to be 3:30
+SELECT TIMESTAMPADD(HOUR,1,'2008-03-30 01:30:00') AS DST_test;
+DST_test
+2008-03-30 03:30:00
+DST - there IS no 2:30, throw EINVAL
+SELECT TIMESTAMPADD(MINUTE,0,'2008-03-30 02:30:00.5') AS EINVAL_test;
+EINVAL_test
+2008-03-30 03:00:00.500000
+Warnings:
+Warning 1292 Truncated incorrect time value: '2008-03-30 02:30:00'
+show that DST-code does not break microsecond-awareness
+SELECT TIMESTAMPADD(MICROSECOND,1,'2008-03-30 01:30:00.5') AS Microsecond_test;
+Microsecond_test
+2008-03-30 01:30:00.500001
+DST - there's no 2:30, so there's only one hour between 1:30 and 3:30
+SELECT TIMESTAMPDIFF(HOUR,'2008-03-30 01:30:00','2008-03-30 03:30:00') AS DST_test;
+DST_test
+1
+show that DST-code does not break microsecond-awareness in -DIFF, either
+SELECT TIMESTAMPDIFF(MICROSECOND,'2008-03-30 03:30:00.1','2008-03-30 03:30:00.5') AS Microsecond_test;
+Microsecond_test
+400000
+SET TIME_ZONE="+03:00";
+pathological: TZ=-3: 2001-02-01,2001-05-01 => 2001-01-31,2001-04-30;
+30<31, would give 2 months instead of 3.
+SELECT TIMESTAMPDIFF(MONTH,'2001-02-01','2001-05-01') AS DaysInMonth_test;
+DaysInMonth_test
+3
+test2 needs to give the same result as test4 (test4 is authoritive)
+SELECT TIMESTAMPADD(MINUTE,1,'2001-03-01') AS DaysInMonth_test2;
+DaysInMonth_test2
+2001-03-01 00:01:00
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test2;
+DaysInMonth_test2
+2001-04-01
+test3 needs to give the same result as test5 (test5 is authoritive)
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test3;
+DaysInMonth_test3
+89
+SET TIME_ZONE="+00:00";
+ref for test2
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test4;
+DaysInMonth_test4
+2001-04-01
+ref for test3
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test5;
+DaysInMonth_test5
+89
+SET TIME_ZONE=DEFAULT;
+End of 5.0 tests
=== modified file 'mysql-test/t/timezone.test'
--- a/mysql-test/t/timezone.test 2006-11-01 13:47:40 +0000
+++ b/mysql-test/t/timezone.test 2008-11-24 14:10:40 +0000
@@ -60,4 +60,37 @@ select unix_timestamp('1970-01-01 01:00:
unix_timestamp('2038-01-19 04:14:07'),
unix_timestamp('2038-01-19 04:14:08');
-# End of 4.1 tests
+--echo End of 4.1 tests
+
+#
+# Bug#40329: TIMESTAMPADD does not take in to account Daylight Savings Time.
+#
+
+--echo DST - there IS no 2:30, needs to be 3:30
+SELECT TIMESTAMPADD(HOUR,1,'2008-03-30 01:30:00') AS DST_test;
+--echo DST - there IS no 2:30, throw EINVAL
+SELECT TIMESTAMPADD(MINUTE,0,'2008-03-30 02:30:00.5') AS EINVAL_test;
+--echo show that DST-code does not break microsecond-awareness
+SELECT TIMESTAMPADD(MICROSECOND,1,'2008-03-30 01:30:00.5') AS Microsecond_test;
+--echo DST - there's no 2:30, so there's only one hour between 1:30 and 3:30
+SELECT TIMESTAMPDIFF(HOUR,'2008-03-30 01:30:00','2008-03-30 03:30:00') AS DST_test;
+--echo show that DST-code does not break microsecond-awareness in -DIFF, either
+SELECT TIMESTAMPDIFF(MICROSECOND,'2008-03-30 03:30:00.1','2008-03-30 03:30:00.5') AS Microsecond_test;
+
+SET TIME_ZONE="+03:00";
+--echo pathological: TZ=-3: 2001-02-01,2001-05-01 => 2001-01-31,2001-04-30;
+--echo 30<31, would give 2 months instead of 3.
+SELECT TIMESTAMPDIFF(MONTH,'2001-02-01','2001-05-01') AS DaysInMonth_test;
+--echo test2 needs to give the same result as test4 (test4 is authoritive)
+SELECT TIMESTAMPADD(MINUTE,1,'2001-03-01') AS DaysInMonth_test2;
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test2;
+--echo test3 needs to give the same result as test5 (test5 is authoritive)
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test3;
+SET TIME_ZONE="+00:00";
+--echo ref for test2
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test4;
+--echo ref for test3
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test5;
+SET TIME_ZONE=DEFAULT;
+
+--echo End of 5.0 tests
=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc 2008-11-21 09:48:22 +0000
+++ b/sql/item_timefunc.cc 2008-11-24 14:10:40 +0000
@@ -2051,6 +2051,38 @@ void Item_func_convert_tz::cleanup()
}
+static int local_to_gmt_TIME(Time_zone *tz, MYSQL_TIME *mt)
+{
+ my_bool dst_fail;
+ unsigned long second_part= mt->second_part; // save microsecond-part
+ my_time_t tt= tz->TIME_to_gmt_sec(mt, &dst_fail);
+
+ if (dst_fail)
+ {
+ char s[20];
+ my_datetime_to_str(mt, s);
+ push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+ ER_TRUNCATED_WRONG_VALUE,
+ ER(ER_TRUNCATED_WRONG_VALUE), "time", s);
+ }
+ my_tz_UTC->gmt_sec_to_TIME(mt, tt);
+ mt->second_part= second_part;
+ return dst_fail;
+}
+
+
+static void gmt_to_local_TIME(Time_zone *tz, MYSQL_TIME *mt)
+{
+ my_bool not_used;
+ unsigned long second_part= mt->second_part; // save microsecond-part
+ ulong days= calc_daynr(mt->year,mt->month,mt->day) - 719528; // epoch
+ my_time_t tt= (longlong) days*LL(86400) +
+ (longlong) (mt->hour*3600L + mt->minute*60L + mt->second);
+ tz->gmt_sec_to_TIME(mt, tt);
+ mt->second_part= second_part;
+}
+
+
void Item_date_add_interval::fix_length_and_dec()
{
enum_field_types arg0_field_type;
@@ -2092,6 +2124,7 @@ bool Item_date_add_interval::get_date(MY
{
long period,sign;
INTERVAL interval;
+ Time_zone *tz= current_thd->variables.time_zone;
ltime->neg= 0;
if (args[0]->get_date(ltime, TIME_NO_ZERO_DATE) ||
@@ -2101,6 +2134,17 @@ bool Item_date_add_interval::get_date(MY
if (date_sub_interval)
sign = -sign;
+ if ((int_type == INTERVAL_YEAR_MONTH) ||
+ (int_type == INTERVAL_QUARTER) ||
+ (int_type == INTERVAL_MONTH))
+ use_utc_internally= false;
+
+ if (use_utc_internally)
+ {
+ current_thd->time_zone_used= 1;
+ local_to_gmt_TIME(tz, ltime);
+ }
+
null_value=0;
switch (int_type) {
case INTERVAL_SECOND:
@@ -2190,6 +2234,8 @@ bool Item_date_add_interval::get_date(MY
default:
goto null_date;
}
+ if (use_utc_internally)
+ gmt_to_local_TIME(tz, ltime);
return 0; // Ok
invalid_date:
@@ -3026,20 +3072,27 @@ longlong Item_func_microsecond::val_int(
return 0;
}
-
longlong Item_func_timestamp_diff::val_int()
{
MYSQL_TIME ltime1, ltime2;
longlong seconds;
long microseconds;
long months= 0;
- int neg= 1;
+ int neg= 1, day1, day2;
+ Time_zone *tz= current_thd->variables.time_zone;
null_value= 0;
if (args[0]->get_date(<ime1, TIME_NO_ZERO_DATE) ||
args[1]->get_date(<ime2, TIME_NO_ZERO_DATE))
goto null_date;
+ day1= ltime1.day;
+ day2= ltime2.day;
+
+ current_thd->time_zone_used= 1;
+ local_to_gmt_TIME(tz, <ime1);
+ local_to_gmt_TIME(tz, <ime2);
+
if (calc_time_diff(<ime2,<ime1, 1,
&seconds, µseconds))
neg= -1;
@@ -3058,8 +3111,8 @@ longlong Item_func_timestamp_diff::val_i
year_end= ltime1.year;
month_beg= ltime2.month;
month_end= ltime1.month;
- day_beg= ltime2.day;
- day_end= ltime1.day;
+ day_beg= day2;
+ day_end= day1;
second_beg= ltime2.hour * 3600 + ltime2.minute * 60 + ltime2.second;
second_end= ltime1.hour * 3600 + ltime1.minute * 60 + ltime1.second;
microsecond_beg= ltime2.second_part;
@@ -3071,8 +3124,8 @@ longlong Item_func_timestamp_diff::val_i
year_end= ltime2.year;
month_beg= ltime1.month;
month_end= ltime2.month;
- day_beg= ltime1.day;
- day_end= ltime2.day;
+ day_beg= day1;
+ day_end= day2;
second_beg= ltime1.hour * 3600 + ltime1.minute * 60 + ltime1.second;
second_end= ltime2.hour * 3600 + ltime2.minute * 60 + ltime2.second;
microsecond_beg= ltime1.second_part;
=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h 2008-01-07 16:55:05 +0000
+++ b/sql/item_timefunc.h 2008-11-24 14:10:40 +0000
@@ -683,12 +683,14 @@ class Item_date_add_interval :public Ite
{
String value;
enum_field_types cached_field_type;
+ bool use_utc_internally;
public:
const interval_type int_type; // keep it public
const bool date_sub_interval; // keep it public
- Item_date_add_interval(Item *a,Item *b,interval_type type_arg,bool neg_arg)
- :Item_date_func(a,b),int_type(type_arg), date_sub_interval(neg_arg) {}
+ Item_date_add_interval(Item *a,Item *b,interval_type type_arg,bool neg_arg,bool transient_utc= false)
+ :Item_date_func(a,b),int_type(type_arg), date_sub_interval(neg_arg)
+ { use_utc_internally= transient_utc; }
String *val_str(String *);
const char *func_name() const { return "date_add_interval"; }
void fix_length_and_dec();
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2008-10-02 11:57:52 +0000
+++ b/sql/sql_yacc.yy 2008-11-24 14:10:40 +0000
@@ -5754,7 +5754,7 @@ simple_expr:
}
| TIMESTAMP_ADD '(' interval_time_stamp ',' expr ',' expr ')'
{
- $$= new Item_date_add_interval($7,$5,$3,0);
+ $$= new Item_date_add_interval($7,$5,$3,0,TRUE);
if ($$ == NULL)
MYSQL_YYABORT;
}
Thread |
---|
• bzr commit into mysql-5.0-bugteam branch (azundris:2722) Bug#40329 | Tatiana A. Nurnberg | 24 Nov |