Look at this (mysql 5.1.30 solaris x86-64):

mysql> select unix_timestamp('1970-01-02 00:00:00')-
unix_timestamp('1970-01-01 00:00:00');
+-----------------------------------------------------------------------------+

mysql> select unix_timestamp('1970-01-03 00:00:00')-
unix_timestamp('1970-01-02 00:00:00');
+-----------------------------------------------------------------------------+

mysql> select unix_timestamp('1970-01-02 00:00:00');
+---------------------------------------+

mysql> select unix_timestamp('1970-01-01 01:00:00');
+---------------------------------------+

Where the heck is the first hour ??? All unix_timestamps are wrong
by this mysterious hour, so unix_timestamp is the number
of seconds since '1970-01-01 01:00:00', not '1970-01-01 00:00:00', as
all MySQL documents are pretending:
" If UNIX_TIMESTAMP() is called with a date argument, it returns
the value of the argument as seconds since '1970-01-01 00:00:00' UTC. "
This is simply not true.

On 4/14/2011 5:45 AM, Udo Grabowski wrote:
Because your system's timezone is set to UTC-1 currently.

The manual is correct and matches UNIX's clock.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle wrote:
No, it's set to UTC+1 + DST, but this shouldn't affect
the very first hour in 1970.

Udo Grabowski wrote:
in my timezone, so that's actually my zeropoint for unix_timestamp(),
since unix_timestamp is TZ dependent. Tricky.

Udo Grabowski <udo.grabowski (AT) imk (DOT) fzk.REMOVEJUNK.de> wrote:

It's nothing to do with the unix_timestamp() function. But
'1970-01-02 00:00:00' is a DATETIME literal and as such it
implicitly uses your local time zone.

If you want to give DATETIME literals in UTC, you must convert
them to your local timezone before putting them into expressions


mysql> show global variables like '%time_zone';
+------------------+--------+
2 rows in set (0,00 sec)

mysql> select convert_tz('1970-01-02 00:00:00', @@time_zone, 'GMT');
+-------------------------------------------------------+
1 row in set (0,00 sec)

mysql> select unix_timestamp(convert_tz('1970-01-02 00:00:00', 'GMT', @@time_zone))
-unix_timestamp(convert_tz('1970-01-01 00:00:00', 'GMT', @@time_zone)) as x;
+-------+
1 row in set (0,00 sec)


Still I think you found a bug. '1970-01-01 00:00:00 MEST' is an
invalid argument for the unix_timestamp() function, because it
is out of range. This expression should give a warning, and in
strict mode even an error.


XL