看了几遍关于mysql的日期,一些貌似有经验的人不用mysql提供的日期类型存储,
而使用bigint的整数类型。
有编程经验的同学,应该知道日期就一串数字,每一秒就1,两分钟则为120。
以上来至[url]http://bbs.chinaunix.net/thread-924379-1-1.html[/url]
[url]http://japankn.iteye.com/blog/558956[/url]
反过来呢?字符串到数字用哪个函数呢。。。。
而使用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以来的毫秒数
/*
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)
以上来至[url]http://bbs.chinaunix.net/thread-924379-1-1.html[/url]
[url]http://japankn.iteye.com/blog/558956[/url]
反过来呢?字符串到数字用哪个函数呢。。。。
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)