Mysql时间相关操作命令

来源：互联网发布：oracle数据库设计编辑：程序博客网时间：2024/05/22 05:10

比较实用分享一下

出处 http://chrui.iteye.com/blog/1547187

5.3日期和时间函数

函数名称功能NOW()返回当前的日期和时间SYSDATE() UTC_TIMESTAMP() UTC_DATE() UTC_TIME() CURDATE()以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。CURTIME()以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。YEAR(date)返回日期date的年份。QUARTER(date)返回date一年中的季度，范围1到4。MONTH(date)返回date的月份，范围1到12。DATE(date)返回date的日期。HOUR(time)返回time的小时。MINUTE(time)返回time的分钟。SECOND(time)返回time的秒数。MONTHNAME(date)返回date的月份英文名字。DAYNAME(date)返回date的星期英文名字。DAYOFMONTH(date)返回date的月份中日期，在1到31范围内。DAYOFYEAR(date)返回date在一年中的日数, 在1到366范围内。DAYOFWEEK(date)返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)WEEKDAY(date)返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。WEEK(date)返回日期date为一年中的的第几周，对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。WEEK(date,first)返回日期date为一年中的的第几周，2个参数形式的WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，从星期一开始。UNIX_TIMESTAMP(date)如果没有参数调用，返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一个date参数被调用，它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME 字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。FROM_UNIXTIME(unix_timestamp,format)返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符。DATE_FORMAT(date, fmt)返回按字符串fmt格式化日期date的值TIME_FORMAT(time,format)类DATE_FORMAT()，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。TO_DAYS(date)给定一个日期date, 返回一个天数 (从年份0开始的天数 )。DATE_ADD(略)(date, INTERVAL expr type)返回一个日期或时间值加上一个日期间隔的时间值DATE_SUB(略)(date,INTERVAL expr type))返回一个日期或时间值减去一个日期间隔的时间值DATEDIFF(expr, expr2)返回起始时间expr和结束时间expr2之间的天数TIMESTAMPADD(略)(interval, int_expr, datetime_expr)将整型表达式int_expr添加到日期或日期时间表达式datetime_expr中。 int_expr的单位被时间间隔参数interval给定，该参数必须是以下值的其中一个： FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、MONTH、 QUARTER或 YEAR。TIMESTAMPDIFF(略)(interval,datetime_expr1,datetime_expr2)返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval参数给出。

举例结果select NOW();2012-06-02 12:56:23(参见CURDATE()举例的+0略)select SYSDATE() ;2012-06-02 12:54:54(参见CURDATE()举例的+0)select DAYOFWEEK('2012-05-31');5(星期四)select WEEKDAY('2012-05-31');3(星期四)select CURDATE();2012-06-02select CURDATE() + 0;20120602select YEAR('2012-06-02');2012select QUARTER('2012-06-02');2select MONTH('2012-06-02');6select DATE(now());2012-06-02select HOUR('10:05:03');10select MINUTE('11:50:02');50select SECOND('11:50:02');02select MONTHNAME('2012-06-02');Juneselect DAYNAME("2012-06-02");'Saturday'select DAYOFMONTH('2012-05-31');31select DAYOFYEAR('2012-05-31');152select UNIX_TIMESTAMP('2012-06-02');1338566400select FROM_UNIXTIME(1338566400, '%Y-%m-%d %H:%i');2012-06-02 00:00select DATE_FORMAT('2012-06-02 12:08:45', '%Y-%m-%d');2012-06-02select TO_DAYS('2012-05-31') - TO_DAYS(NOW());-2select DATEDIFF('2012-06-02', '2012-05-31');2select DATEDIFF('2012-05-31', '2012-06-02');-2

MySql中的日期时间格式(DATE_FORMAT(date, fmt))
说明符说明举例%a工作日的缩写名称(Sun, Mon, ..., Sat) %b月份的缩写名称(Jan~Dec) %c月份，数字形式(0~12) %D带有英语后缀的该月日期  (0th,1st, 2nd,3rd, ...) %d该月日期,数字形式 (00~31) %e该月日期,数字形式(0~31) %f微秒 (000000~999999) %H小时(00~23)同%k, %Y-%m-%d %H:%i:%s%h 或 %I小时(01~12) %i分钟,数字形式 (00~59) %j一年中的天数 (001~366) %k小时 (0~23)同%H, %Y-%m-%d %k:%i:%s%l小时 (1~12) %M月份名称 (January~December) %m月份,数字形式 (00~12) %p上午（AM）或下午（ PM） %r时间 , 12小时制 (小时hh:分钟mm:秒数ss后加 AM或PM) %S 或 %s秒 (00~59) %T时间 , 24小时制 (小时hh:分钟mm:秒数ss) %U周 (00~53),其中周日为每周的第一天 %u周 (00~53),其中周一为每周的第一天 %V周 (01~53),其中周日为每周的第一天 ;和%X同时使用DATE_FORMAT('2012-06-02 12:08:45', '%X %V'); 2012 22%v周 (01~53),其中周一为每周的第一天 ;和%x同时使用 %W一周中每一天的名称(Sunday, Monday, ..., Saturday)select DATE_FORMAT('2012-06-02 12:08:45', '%W'); Saturday%w以数字形式表示周中的天数(0 = Sunday, 1 = Monday, ..., 6 = Saturday)'%w'则输出为6%X该周的年份，其中周日为每周的第一天,数字形式,4位数;和%V同时使用DATE_FORMAT('2012-06-02 12:08:45', '%X %V'); 2012 22%x该周的年份，其中周一为每周的第一天,数字形式,4位数;和%v同时使用 %Y年份,数字形式,4位数 %y年份,数字形式 (2位数) %%‘%’文字字符

