| Bug #1998 | UNIX_TIMESTAMP gives incorrect results for certain DATETIMES | ||
|---|---|---|---|
| Submitted: | 2 Dec 2003 22:35 | Modified: | 12 Dec 2003 7:34 |
| Reporter: | Michael Stassen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0.16 | OS: | MacOS (Mac OS X 10.2.8) |
| Assigned to: | Michael Widenius | CPU Architecture: | Any |
[3 Dec 2003 8:27]
Dean Ellis
Verified with latest 4.0.17/Linux. Thank you.
[12 Dec 2003 7:34]
Michael Widenius
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/ Additional info: Thanks for the patch. I have now added this to the MySQL 4.0 tree (slightly modified to be able to handle dates between 2031 and 21xx). Fix will be in 4.0.17
[12 Dec 2003 7:34]
Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: UNIX_TIMESTAMP takes input in the local timezone, but the validation of the input does not take that into account. Thus valid input may be rejected or incorrect results returned, depending on your timezone. How to repeat: Use a DATETIME closer to 1970-01-01 00:00:00 than your timezone's offset of GMT as input to UNIX_TIMESTAMP. For example, in EST (-5), I get mysql> select FROM_UNIXTIME(1); +---------------------+ | FROM_UNIXTIME(1) | +---------------------+ | 1969-12-31 19:00:01 | +---------------------+ 1 row in set (0.00 sec) This is as expected, since 1970-01-01 00:00:01 GMT is 1969-12-31 19:00:01 EST, but mysql> select UNIX_TIMESTAMP('1969-12-31 19:00:01'); +---------------------------------------+ | UNIX_TIMESTAMP('1969-12-31 19:00:01') | +---------------------------------------+ | 0 | +---------------------------------------+ 1 row in set (0.00 sec) This should be 1, not 0, as shown in the first select. In fact, I get 0 for all values from 1969-12-31 19:00:01 to 1969-12-31 23:59:59. Then I get the expected 18000 for 1970-01-01 00:00:00. I have not tested, but expect that UNIX_TIMESTAMP will return negative numbers for some times in timezones with a positive offset from GMT, unless this is caught somewhere. For example, I expect UNIX_TIMESTAMP('1970-01-01 00:00:00') to return -3600 in timezone +1, -7200 in +2. Suggested fix: I believe the problem lies in sql/time.cc. Here is a patch which provides one simple way to fix the problem: *** ../mysql-4.0.16.orig/sql/time.cc Fri Oct 17 01:29:17 2003 --- sql/time.cc Wed Dec 3 00:40:32 2003 *************** *** 125,130 **** --- 125,131 ---- tmp-=t->minute*60 + t->second; // Move to previous hour } *my_timezone= current_timezone; + if (tmp < 0) tmp = 0; return (long) tmp; } /* my_gmt_sec */ *************** *** 415,421 **** if (str_to_TIME(str,length,&l_time,0) == TIMESTAMP_NONE) return(0); ! if (l_time.year >= TIMESTAMP_MAX_YEAR || l_time.year < 1900+YY_PART_YEAR) { current_thd->cuted_fields++; return(0); --- 416,422 ---- if (str_to_TIME(str,length,&l_time,0) == TIMESTAMP_NONE) return(0); ! if (l_time.year >= TIMESTAMP_MAX_YEAR) { current_thd->cuted_fields++; return(0);