[总结] MySQL 的 时间/日期 计算
[总结] MySQL 的 时间/日期 计算
·[MySQL 时间、日期 计算]
·相关函数：
·取当前时间的函数：
NOW()、LOCALTIME、LOCALTIME()，LOCALTIMESTAMP, LOCALTIMESTAMP()
CURDATE()、 CURTIME()、 UTC_DATE()、 CURRENT_DATE()、
UTC_TIME()、UTC_TIMESTAMP()、CURRENT_TIMESTAMP()、 CURRENT_TIME()、
FROM_UNIXTIME()、
CONVERT_TZ()、DAY()
·时间、日期计算函数：
DATE_ADD(), DATE_SUB(),ADDDATE(),SUBDATE,
CURRENT_DATE(),CURRENT_TIME(),CURDATE(),CURTIME()
DATEDIFF(expr,expr2)
TIMEDIFF(expr,expr2)
ADDTIME(),SUBTIME()
CURDATE(), CURTIME()
LOCALTIMESTAMP, LOCALTIMESTAMP()
TIMESTAMP(expr) , TIMESTAMP(expr,expr2) ,
TIMESTAMPADD(interval,int_expr,datetime_expr)
TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
·时间、日期抽取函数：
EXTRACT(),
TIME(expr)
DAY(), DAYNAME(), DAYOFMONTH(), DAYOFWEEK(), DAYOFWEEK(),
WEEK(date[,mode]) ,WEEKDAY(date),
DAYOFYEAR(date), MONTH(date)， MONTHNAME(date)，QUARTER(date)
MICROSECOND(expr)，MINUTE(),SECOND(),HOUR(time)，
MAKEDATE(year,dayofyear) MAKETIME(hour,minute,second)
YEAR(date)
FROM_DAYS(), TO_DAYS()， LAST_DAY(date)
DATE_FORMAT(date,format), STR_TO_DATE(str,format)
TIME_FORMAT(time,format)
TIME_TO_SEC(time) TO_DAYS(date)
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
UTC_TIMESTAMP, UTC_TIMESTAMP()
UTC_DATE, UTC_DATE()
·INTERVAL 关键字（重点！！！）
======================================================================
type 值 预期的 expr 格式
======================================================================
MICROSECOND MICROSECONDS
----------------------------------------------------------------------
SECOND SECONDS
----------------------------------------------------------------------
MINUTE MINUTES
----------------------------------------------------------------------
HOUR HOURS
----------------------------------------------------------------------
DAY DAYS
----------------------------------------------------------------------
WEEK WEEKS
----------------------------------------------------------------------
MONTH MONTHS
----------------------------------------------------------------------
QUARTER QUARTERS
----------------------------------------------------------------------
YEAR YEARS
----------------------------------------------------------------------
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
----------------------------------------------------------------------
MINUTE_MICROSECOND 'MINUTES.MICROSECONDS'
----------------------------------------------------------------------
MINUTE_SECOND 'MINUTES:SECONDS'
----------------------------------------------------------------------
HOUR_MICROSECOND 'HOURS.MICROSECONDS'
----------------------------------------------------------------------
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
----------------------------------------------------------------------
HOUR_MINUTE 'HOURS:MINUTES'
----------------------------------------------------------------------
DAY_MICROSECOND 'DAYS.MICROSECONDS'
----------------------------------------------------------------------
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
----------------------------------------------------------------------
DAY_MINUTE 'DAYS HOURS:MINUTES'
----------------------------------------------------------------------
DAY_HOUR 'DAYS HOURS'
----------------------------------------------------------------------
YEAR_MONTH 'YEARS-MONTHS'
======================================================================
·详尽范例：
SELECT NOW() + INTERVAL 3 DAY
SELECT NOW() + INTERVAL -3 DAY
SELECT NOW() - INTERVAL +3 DAY
·MICROSECOND
SELECT NOW(), NOW()+INTERVAL 1 MICROSECOND
==> 2007-04-20 14:35:04.0 2007-04-20 14:35:04.000000001
·SECOND
SELECT NOW(), NOW()+INTERVAL 1 SECOND
==> 2007-04-20 14:38:40.0 2007-04-20 14:38:41.0
·MINUTE
SELECT NOW(), NOW()+INTERVAL 1 MINUTE
==> 2007-04-20 14:38:40.0 2007-04-20 14:38:41.0
·HOUR
SELECT NOW(), NOW()+INTERVAL 1 HOUR
==> 2007-04-20 14:41:23.0 2007-04-20 15:41:23.0
·DAY
SELECT NOW(), NOW()+INTERVAL 1 DAY
==> 2007-04-20 14:42:04.0 2007-04-21 14:42:04.0
·EXTRACT(type FROM date) (重点！！！)
·说明：
EXTRACT()函数所使用的时间间隔类型说明符同 DATE_ADD()或DATE_SUB()
的相同,但它从日期中提取其部分，而不是执行日期运算。
·范例：
SELECT NOW(), EXTRACT(YEAR FROM NOW())
==> 2007-04-20 15:06:46.0 2007
SELECT NOW(), EXTRACT(MONTH FROM NOW())
==> 2007-04-20 15:08:24.0 4
SELECT NOW(), EXTRACT(DAY FROM NOW())
==> 2007-04-20 15:07:31.0 20
·DATE_ADD(date, INERVAL expr type) DATE_SUB(date, INERVAL expr type)
·说明：参数date 只取日期部分。零值日期 返回 0，如'2007-04-00'。
激活 INTERVAL 后，DATE_ADD等价于 ADDDATE(),DATE_SUB等价于SUB_DATE()
·范例：
SELECT NOW(), DATE_ADD(CURDATE(),INTERVAL 3 DAY)
SELECT NOW(), DATE_SUB(CURDATE(),INTERVAL 3 DAY)
SELECT NOW(), ADDDATE(CURDATE(),INTERVAL 3 DAY)
SELECT NOW(), SUBDATE(CURDATE(),INTERVAL 3 DAY)
SELECT NOW(), ADDDATE(CURDATE(), 3)
SELECT NOW(), SUBDATE(CURDATE(), 3)
·CONVERT_TZ()
·说明：将时间日期值dt 从from_tz 给出的时区转到to_tz给出的时区，
然后返回结果值。若自变量无效，则这个函数会返回 NULL。
·范例：
SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET')
SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00')
·ADDTIME(expr,expr2)
·说明：ADDTIME()将 expr2添加至expr 然后返回结果。
expr 是一个时间或时间日期表达式，而expr2 是一个时间表达式。
·范例：
SELECT ADDTIME('1997-12-31 23:59:59.999999', '1 1:1:1.000002')
SELECT ADDTIME('01:00:00.999999', '02:00:00.999998')
·SUBTIME(expr,expr2)
SUBTIME()从expr 中提取expr2 ，然后返回结果。expr 是一个时间或日期
时间表达式，而xpr2 是一个时间表达式。
SELECT NOW(), SUBTIME(NOW(), '1 1:1:1.000002')
==> 2007-04-20 16:00:35.0 2007-04-19 14:59:33.000999998
·CURDATE(), CURTIME(),CURRENT_DATE(),CURRENT_TIME()
·说明：
CURDATE()将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，
CURTIME()将当前时间以'HH:MM:SS'或 HHMMSS 的格式返回，
具体格式根据函数用在字符串或是数字语境中而定。
CURDATE()等价于CURRENT_DATE()
CURTIME()等价于CURRENT_TIME()
·范例：
SELECT CURDATE() ==> 2007-4-20
SELECT CURDATE()+0 ==> 20070420
SELECT CURRENT_TIME() ==> 03:59:28
SELECT CURRENT_TIME()+0 ==> 035928
·DATE(expr)
·说明：提取日期或时间日期表达式expr中的日期部分。
·范例：
SELECT DATE('2007-04-20 01:02:03') ==> 2007-04-20
SELECT NOW(), DATE(NOW()) ==> 2007-04-20 04:03:30.0 2007-4-20
·DATEDIFF(expr,expr2)
·说明：返回起始时间 expr和结束时间expr2之间的天数。
Expr和expr2 为日期或 date-and-time 表达式。
计算中只用到这些值的日期部分。
·范例：
SELECT NOW(), DATEDIFF(NOW(),'2007-04-01')
==> 2007-04-20 14:25:33.0 19
SELECT NOW(), DATEDIFF('2007-04-01', NOW())
==> 2007-04-20 14:25:33.0 -19
·DATE_FORMAT(date,format) （重点！！！）
·说明：根据format 字符串安排date 值的格式。
以下说明符可用在 format 字符串中：
======================================================================
说明符 说明
======================================================================
%a 工作日的缩写名称 (Sun..Sat)
----------------------------------------------------------------------
%b 月份的缩写名称 (Jan..Dec)
----------------------------------------------------------------------
%c 月份，数字形式(0..12)
----------------------------------------------------------------------
%D 带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, ...)
----------------------------------------------------------------------
%d 该月日期, 数字形式 (00..31)
----------------------------------------------------------------------
%e 该月日期, 数字形式(0..31)
----------------------------------------------------------------------
%f 微秒 (000000..999999)
----------------------------------------------------------------------
%H 小时(00..23)
----------------------------------------------------------------------
%h 小时(01..12)
----------------------------------------------------------------------
%I 小时 (01..12)
----------------------------------------------------------------------
%i 分钟,数字形式 (00..59)
----------------------------------------------------------------------
%j 一年中的天数 (001..366)
----------------------------------------------------------------------
%k 小时 (0..23)
----------------------------------------------------------------------
%l 小时 (1..12)
----------------------------------------------------------------------
%M 月份名称 (January..December)
----------------------------------------------------------------------
%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位数)
----------------------------------------------------------------------
%% ‘%’文字字符
======================================================================
所有其它字符都被复制到结果中，无需作出解释。
注意， ‘%’字符要求在格式指定符之前。
月份和日期说明符的范围从零开始，原因是 MySQL允许存储诸如 '2004-00-00'的不完全日期.
·%Y-%m-%d %H:%i:%S.%f 为最常用的格式
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S.%f')
·TIME_FORMAT(time,format)
同 DATE_FORMAT()
·STR_TO_DATE(str,format)
这是DATE_FORMAT() 函数的倒转。
·DAY(date)
DAY() 和DAYOFMONTH()的意义相同。
SELECT NOW() ,DAY(NOW()) ==> 2007-04-20 14:58:37.0 20
·DAYNAME(date)
返回date 对应的工作日名称。
SELECT NOW() ,DAYNAME(NOW()) ==> 2007-04-20 14:58:05.0 Friday
·DAYOFMONTH(date)
返回date 对应的该月日期，范围是从 1到31。与DAY()意义相同。
SELECT NOW() ,DAYOFMONTH(NOW()) ==> 2007-04-20 15:00:53.0 20
·DAYOFWEEK(date)
返回date (1 = 周日, 2 = 周一, ..., 7 = 周六)对应的工作日索引。
这些索引值符合 ODBC标准
SELECT NOW() ,DAYOFWEEK(NOW()) ==> 2007-04-20 15:01:24.0 6
·DAYOFYEAR(date)
返回date 对应的一年中的天数，范围是从 1到366。
参考 MAKEDATE(year,dayofyear)
SELECT NOW() ,DAYOFYEAR(NOW()) ==> 2007-04-20 15:02:53.0 110
·HOUR(time)
返回time 对应的小时数。对于日时值的返回值范围是从 0 到 23 。
·LAST_DAY(date)
获取一个日期或日期时间值，返回该月最后一天对应的值。
若参数无效，则返回NULL。
·MAKEDATE(year,dayofyear)
给出年份值和一年中的天数值，返回一个日期。
dayofyear 必须大于 0 ，否则结果为 NULL。
参考DAYOFYEAR(date)
SELECT NOW(), MAKEDATE(YEAR(NOW()),DAYOFYEAR(NOW()))
==> 2007-04-20 15:47:03.0 2007-04-20
·MAKETIME(hour,minute,second)
返回由hour、 minute和second 参数计算得出的时间值。
参考：HOUR(),MINUTE(),SECOND()
·FROM_DAYS()
·TO_DAYS()
SELECT FROM_DAYS(TO_DAYS(NOW())) ==> 2007-04-20
·MICROSECOND(expr)
·从时间或日期时间表达式expr返回微秒值，其数字范围从 0到 999999。
SELECT MICROSECOND('12:00:00.123456') ==> 123456
·MINUTE(time)
返回 time 对应的分钟数,范围是从 0 到 59。
·MONTH(date)
返回date 对应的月份，范围时从 1 到 12。
·MONTHNAME(date)
返回date 对应月份的全名。
·QUARTER(date)
返回date 对应的一年中的季度值，范围是从 1到 4。
·SYSDATE()
返回当前日期和时间值,格式为'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS,
具体格式根据函数是否用在字符串或数字语境而定。
在一个存储程序或触发器中, SYSDATE()返回其执行的时间, 而非存储成都
或触发语句开始执行的时间。这个NOW()的运作有所不同。
·TIME(expr)
提取一个时间或日期时间表达式的时间部分，并将其以字符串形式返回。
SELECT NOW(), TIME(NOW())
==> 2007-04-20 16:03:30.0 16:03:30
·TIMEDIFF(expr,expr2)
TIMEDIFF() 返回起始时间 expr 和结束时间expr2 之间的时间。
expr 和expr2 为时间或 date-and-time 表达式,两个的类型必须一样。
·TIMESTAMP(expr) , TIMESTAMP(expr,expr2)
对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.
对于两个参数, 它将时间表达式 expr2 添加到日期或日期时间表达式 expr 中，
将theresult作为日期时间值返回。
SELECT TIMESTAMP('2007-04-20')
==> 2007-04-20 00:00:00.0
SELECT TIMESTAMP('2007-04-20 12:00:00','12:00:00')
==> 2007-04-21 00:00:00
·TIMESTAMPADD(interval,int_expr,datetime_expr)
将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。
int_expr 的单位被时间间隔参数给定，该参数必须是以下值的其中一个：
FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、
WEEK、 MONTH、 QUARTER或 YEAR。
SELECT TIMESTAMPADD(MINUTE,1,'2007-04-20 12:00:00')
==> 2007-04-20 12:01:00
·TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the
之间的整数差。其结果的单位由interval 参数给出。interval 的法
定值同TIMESTAMPADD()函数说明中所列出的相同。
SELECT TIMESTAMPDIFF(MONTH,'2007-04-20','2003-05-01')
==> -47
SELECT TIMESTAMPDIFF(YEAR,'2001-01-01','2007-04-20')
==> 6
·TIME_TO_SEC(time)
返回已转化为秒的time参数。
·TO_DAYS(date)
给定一个日期date, 返回一个天数 (从年份0开始的天数 )。
·UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
若无参数调用，则返回一个Unix timestamp ('1970-01-01 00:00:00'
GMT 之后的秒数) 作为无符号整数。若用date 来调用UNIX_TIMESTAMP()，
它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。
date 可以是一个DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或
一个当地时间的YYMMDD 或YYYMMDD格式的数字。
SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW())
==> 2007-04-20 16:27:24.0 1177057644 1177057644
·UTC_DATE, UTC_DATE()
返回当前 UTC日期值，其格式为 'YYYY-MM-DD' 或 YYYYMMDD，
具体格式取决于函数是否用在字符串或数字语境中。
SELECT UTC_DATE(), UTC_DATE() + 0
==> 2007-04-20 20070420
·UTC_TIMESTAMP, UTC_TIMESTAMP()
返回当前UTC日期及时间值，格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS，
具体格式根据该函数是否用在字符串或数字语境而定。
SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0
==> 2007-04-20 08:30:05.0 20070420083005
·WEEK(date[,mode])
第一天
Mode 工作日 范围 Week 1 为第一周 ...
0 周日 0-53 本年度中有一个周日
1 周一 0-53 本年度中有3天以上
2 周日 1-53 本年度中有一个周日
3 周一 1-53 本年度中有3天以上
4 周日 0-53 本年度中有3天以上
5 周一 0-53 本年度中有一个周一
6 周日 1-53 本年度中有3天以上
7 周一 1-53 本年度中有一个周一
·WEEKDAY(date)
返回date (0 = 周一, 1 = 周二, ... 6 = 周日)对应的工作日索引
weekday index for
SELECT WEEKDAY(NOW()) ==> 4
·WEEKOFYEAR(date)
将该日期的阳历周以数字形式返回，范围是从1到53。
它是一个兼容度函数，相当于WEEK(date,3)。
·YEAR(date)
返回date 对应的年份,范围是从1000到9999。
·YEARWEEK(date), YEARWEEK(date,start)
返回一个日期对应的年或周。start参数的工作同 start参数对 WEEK()的工作相同。
结果中的年份可以和该年的第一周和最后一周对应的日期参数有所不同。
SELECT YEARWEEK('2007-01-01')
==> 200653
注意，周数和WEEK()函数队可选参数0或 1可能会返回的(0) w有所不同，
原因是此时 WEEK() 返回给定年份的语境中的周。
|
