Mysql实例MySql时间查询总结
《Mysql实例MySql时间查询总结》要点: mysql时间查询函数: +-----------------------+ |DAYOFWEEK('2012-08-28')| +-----------------------+ |???????????????????? 3 | +-----------------------+ 1 row in set WEEKDAY(date) ? 返回date的星期索引(0=星期一,1=星期二,……6= 星期天). +------------------------------+ |WEEKDAY('2012-08-28 22:23:00')| +------------------------------+ |??????????????????????????? 1 | +------------------------------+ 1 row in set mysql> select WEEKDAY('2012-08-28'); +---------------------+ |WEEKDAY('2012-08-28')| +---------------------+ |?????????????????? 1 | +---------------------+ 1 row in set DAYOFMONTH(date) ? 返回date的月份中日期,在1到31范围内. +------------------------+ |DAYOFMONTH('2012-08-28')| +------------------------+ |???????????????????? 28 | +------------------------+ 1 row in set? DAYOFYEAR(date) ? 返回date在一年中的日数,在1到366范围内. +-----------------------+ |DAYOFYEAR('2012-08-28')| +-----------------------+ |?????????????????? 241 | +-----------------------+ 1 row in set MONTH(date) ? 返回date的月份,范围1到12. +-------------------+ |MONTH('2012-08-28')| +-------------------+ |???????????????? 8 | +-------------------+ 1 row in set DAYNAME(date) ? 返回date的星期名字. +---------------------+ |DAYNAME("2012-08-28")| +---------------------+ |???????????? Tuesday | +---------------------+ 1 row in set MONTHNAME(date) ? 返回date的月份名字. +-----------------------+ |MONTHNAME("2012-08-28")| +-----------------------+ |??????????????? August | +-----------------------+ 1 row in set QUARTER(date) ? 返回date一年中的季度,范围1到4. +-------------------+ |QUARTER('12-08-28')| +-------------------+ |???????????????? 3 | +-------------------+ 1 row in set WEEK(date)、WEEK(date,first) ? ? +------------------------+ |WEEK('2012-08-28')| +------------------+ |?????????????? 35 | +------------------+ 1 row in set mysql> select WEEK('2012-08-28',0); +--------------------+ |WEEK('2012-08-28',0)| +--------------------+ |???????????????? 35 | +--------------------+ 1 row in set mysql> select WEEK('2012-08-28',1); +------------------------+ |WEEK('2012-08-28',1)| +--------------------+ |???????????????? 35 | +--------------------+ 1 row in set YEAR(date) ? ? +----------------+ |YEAR('12-08-28')| +----------------+ |?????????? 2012 | +----------------+ 1 row in set HOUR(time) ? ? +----------------+ |HOUR('10:05:03')| +----------------+ |???????????? 10 | +----------------+ 1 row in set MINUTE(time) ? ? 返回time的分钟,范围是0到59. +---------------------------+ |MINUTE('12-08-28 10:05:03')| +---------------------------+ |???????????????????????? 5 | +---------------------------+ 1 row in set SECOND(time) ? ? +------------------+ |SECOND('10:05:03')| +------------------+ |??????????????? 3 | +------------------+ 1 row in set PERIOD_ADD(P,N) ? ? +------------------+ |PERIOD_ADD(1208,2)| +------------------+ |?????????? 201210 | +------------------+ 1 row in set PERIOD_DIFF(P1,P2) ? ? 返回在时期P1和P2之间月数,P1和P2应该以格式YYMM或YYYYMM.注意,时期参数P1和P2不是日期值. +------------------------+ |PERIOD_DIFF(1208,201203)| +------------------------+ |????????????????????? 5 | +------------------------+ 1 row in set ? DATE_ADD(date,INTERVAL expr type)、DATE_SUB(date,INTERVAL expr type)、ADDDATE(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type) MINUTE 分钟 MINUTESMYSQL入门 HOUR 时间 HOURSMYSQL入门 DAY 天 DAYSMYSQL入门 MONTH 月 MONTHSMYSQL入门 YEAR 年 YEARSMYSQL入门 MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS"MYSQL入门 HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"MYSQL入门 DAY_HOUR 天和小时 "DAYS HOURS"MYSQL入门 YEAR_MONTH 年和月 "YEARS-MONTHS"MYSQL入门 HOUR_SECOND 小时,分钟,"HOURS:MINUTES:SECONDS"MYSQL入门 DAY_MINUTE 天,小时,分钟 "DAYS HOURS:MINUTES"MYSQL入门 DAY_SECOND 天,秒 "DAYS HOURS:MINUTES:SECONDS"MYSQL入门 MySQL在expr格式中允许任何标点分隔符.表示显示的是建议的分隔符.如果date参数是一个DATE值并且你的计算仅仅包含YEAR、MONTH和DAY部分(即,没有时间部分),结果是一个DATE值.否则结果是一个DATETIME值. +-----------------------------------------+ |"2012-08-28 23:59:59" + INTERVAL 1 SECOND| +-----------------------------------------+ |???????????????????? 2012-08-29 00:00:00 | +-----------------------------------------+ 1 row in set mysql> SELECT INTERVAL 1 DAY + "2012-08-28"; +-----------------------------+ |INTERVAL 1 DAY + "2012-08-28"| +-----------------------------+ |????????????????? 2012-08-29 | +-----------------------------+ 1 row in set mysql> SELECT "2012-08-28" - INTERVAL 1 SECOND; +--------------------------------+ |"2012-08-28" - INTERVAL 1 SECOND| +--------------------------------+ |??????????? 2012-08-27 23:59:59 | +--------------------------------+ 1 row in set mysql> SELECT DATE_ADD("2012-08-28 23:59:59",INTERVAL 1 SECOND); +-------------------------------------------------+ |DATE_ADD("2012-08-28 23:59:59",INTERVAL 1 SECOND)| +-------------------------------------------------+ |???????????????????????????? 2012-08-29 00:00:00 | +-------------------------------------------------+ 1 row in set mysql> SELECT DATE_ADD("2012-08-28 23:59:59",INTERVAL 1 DAY); +----------------------------------------------+ |DATE_ADD("2012-08-28 23:59:59",INTERVAL 1 DAY)| +----------------------------------------------+ |????????????????????????? 2012-08-29 23:59:59 | +----------------------------------------------+ 1 row in set mysql> SELECT DATE_ADD("2012-08-28 23:59:59",INTERVAL "1:1" MINUTE_SECOND); +------------------------------------------------------------+ |DATE_ADD("2012-08-28 23:59:59",INTERVAL "1:1" MINUTE_SECOND)| +------------------------------------------------------------+ |??????????????????????????????????????? 2012-08-29 00:01:00 | +------------------------------------------------------------+ 1 row in set mysql> SELECT DATE_SUB("2012-08-28 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); +-------------------------------------------------------------+ |DATE_SUB("2012-08-28 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND)| +-------------------------------------------------------------+ |???????????????????????????????????????? 2012-08-26 22:58:59 | +-------------------------------------------------------------+ 1 row in set mysql> SELECT DATE_ADD("2012-08-28 00:00:00",INTERVAL "-1 10" DAY_HOUR); +---------------------------------------------------------+ |DATE_ADD("2012-08-28 00:00:00",INTERVAL "-1 10" DAY_HOUR)| +---------------------------------------------------------+ |???????????????????????????????????? 2012-08-26 14:00:00 | +---------------------------------------------------------+ 1 row in set mysql> SELECT DATE_SUB("2012-08-28",INTERVAL 31 DAY); +---------------------------------------+ |DATE_SUB("2012-08-28",INTERVAL 31 DAY)| +---------------------------------------+ |??????????????????????????? 2012-07-28 | +---------------------------------------+ 1 row in set mysql> SELECT EXTRACT(YEAR FROM "2012-08-28"); +-------------------------------+ |EXTRACT(YEAR FROM "2012-08-28")| +-------------------------------+ |????????????????????????? 2012 | +-------------------------------+ 1 row in set mysql> SELECT EXTRACT(YEAR_MONTH FROM "2012-08-28 01:02:03"); +----------------------------------------------+ |EXTRACT(YEAR_MONTH FROM "2012-08-28 01:02:03")| +----------------------------------------------+ |?????????????????????????????????????? 201208 | +----------------------------------------------+ 1 row in set mysql> SELECT EXTRACT(DAY_MINUTE FROM "2012-08-28 01:02:03"); +----------------------------------------------+ |EXTRACT(DAY_MINUTE FROM "2012-08-28 01:02:03")| +----------------------------------------------+ |?????????????????????????????????????? 280102 | +----------------------------------------------+ 1 row in set ? 如果你指定太短的间隔值(不包括type关键词期望的间隔部分),MySQL假设你省掉了间隔值的最左面部分.例如,如果你指定一个type是DAY_SECOND,值expr被希望有天、小时、分钟和秒部分.如果你象"1:10"这样指定值,MySQL假设日子和小时部分是丢失的并且值代表分钟和秒.换句话说,"1:10" DAY_SECOND以它等价于"1:10" MINUTE_SECOND的方式解释,这对那MySQL解释TIME值表示经过的时间而非作为一天的时间的方式有二义性.如果你使用确实不正确的日期,结果是NULL.如果你增加MONTH、YEAR_MONTH或YEAR并且结果日期大于新月份的最大值天数,日子在新月用最大的天调整. +----------------------------------------+ |DATE_ADD('2012-08-28',Interval 1 month)| +----------------------------------------+ |???????????????????????????? 2012-09-28 | +----------------------------------------+ 1 row in set ? 注意,从前面的例子中词INTERVAL和type关键词不是区分大小写的.MYSQL入门 TO_DAYS(date) +---------------+ |TO_DAYS(120828)| +---------------+ |??????? 735108 | +---------------+ 1 row in set mysql> select TO_DAYS('2012-08-28'); +---------------------+ |TO_DAYS('2012-08-28')| +---------------------+ |????????????? 735108 | +---------------------+ 1 row in set ? TO_DAYS()不打算用于使用格列高里历(1582)出现前的值.MYSQL入门 FROM_DAYS(N) +-----------------+ |FROM_DAYS(735108)| +-----------------+ |????? 2012-08-28 | +-----------------+ 1 row in set DATE_FORMAT(date,format) ? ? 根据format字符串格式化date值.下列修饰符可以被用在format字符串中: %M 月名字(January……December)MYSQL入门 %W 星期名字(Sunday……Saturday)MYSQL入门 %D 有英语前缀的月份的日期(1st,2nd,3rd,等等.)MYSQL入门 %Y 年,数字,4 位MYSQL入门 %y 年,2 位MYSQL入门 %a 缩写的星期名字(Sun……Sat)MYSQL入门 %d 月份中的天数,数字(00……31)MYSQL入门 %e 月份中的天数,数字(0……31)MYSQL入门 %m 月,数字(01……12)MYSQL入门 %c 月,数字(1……12)MYSQL入门 %b 缩写的月份名字(Jan……Dec)MYSQL入门 %j 一年中的天数(001……366)MYSQL入门 %H 小时(00……23)MYSQL入门 %k 小时(0……23)MYSQL入门 %h 小时(01……12)MYSQL入门 %I 小时(01……12)MYSQL入门 %l 小时(1……12)MYSQL入门 %i 分钟,数字(00……59)MYSQL入门 %r 时间,12 小时(hh:mm:ss [AP]M)MYSQL入门 %T 时间,24 小时(hh:mm:ss)MYSQL入门 %S 秒(00……59)MYSQL入门 %s 秒(00……59)MYSQL入门 %p AM或PMMYSQL入门 %w 一个星期中的天数(0=Sunday ……6=Saturday )MYSQL入门 %U 星期(0……52),这里星期天是星期的第一天MYSQL入门 %u 星期(0……52),这里星期一是星期的第一天MYSQL入门 %% 一个文字“%”.MYSQL入门 所有的其他字符不做解释被复制到结果中. +----------------------------------------------+ |DATE_FORMAT('2012-08-28 22:23:00','%W %M %Y')| +----------------------------------------------+ |????????????????????????? Tuesday August 2012 | +----------------------------------------------+ 1 row in set mysql> select DATE_FORMAT('2012-08-28 22:23:00','%H:%i:%s'); +------------------------+ |DATE_FORMAT('2012-08-28 22:23:00','%H:%i:%s')| +------------------------+ |?????????????? 22:23:00 | +------------------------+ 1 row in set mysql> select DATE_FORMAT('2012-08-28 22:23:00','%D %y %a %d %m %b %j'); +---------------------------------------------------------+ |DATE_FORMAT('2012-08-28 22:23:00','%D %y %a %d %m %b %j')| +---------------------------------------------------------+ |?????????????????????????????? 28th 12 Tue 28 08 Aug 241 | +---------------------------------------------------------+ 1 row in set mysql> select DATE_FORMAT('2012-08-28 22:23:00','%H %k %I %r %T %S %w'); +---------------------------------------------------------+ |DATE_FORMAT('2012-08-28 22:23:00','%H %k %I %r %T %S %w')| +---------------------------------------------------------+ |????????????????????? 22 22 10 10:23:00 PM 22:23:00 00 2 | +---------------------------------------------------------+ 1 row in set ? MySQL3.23中,在格式修饰符字符前需要%.在MySQL更早的版本中,%是可选的.MYSQL入门 TIME_FORMAT(time,format) +-----------------------------------+ |time_format('22:23:01','%H.%i.%s')| +-----------------------------------+ |????????????????????????? 22.23.01 | +-----------------------------------+ 1 row in set
+-----------+ | CURDATE() | +-----------+ |2012-08-28 | +-----------+ 1 row in set mysql> select CURDATE() + 0; +-------------+ |CURDATE() + 0| +-------------+ |??? 20120828 | +-------------+ 1 row in set ? CURTIME()、CURRENT_TIME +---------+ |CURTIME()| +---------+ |19:31:09 | +---------+ 1 row in set mysql> select CURTIME() + 0; +-------------+ |CURTIME() + 0| +-------------+ |193126.000000| +-------------+ 1 row in set ? NOW()、SYSDATE()、CURRENT_TIMESTAMP +---------------------+ |???????? NOW()?????? | +---------------------+ | 2012-08-28 19:31:46 | +---------------------+ 1 row in set mysql> select NOW() + 0; +-----------------------+ |??????? NOW() + 0????? | +-----------------------+ | 20120828193253.000000 | +-----------------------+ 1 row in set ? UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date) +----------------+ |UNIX_TIMESTAMP()| +----------------+ |???? 1346153620 | +----------------+ 1 row in set mysql> select UNIX_TIMESTAMP('2012-08-28 22:23:00'); +-------------------------------------+ |UNIX_TIMESTAMP('2012-08-28 22:23:00')| +-------------------------------------+ |????????????????????????? 1346163780 | +-------------------------------------+ 1 row in set ? 当UNIX_TIMESTAMP被用于一个TIMESTAMP列,函数将直接接受值,没有隐含的“string-to-unix-timestamp”变换.MYSQL入门 FROM_UNIXTIME(unix_timestamp) +-------------------------+ |FROM_UNIXTIME(1346159580)| +-------------------------+ |???? 2012-08-28 21:13:00 | +-------------------------+ 1 row in set mysql> select FROM_UNIXTIME(1346159580) + 0; +----------------------------+ |FROM_UNIXTIME(875996580) + 0| +----------------------------+ |????? 20120828211300.000000 | +----------------------------+ 1 row in set ? FROM_UNIXTIME(unix_timestamp,format) +------------------------------------------------------+ |FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x')| +------------------------------------------------------+ |?????????????????????? 2012 28th August 07:37:47 2012 | +------------------------------------------------------+ 1 row in set ? SEC_TO_TIME(seconds) +-----------------+ |SEC_TO_TIME(2378)| +-----------------+ |??????? 00:39:38 | +-----------------+ 1 row in set mysql> select SEC_TO_TIME(2378) + 0; +---------------------+ |SEC_TO_TIME(2378) + 0| +---------------------+ |???????? 3938.000000 | +---------------------+ 1 row in set ? TIME_TO_SEC(time) +-----------------------+ |TIME_TO_SEC('22:23:00')| +-----------------------+ |???????????????? 80580 | +-----------------------+ 1 row in set mysql> select TIME_TO_SEC('00:39:38'); +-----------------------+ |TIME_TO_SEC('00:39:38')| +-----------------------+ |????????????????? 2378 | +-----------------------+ 1 row in set 《Mysql实例MySql时间查询总结》是否对您有启发,欢迎查看更多与《Mysql实例MySql时间查询总结》相关教程,学精学透。编程之家PHP学院为您提供精彩教程。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |