MySQL基础--时间和日期

MySQL中日期和时间相关资料的汇总

日期和时间类型

    MySQL中支持的5种类型的格式及取值范围：

• DATE   'YYYY-MM-DD'  '1000-01-01' TO '9999-12-31'

• DATETIME  'YYYY-MM-DD HH:MM:SS'   '1000-01-01 00:00:00' to'9999-12-31 23:59:59'

• TIMESTAMP  'YYYY-MM-DD HH:MM:SS' '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
• TIME    'HH:MM:SS'    '-838:59:59' to '838:59:59'  
• YEAR    'YYYY'     '1901' to '2155'

其中，TIME的范围大于24的原因是TIME也可以用于表示2个时间之间的时间差。YEAR还有1位和2位的格式，本文不讨论，详细见手册

时间获取

1. 当前时间

now()  CURDATE()  CURTIME()  sysdate（）   
      函数now()返回当前日期或时间的函数在每次询问执行开始时计算一次。在一次查询中，使用now()则多次查询时间一样，但sysdate()返回为动态时间。 
    2. 0时区时间

UTC_DATE()  UTC_TIME()  UTC_TIMESTAMP()  
3.时间截取

date（expr） 提取日期部分

hour(time) 提取时间中小时 seconde（time）

month(date)提取月份

year（date）提取年份

weekday(date)获取星期

weekofyear（） 一年的第几周   

last_day(time) 返回一个月最后一天

day（expr）/ dayofmonth(expr) 提取单独日期

dayname（expr） 提取工作日名称

dayofweek (expr) 提取一周的第几天

dayofyear(expr) 提取一年的第几天

EXTRACT(type FROM date) 获取参数中某种类型

时间计算

1.adddate 和 subdate

ADDDATE(date,INTERVAL expr type)  同义 date_add(date,interval expr type)

+---------------------------------+

|adddate(now(),interval 2 month)|
+---------------------------------+
|2011-12-18 11:33:41|
+---------------------------------+
ADDDATE(expr,days)
+---------------------+
|adddate(now(),2)|
+---------------------+
|2011-10-20 11:33:09|
+---------------------------------+

    subdate(),date_sub()同法相同

2.addtime

ADDTIME(expr,expr2)  expr为日期或时间，expr2为时间

3. convert_tz

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

4.datediff

DATEDIFF(expr,expr2) 计算2个参数的时间差，单位为天

+------------------------------+
|datediff(now(),'2011-10-20')|
+------------------------------+
|-2|
+------------------------------+

5. date_fromat 和  str_to_date 

DATE_FORMAT(date,format)
+---------------------------+
|date_format(now(),'%Y%m')|
+---------------------------+
|201110|
+---------------------------+

逆转函数：STR_TO_DATE(str,format) 将字符串转换为日期

6.makedate

MAKEDATE(year,dayofyear)

给出年份值和一年中的天数值，返回一个日期。dayofyear 必须大于 0 ，否则结果为 NULL

+--------------------+
|makedate(2011,123)|
+--------------------+
|2011-05-03|
+--------------------+

7.maketime

MAKETIME(hour,minute,second)

返回由hour、 minute和second 参数计算得出的时间值。

8.timediff

TIMEDIFF(expr,expr2)

TIMEDIFF() 返回起始时间 expr 和结束时间expr2 之间的时间。 expr 和expr2 为时间或 date-and-time 表达式,两个的类型必须一样。

9.timestamp

TIMESTAMP(expr)一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回

TIMESTAMP(expr,expr2) 两个参数, 它将时间表达式 expr2 添加到日期或日期时间表达式 expr 中，将theresult作为日期时间值返回。

10.timestampadd

TIMESTAMPADD(interval,int_expr,datetime_expr)

将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。 int_expr 的单位被时间间隔参数给定，该参数必须是以下值的其中一个： FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、 MONTH、QUARTER或 YEAR

11.timstampdiff

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同

12.time_to_sec

TIME_TO_SEC(time)

返回已转化为秒的time参数。

mysql>SELECTTIME_TO_SEC('22:23:00');
->80580

附录，日期格式：

%S, %s 两位数字形式的秒（ 00,01, . . ., 59）

%i 两位数字形式的分（ 00,01, . . ., 59）

%H 两位数字形式的小时，24 小时（00,01, . . ., 23）

%h, %I 两位数字形式的小时，12 小时（01,02, . . ., 12）

%k 数字形式的小时，24 小时（0,1, . . ., 23）

%l 数字形式的小时，12 小时（1, 2, . . ., 12）

%T 24 小时的时间形式（h h : m m : s s）

%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）

%p AM 或P M

%W 一周中每一天的名称（ S u n d a y, Monday, . . ., Saturday）

%a 一周中每一天名称的缩写（ Sun, Mon, . . ., Sat）

%d 两位数字表示月中的天数（ 00, 01, . . ., 31）

%e 数字形式表示月中的天数（ 1, 2， . . ., 31）

%D 英文后缀表示月中的天数（ 1st, 2nd, 3rd, . . .）

%w 以数字形式表示周中的天数（ 0 = S u n d a y, 1=Monday, . . ., 6=Saturday）

%j 以三位数字表示年中的天数（ 001, 002, . . ., 366）

% U 周（0, 1, 52），其中Sunday 为周中的第一天

%u 周（0, 1, 52），其中Monday 为周中的第一天

%M 月名（J a n u a r y, February, . . ., December）

%b 缩写的月名（ J a n u a r y, February, . . ., December）

%m 两位数字表示的月份（ 01, 02, . . ., 12）

%c 数字表示的月份（ 1, 2, . . ., 12）

%Y 四位数字表示的年份

%y 两位数字表示的年份

%% 直接值“%”