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
欢迎大家阅读《mysql日期相干》,跪求各位点评，by 搞代码
有编程经验的同学，应该知道日期就一串数字，每一秒就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)
以上来至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)
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql日期相干
转载请注明原文链接：mysql日期相干
