mysql日付関連

mysqlに関する日付を何回か見ましたが、mysqlが提供する日付タイプのストレージを使わない経験のある人もいます.
を選択してbigintの整数タイプを使用します.

                                                 
------------  ---------   --------------------- ----------------------------------------- 
datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 
timestamp      4 bytes   YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038 
date           3 bytes   YYYY-MM-DD            1000-01-01          ~ 9999-12-31 
year           1 bytes   YYYY                  1901                ~ 2155 
bigint         8 bytes

プログラミングの経験のある学生は、日付が一連の数字であることを知っているはずで、1秒ごとに1、2分ごとに120です.

--    1164691264437   Java   “    ”： ： 1970-01-01 00:00:00      
/*
getTime
public long getTime()Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this Date object. 

Returns:
the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this date.
*/
mysql> select from_unixtime(1164691264437/1000);
+-----------------------------------+
| from_unixtime(1164691264437/1000) |
+-----------------------------------+
| 2006-11-28 13:21:04               |
+-----------------------------------+
1 row in set (0.05 sec)

以上から
http://bbs.chinaunix.net/thread-924379-1-1.html
http://japankn.iteye.com/blog/558956
逆に?文字列から数字までどの関数を使いますか...

mysql> SELECT UNIX_TIMESTAMP('2010-04-12')*1000;
+-----------------------------------+
| UNIX_TIMESTAMP('2010-04-12')*1000 |
+-----------------------------------+
|                     1271001600000 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2010-04-12','1970-01-01')*24*3600-8*3600,UNIX_TIMESTAMP('2010-04-12' );
+----------------------------------------------------+-------------------------------+
| DATEDIFF('2010-04-12','1970-01-01')*24*3600-8*3600 | UNIX_TIMESTAMP('2010-04-12' ) |
+----------------------------------------------------+-------------------------------+
|                                         1271001600 |                    1271001600 |
+----------------------------------------------------+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%Y-%m-%d %H:%i:%s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2010-09-01 17:02:29                    |
+----------------------------------------+
1 row in set (0.00 sec)