MySQL常用时间函数

官方文档：Date and Time Functions

NameDescription
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
CONVERT_TZ() Convert from one timezone to another
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME() Return the current time
DATE() Extract the date part of a date or datetime expression
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format UNIX timestamp as a date
GET_FORMAT() Return a date format string
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE() Create a date from the year and day of year
MAKETIME() Create time from hour, minute, second
MICROSECOND() Return the microseconds from argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
QUARTER() Return the quarter from a date argument
SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
SECOND() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
SUBTIME() Subtract times
SYSDATE() Return the time at which the function executes
TIME() Extract the time portion of the expression passed
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP() Return a UNIX timestamp
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (1-53)
YEAR() Return the year
YEARWEEK() Return the year and week

注：

  • 接收date参数的函数在收到datetime时，会忽略掉time部分；接收time参数的函数在收到datetime时，也会忽略掉date部分。
  • 在一次查询中，如果多次用到查询当前时间的函数，如NOW()/CURDATE()等，则其返回结果是一样的。同样适用于： CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP()等。

1.CONVERT_TZ

CONVERT_TZ(dt,from_tz,to_tz)：时区转换

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

　　

2.CURDATE/CURRENT_DATE

CURDATE()/CURRENT_DATE()

返回当前的日期。'YYYY-MM-DD' or YYYYMMDD 形式，根据需要。

mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613

　　

 

2.CURTIME/CURRENT_TIME

CURTIME()/CURRENT_TIME()

返回当前的时间。'HH:MM:SS' or HHMMSS 形式，根据需要。

mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026.000000

　　

3.DATE/TIME

DATE(str)：抽取date或datetime参数的日期部分。

TIME(str)：抽取time或datetime参数的时间部分。

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'
mysql> SELECT TIME('2003-12-31 01:02:03');
        -> '01:02:03'
mysql> SELECT TIME('2003-12-31 01:02:03.000123');
        -> '01:02:03.000123'

　　

 

4.DATEDIFF/TIMEDIFF

DATEDIFF(date1,date2)

DATEDIFF() 函数返回两个日期之间的天数（只比天），date1 和 date2 参数是合法的datetime/date表达式。

select datediff('2016-03-29','2016-03-29');
select datediff('2016-03-29 00:00:00','2016-03-29 23:59:59');

　　

TIMEDIFF(date1,date2)
TIMEDIFF()函数返回两个日期之间的时分秒数(HH:MM:ss)，date1 和 date2 参数是datetime/time表达式。

select timediff('2016-03-30 00:00:00','2016-03-28 11:11:11');
select timediff('00:00:00','11:11:11');

 

5.DATE_SUB/DATE_ADD

DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

SELECT id FROM my_table WHERE create_time >= date_sub(now(), INTERVAL 3 HOUR) AND create_time < now();

Type 值

  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

　

6.时间加减

当我们在给now（）+-一个时间的时候，其实应该这样理解的：
+1/+01：加1秒钟
+101/+0101：加1分钟1秒钟
+10101/+010101：加1小时1分钟1秒钟
+1010101/+01010101：加1天1时1分钟1秒钟
+101010101/+0101010101：加1月1天1时1分钟1秒钟
+1101010101/+010101010101：加1年1月1天1时1分钟1秒钟,这里要注意下，年这个部分可以是4位(高位没有的话会补零)：00010101010101

 

7.DATE_FORMAT

DATE_FORMAT(date,format)
用于以不同的格式显示日期/时间数据。

SELECT DATE_FORMAT(insert_time,'%Y-%m-%d %H:%i:%S') AS insert_time FROM user;
SELECT DATE_FORMAT(insert_time,'%Y-%m-%d') AS day, COUNT(id) AS count FROM user GROUP BY day;

　　

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

 


8.DAYOFWEEK/DAYOFMONTH/DAYOFYEAR

DAYOFWEEK(date)：返回date所代表的一星期中的第几天(1~7)

DAYOFMONTH(date)：返回date是一个月的第几天(1~31)

DAYOFYEAR(date)：返回date是一年的第几天(1~366)

 

9.MINUTE/HOUR/DAY/WEEK/MONTH/QUARTER/YEAR

MINUTE(time)：返回time的分钟值(0~59)

HOUR(time)：返回time的小时值(0~23)

DAY(date)：返回date是一个月的第几天(1~31)，等同于DAYOFMONTH(date)

WEEK(date)：返回日期date为一年中第几周(0~53)

MONTH(date)：返回date的月份值(1~12)

QUARTER(date)：返回date在一年中的季度(1~4)

YEAR(date)：返回日期date的年份(1000~9999)

 

10.DAYNAME/MONTHNAME

DAYNAME(date)： 返回date的星期名

MONTHNAME(date)：返回date的月份名

 

11.EXTRACT

EXTRACT(unit FROM date)

从时间里抽取对应的单位。unit参数DATE_SUB的Type。

mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
       -> 2009
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
       -> 200907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');
       -> 20102
mysql> SELECT EXTRACT(MICROSECOND
    ->                FROM '2003-01-02 10:30:00.000123');
        -> 123

　

12.FROM_UNIXTIME

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

将unix_timestamp类型的参数转换为'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS 形式。如果给定fromat，则按指定格式转。

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

　　

13.LAST_DAY

LAST_DAY(date)：返回当月的最后一天(date类型)。date非法，则返回NULL。

mysql> SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
        -> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
        -> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
        -> NULL

　　

14.NOW/SYSDATE

NOW()：返回当前的日期和时间。'YYYY-MM-DD HH:MM:SS' 或者 YYYYMMDDHHMMSS形式，根据需要。它记录的是SQL语句开始执行的时间，所以一条语句有多个NOW()时，其返回结果是一样的。

SYSDATE()：返回当前的日期和时间。'YYYY-MM-DD HH:MM:SS' 或者 YYYYMMDDHHMMSS形式，根据需要。它记录的是SYSDATE()函数开始执行的时间，所以一条语句有多个SYSDATE()时，其返回结果是不一样的。

  

参考：

  1. Mysql日期和时间函数
posted @ 2016-06-22 10:27  waterystone  阅读(1137)  评论(0)  编辑  收藏  举报