Unix date problems

[Andy Jackman]

Hi,
1) I was investigating the unix_timestamp routine in mysql (version
3.23.46-nt) and for some reason the unix epoch (1-1-1970) was returned
with a value of -3600. Then, as you can see, it suddenly corrected
itself. I can't replicate this error, but it caused me to shudder.
Attached is the output from the command line client.
2) The function from_unixtime(n) wraps on my server after 2^31 seconds.
e.g. select from_unixtime(2147483648) returns "1900-01-00 00:00:00". I
suspect this is caused by the underlying time_t size that mysql was
compiled with. Is this so? Are any versions of mysql compiled with a
larger time_t?
Regards,
Andy

+---------------------------+
| from_unixtime(2147483648) |
+---------------------------+
| 1900-01-00 00:00:00 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1060262715 |
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:00') |
+---------------------------------------+
| -3600 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:01') |
+---------------------------------------+
| -3599 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(0);
+---------------------+
| from_unixtime(0) |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:00') |
+---------------------------------------+
| -3600 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:01:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:01:00') |
+---------------------------------------+
| 60 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:02:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:02:00') |
+---------------------------------------+
| 120 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:10');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:10') |
+---------------------------------------+
| 10 |
+---------------------------------------+
1 row in set (0.02 sec)

mysql> select unix_timestamp('1970-01-01 00:00:09');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:09') |
+---------------------------------------+
| 9 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:08');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:08') |
+---------------------------------------+
| 8 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:07');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:07') |
+---------------------------------------+
| 7 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:01') |
+---------------------------------------+
| 1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:00') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)
- Andy.
mysql> select unix_timestamp('1970-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:00') |
+---------------------------------------+
| -3600 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('1970-01-01 00:00:01');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:01') |
+---------------------------------------+
| -3599 |
+---------------------------------------+
1 row in set (0.00 sec)

<snip> - a little later in the same session:

mysql> select unix_timestamp('1970-01-01 00:00:00');
+---------------------------------------+
| unix_timestamp('1970-01-01 00:00:00') |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw