Description:
We want to make some suggestions to the unix_timestamp function
The documentation at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestam... is a little bit misleading when saying:
>> The server interprets date as a value in the current time zone and converts it to an internal value in UTC.
This is only true if you do "select unix_timestamp('2007-01-01 03:00:00')", i.e. you specify the date. However, if you do a "select unix_timestamp(columnA)" and columnA is from the type datetime, than unix_timestamp will not return the corresponding UNIX timestamp relevant to the local time but to the real time.
Example:
While being in timezone UTC:
mysql> select minute_timestamp,unix_timestamp(minute_timestamp) from ticks_charthistory_1i where ISIN='DE0008469008' and market='ITF' order by minute_timestamp desc limit 0,1;
+---------------------+----------------------------------+
| minute_timestamp | unix_timestamp(minute_timestamp) |
+---------------------+----------------------------------+
| 2007-06-07 16:32:00 | 1181233920 |
+---------------------+----------------------------------+
1 row in set (0.00 sec)
Now we ran into a problem, because the implementation differs a little bit when converting DATE columns.
While being in UTC timezone:
+------------+----------------------+
| date | unix_timestamp(date) |
+------------+----------------------+
| 2007-06-07 | 1181174400 |
+------------+----------------------+
1 row in set (0.02 sec)
+------------+----------------------+
| date | unix_timestamp(date) |
+------------+----------------------+
| 2007-06-07 | 1181167200 |
+------------+----------------------+
1 row in set (0.01 sec)
Here, a 00:00:00 is added and that is interpreted as a local time. Now, in my opinion, there are pros and cons to both. I can see the point in doing it like it as at the moment, but there also other situations, where this behaviour causes problems. Mostly, because if you do a select on a date table, you'll see that due to summer/winter time changes, there is not always a 86400 difference. This is crucial, if you use the unix timestamp as an index in an array and want to jump by the days just by adding 86400.
My suggestion would be to add a new function unix_timestamp_utc, which interprets any times like in DATE relative to the UTC timezone. Perhaps a look at http://www.php.net/manual/en/function.mktime.php vs. http://www.php.net/manual/en/function.gmmktime.php can make my idea clearer.
Please let me know your thoughts.
How to repeat:
.
