Bug #1998 UNIX_TIMESTAMP gives incorrect results for certain DATETIMES

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

Verified with latest 4.0.17/Linux.

Thank you.

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

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