Sql代码  
//注：visit-time不可用单引号或双引号等限定。  
select DATE_FORMAT(visit_time, '%Y-%m-%d') from t_visit_result;  

MySql中的日期间隔类型DATE_ADD(date, INTERVAL expr type)
表达式类型(type)描述格式(expr)YEAR年YYQUARTER季度 MONTH月MMWEEK周 DAY日DDHOUR小时hhMINUTE分mmSECOND秒ssMICROSECOND微秒 YEAR_MONTH年和月YY_MMDAY_HOUR日和小时DD hhDAY_MINUTE日和分钟DD hh:mmDAY_SECOND日和秒DD hh:mm:ssHOUR_MINUTE小时和分hh:mmHOUR_SECOND小时和秒hh:ssMINUTE_SECOND分钟和秒mm:ssDAY_MICROSECOND日和微秒DD hh:mm:ss.fffHOUR_MICROSECOND小时和微秒hh:mm:ss.fffMINUTE_MICROSECOND分和微秒mm ss.fffSECOND_MICROSECOND秒和微秒ss.fff

说明：
下面传入的均为正的整数值，若希望减去相应的值，则传入负数即可。
select now();2012-06-02 16:45:53select DATE_ADD(now(), INTERVAL 1 YEAR);2013-06-02 16:45:53select DATE_ADD(now(), INTERVAL 2 QUARTER);2012-12-02 16:45:53 ## 加两个季度select DATE_ADD(now(), INTERVAL '1_2' YEAR_MONTH);## 加1年两个月2013-08-02 16:45:53 select DATE_ADD(now(), INTERVAL '1_26' DAY_HOUR); ## 加1天26个小时 2012-06-04 18:45:53select DATE_ADD(now(), INTERVAL '1 2:10:30' DAY_SECOND);## 加1天2小时10分钟30秒2012-06-03select DATE_ADD(now(), INTERVAL '3:10' HOUR_MINUTE);2012-06-02 19:55:54

Sql代码  
SELECT visit_time, TO_DAYS(UTC_DATE()) - TO_DAYS(visit_time) AS '间隔'   
    FROM  t_visit_result  
        WHERE is_delete = '0' order by visit_result_id limit 0, 5;  
  
SELECT visit_time, TIMESTAMPDIFF(DAY, DATE_FORMAT(visit_time, '%Y-%m-%d'), UTC_DATE()) AS '间隔'   
    FROM t_visit_result   
        WHERE is_delete = '0' order by visit_result_id limit 0, 5;  
  
SELECT visit_time, TIMESTAMPDIFF(WEEK, DATE_FORMAT(visit_time, '%Y-%m-%d'), UTC_DATE()) AS '间隔'   
    FROM t_visit_result   
        WHERE is_delete = '0' order by visit_result_id limit 0, 5;  
## visit_time : 2012-05-03 10:11:24  
## UTC_DATE : 2012-06-05  
## SQL 结果：  
##  visit_time      |   间隔  
##  2012-05-03 10:11:24     |   4  
## 说明：  
##  2012-05-03为周四   2012-05-06为周日  
##  2012-05-13为周日  
##  2012-05-20为周日  
##  2012-05-27为周日  
##  2012-06-03为周日   2012-06-05为周二  
##    
  
SELECT visit_time, TIMESTAMPADD(MONTH, 2, visit_time) as 'NewVisitTime'   
    FROM t_visit_result  
        WHERE is_delete = '0' order by visit_result_id limit 0, 5;  
##TIMESTAMPADD(WEEK, 2, visit_time) 2012-05-03 10:11:24 2012-05-17 10:11:24  
##TIMESTAMPADD(MONTH, 2, visit_time)    2012-05-03 10:11:24 2012-07-03 10:11:24  
##TIMESTAMPADD(MONTH, -2, visit_time)   2012-05-03 10:11:24 2012-03-03 10:11:24  

0 0