MySQL_日期函数汇总

 
 


 
    如果转载,请注明博文来源: www.cnblogs.com/xinysu/   ,版权归 博客园 苏家小萝卜 所有。望各位支持!
  


 
      关于MySQL日期时间函数,每回总能发现一些新特性,索性做一次统一的整理,补充下笔记库。可以会有所遗漏,欢迎补充。
      本文整理了30个时间日期函数,分为4个段落分析,详见下文。
 

1 获取时间函数

  • 获取当前时间
    • sysdate()
    • now() , current_timestamp(), current_timestamp,LOCALTIME ,LOCALTIME() ,LOCALTIMESTAMP ,LOCALTIMESTAMP()
  • 休息ns
    • sleep(n)
  • 例子
    • now() , current_timestamp(), current_timestamp,LOCALTIME ,LOCALTIME() ,LOCALTIMESTAMP ,LOCALTIMESTAMP()
      • 相同点:获取当前时刻
      • 区别:返回时刻是什么时候,执行SQL还是调用该函数的时间值
        • sysdate(),返回函数调用时刻的时间值
        • now() ,current_timestamp()/current_timestamp ,LOCALTIME ,LOCALTIME() ,LOCALTIMESTAMP ,LOCALTIMESTAMP()  返回语句调用时的时间值;
        • 使用过程中,请明确清理这三者的区别

2 时间计算函数

  • 时间之差
    • datediff(date1, date2 ),timediff(time1 , time2 ),  timestampdiff(interval,datetime_expr1,datetime_expr2)
  • 时间加减法
    • date_sub( date, INTERVAL expr type )
    • DATE_ADD(date,INTERVAL expr type)
    • adddate(date,INTERVAL expr type ) ,timestampadd(interval, count, timestamp)
  • 例子
    • datediff( date1, date2 )
      • 两个日期相减得相差天数,注意,两个需是一样的格式
    • timediff( time1 , time2 )
      • 两个时间相减的相差时间,注意,需要是一样的格式
    • TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
      • 两个时间之差
      • interval 可以是:
        • FRAC_SECOND 毫秒 ,SECOND 秒 ,MINUTE 分钟 ,HOUR 小时
        • DAY 天 ,WEEK 星期 ,MONTH 月 ,QUARTER 季度 ,YEAR 年
  • date_sub( date, INTERVAL expr type )
    • 日期减去指定的时间间隔。
    • expr为正,则为减去这个时间间隔;expr为负,减去负数,等于加法
    • date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。type可以参考下方
    • @date = '2017-09-09 09:09:09'
    • Type 值                
      MICROSECOND 十分之一秒 , ±n date_sub( @date ,INTERVAL 2 MICROSECOND ) 2017-09-09 09:09:08.999998
      SECOND date_sub( @date ,INTERVAL 2 SECOND ) 2017-09-09 09:09:07
      MINUTE 分钟 date_sub( @date ,INTERVAL 2 MINUTE ) 2017-09-09 09:07:09
      HOUR 小时 date_sub( @date ,INTERVAL 2 HOUR) 2017-09-09 07:07:09
      DAY date_sub( @date ,INTERVAL 2 DAY ) 2017-09-07 09:09:09
      WEEK date_sub( @date ,INTERVAL 2 WEEK ) 2017-08-26 09:09:09
      MONTH date_sub( @date ,INTERVAL 2 MONTH ) 2017-07-09 09:09:09
      QUARTER(季度,3个月) 季度 date_sub( @date ,INTERVAL 2 QUARTER ) 2017-03-09 09:09:09
      YEAR date_sub( @date ,INTERVAL 2 YEAR ) 2015-09-09 09:09:09
      SECOND_MICROSECOND 表达式,从秒到十分之1秒 date_sub( @date ,INTERVAL '2.2' SECOND_MICROSECOND ) 2017-09-09 09:09:06.980000
      MINUTE_MICROSECOND 表达式,从分钟到十分之一秒 date_sub( @date ,INTERVAL '2:2.2' MINUTE_MICROSECOND ) 2017-09-09 09:07:06.800000
      MINUTE_SECOND 表达式,从分钟到秒 date_sub( @date ,INTERVAL '2:2' MINUTE_SECOND ) 2017-09-09 09:07:07
      HOUR_MICROSECOND 表达式,从小时到十分之一秒 date_sub( @date ,INTERVAL '2:2:2.2' HOUR_MICROSECOND ) 2017-09-09 07:07:06.800000
      HOUR_SECOND 表达式,小时到秒 date_sub( @date ,INTERVAL  '2:2:2'  HOUR_SECOND ) 2017-09-09 07:07:07
      HOUR_MINUTE 表达式,小时到分钟 date_sub( @date ,INTERVAL '2:2'  HOUR_MINUTE ) 2017-09-09 07:07:09
      DAY_MICROSECOND 表达式,天到十分之一秒, date_sub( @date ,INTERVAL '2 2:2:2.2' DAY_MICROSECOND ) 2017-09-07 07:07:06.800000
      DAY_SECOND 表达式,天到秒, date_sub( @date ,INTERVAL  '2 2:2:2'  DAY_SECOND ) 2017-09-07 07:07:07
      DAY_MINUTE 表达式,天到分钟, date_sub( @date ,INTERVAL '2 2:2 ' DAY_MINUTE ) 2017-09-07 07:07:09
      DAY_HOUR 表达式,天到小时, date_sub( @date ,INTERVAL '2 2'  DAY_HOUR ) 2017-09-07 07:09:09
      YEAR_MONTH 表达式,年到月, date_sub( @date ,INTERVAL '2 2' YEAR_MONTH ) 2015-07-09 09:09:09
  • DATE_ADD(date,INTERVAL expr type)
    • 日期增加指定的时间间隔,使用方法 跟 date_sub一样
  • adddate(date,INTERVAL expr type )
    • adddate(date,INTERVAL expr type )
      • 日期增加指定的时间间隔,使用方法 DATE_ADD  一样
    • adddate(date, n )
      • 日期增加 n 天
  • timestampadd(interval, count, timestamp)
    • 时间增加函数
      • Year
      • quarter
      • Month
      • Day
      • Week
      • Hour
      • minute
      • second
      • millisecond

