MySQL_日期函數彙總

    如果轉載，請注明博文來源： www.cnblogs.com/xinysu/   ，版權歸 部落格園 蘇家小蘿蔔 所有。望各位支援！

      關于MySQL日期時間函數，每回總能發現一些新特性，索性做一次統一的整理，補充下筆記庫。可以會有所遺漏，歡迎補充。

      本文整理了30個時間日期函數，分為4個段落分析，詳見下文。

1 擷取時間函數

• 擷取目前時間
  • sysdate()
  • now() , current_timestamp(), current_timestamp，LOCALTIME ，LOCALTIME() ，LOCALTIMESTAMP ，LOCALTIMESTAMP()
• 休息ns
  • sleep(n)
• 例子
  • • 相同點：擷取目前時刻

      • 

    • 差別：傳回時刻是什麼時候，執行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 年

      • 

• • 日期減去指定的時間間隔。
  • 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 )
    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_sub一樣

• 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
    • 将時間轉化為時間戳

    • 

  • • 

  • 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)
  • 前者根據時間轉化為秒數，後者根據秒數轉化為時間

  • 

如果轉載，請注明博文來源： www.cnblogs.com/xinysu/ ，版權歸 部落格園 蘇家小蘿蔔 所有。望各位支援！