List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:February 13 2006 3:20pm
Subject:Re: FROM_UNIXTIME()
View as plain text  
Hi Ben, all!


scandisk@stripped wrote:
> Hi,
> 
> I have two different servers each running mysql. One is a SuSE and the other
> a FreeBSD system.
> 
> mysql --version returns:
> mysql  Ver 12.22 Distrib 4.0.26, for portbld-freebsd5.4 (i386)
> mysql  Ver 12.22 Distrib 4.0.18, for suse-linux (i686)
> 
> On the SuSE system the following command returns this:
> mysql> SELECT FROM_UNIXTIME(-1000000);
> +-------------------------+
> | FROM_UNIXTIME(-1000000) |
> +-------------------------+
> | 1969-12-20 11:13:20     |
> +-------------------------+
> 
> 
> 
> On the FreeBSD system the same command returns:
> mysql> SELECT FROM_UNIXTIME(-1000000);
> +-------------------------+
> | FROM_UNIXTIME(-1000000) |
> +-------------------------+
> | NULL                    |
> +-------------------------+
> 
> 
> Obviously the SuSE system seems to be able to use negative values for a unix
> timestamp.
> 
> I am wondering, how I can get the FreeBSD system to work the same way. Can
> you help me out?

This is probably no issue of the SuSE vs. FreeBSD binary but rather one 
of 4.0.18 vs. 4.0.26.

Scanning the "Changes in release 4.0.x" sections from 4.0.26 down to 
4.0.18, I came across this one for 4.0.23:
    Fixed bug which caused FROM_UNIXTIME() function to return wrong
    result if the argument was too big. (Bug #6439)
Looking at the associated code change, it seems that this disables 
negative values of Unix timestamps.


The Unix type "time_t" (used to hold timestamps, coded as "values since 
the epoch" which is Jan 1, 1970, 00:00:00 UTC) may be unsigned, and 
negative values are not defined for it (but not excluded either).
(Checked with X/Open 1988, and IEEE / Open Group 2001)
So IMO using negative "Unix timestamp" values is outside the 
specification, and anything may happen.

The 4.0.18 behavior you report seems a bit inconsistent anyway, because 
already in that version the test "func_time" should contain these lines:
    select unix_timestamp('1969-12-01 19:00:01');    # command
    unix_timestamp('1969-12-01 19:00:01')            # result header
    0                                                # result value
So this shows that dates prior to the "epoch" will not be converted to a 
Unix timestamp (which would have to be negative).


If you need to handle values before the "epoch", you should use a 
different data type to store them.


Regards,
Jörg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
Thread
FROM_UNIXTIME()scandisk8 Feb
  • Re: FROM_UNIXTIME()Joerg Bruehe13 Feb