3 时间转换函数

  • from_unixtime(),unix_timestamp()
    • 前者将 时间戳 转化为 日期时间 格式,后者将 日期时间 格式转化为时间戳
  • makedate(year, day_of_year),maketime(hour,minute,second)
    • makedate 根据 年份跟年份中的第几天,转化为日期
    • maketime 根据 小时 分钟 秒 ,转化为时间
  • date(datetime),timestamp(date [,time])
    • 前者将 日期时间 格式 转化为 日期格式(仅含有 年月日),后者 将日期 转化为 日期时间 格式
  • convert_tz(datetime, from_tz, to_tz)
    • 根据 A 时区的时间,返回在B时区 的时间是多少
  • date_format(datetime,format),str_to_date(string,format)
    • 前者将 日期时间 转化为 指定格式的文本,后者将 文本 转化为制定格式的 日期时间
    • from_unixtime
      • 将时间戳转化为时间
    • unix_timestamp
      • 将时间转化为时间戳
    • makedate(year, day_of_year),maketime(hour,minute,second)
      • makedate 根据 年份跟年份中的第几天,转化为日期
      • maketime 根据 小时 分钟 秒 ,转化为时间
    • date(datetime)
      • 日期时间格式 转化为 日期格式
    • timestamp(date),timestamp(date,time)
      • 时间戳到转换
    • convert_tz(datetime,from_tz,to_tz)
      • 时区转化
      • datetime为from_zt的时区,返回to_tz时区的时间
    • str_to_date(string,format)
      • 将 string 转化为 日期格式,string 哪几个字符是年月日时间,由format匹配
    • date_format
        • %X     年,其中的星期日是周的第一天,4 位,与 %V 使用
        • %x     年,其中的星期一是周的第一天,4 位,与 %v 使用
        • %Y     年,4 位
        • %y     年,2 位
        • %b     缩写月名
        • %c     月,数值
        • %M     月名
        • %m     月,数值(00-12)
        • %D     带有英文前缀的月中的天
        • %d     月的天,数值(00-31)
        • %e     月的天,数值(0-31)
        • %j     年的天 (001-366)
      • 时间模块
        • %T     时间, 24-小时 (hh:mm:ss)
        • %r     时间,12-小时(hh:mm:ss AM 或 PM)
      • 小时
        • %H     小时 (00-23)
        • %k     小时 (0-23)
        • %h     小时 (01-12)
        • %I     小时 (01-12) #%h跟%I,没区别 ( 大写的 i )
        • %l     小时 (1-12) 小写的 L
        • %p     AM 或 PM
      • 分钟
        • %i     分钟,数值(00-59)
        • %S     秒(00-59)
        • %s     秒(00-59)
        • %f     微秒
        • %a     缩写星期名
        • %W     星期名,英文全称 Saturday
        • %w     周的天 (0=星期日, 6=星期六,0-6)
        • %U     周 (00-52) 星期日是一周的第一天
        • %u     周 (00-52) 星期一是一周的第一天
        • %V     周 (01-53) 星期日是一周的第一天,与 %X 使用
        • %v     周 (01-53) 星期一是一周的第一天,与 %x 使用
      • 实际应用
        • SELECT NOW(),DATE_FORMAT(NOW(),'%Y-%m-%d') '%Y-%m-%d';

4 获取部分函数

  • 获取第几天
    • to_days(datetime ),dayofyear(datetime ),dayofmonth(datetime)
    • todays返回从 '0000-00-00' 到datetime之间隔了多少天
    • dayofyear,返回 datetime 这一天是一年中的第几天
    • dayofmonth,返回 datetime 这一天是 一个月中的第几天
  • 秒转化
    • time_to_sec(time),sec_to_time(number)
    • 前者根据时间转化为秒数,后者根据秒数转化为时间
    •  

posted @ 2017-09-26 11:44 苏家小萝卜 阅读(...) 评论(...) 编辑 收藏
levels of contents