Chinaunix首页 | 论坛 | 博客
  • 博客访问: 187096
  • 博文数量: 52
  • 博客积分: 120
  • 博客等级: 民兵
  • 技术积分: 1189
  • 用 户 组: 普通用户
  • 注册时间: 2011-08-03 15:41
个人简介

MySQL DBA

文章分类

全部博文(52)

文章存档

2013年(51)

2011年(1)

分类: Mysql/postgreSQL

2013-03-07 13:03:28

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)


  1. +---------------------------------+

  2. |adddate(now(),interval 2 month)|
  3. +---------------------------------+
  4. |2011-12-18 11:33:41|
  5. +---------------------------------+
  6. ADDDATE(expr,days)
  7. +---------------------+
  8. |adddate(now(),2)|
  9. +---------------------+
  10. |2011-10-20 11:33:09|
  11. +---------------------------------+

    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个参数的时间差,单位为天


  1. +------------------------------+
  2. |datediff(now(),'2011-10-20')|
  3. +------------------------------+
  4. |-2|
  5. +------------------------------+


5. date_fromat 和  str_to_date 


  1. DATE_FORMAT(date,format)
  2. +---------------------------+
  3. |date_format(now(),'%Y%m')|
  4. +---------------------------+
  5. |201110|
  6. +---------------------------+

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


6.makedate

MAKEDATE(year,dayofyear)

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


  1. +--------------------+
  2. |makedate(2011,123)|
  3. +--------------------+
  4. |2011-05-03|
  5. +--------------------+


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参数。


  1. mysql>SELECTTIME_TO_SEC('22:23:00');
  2. ->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 两位数字表示的年份

%% 直接值“%”

阅读(1434) | 评论(0) | 转发(0) |
0

上一篇:Linux 文件传输

下一篇:Xtrabackup

给主人留下些什么吧!~~