Hi Ben, all!
[EMAIL PROTECTED] 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
