mysql日期相干

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，两分钟则为120。

-- 假设 1164691264437 是 Java 里的“日期时间”：即：自1970-01-01 00:00:00以来的毫秒数/*getTimepublic 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)

查看更多 下一篇