mysql 日期時間處理函數，mysql日期處理函數

mysql 日期時間處理函數，mysql日期處理函數

• ADDDATE(date,interval expr unit)，ADDDATE(expr,days) --- 添加時間值（時區）為日期值,其為DATE_ADD()的別名

DATA_ADD(date,INTERVAL expr unit)、DATE_SUB(date,INTERVAL expr unit)

這些函數進行時間運算；date參數指定開始日期的日期或日期時間值，expr是一個表達式指定要添加或減去的值的間隔的開始日期

unit Value Expected expr Format 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:SECONDS.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE 'HOURS:MINUTES' DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS HOURS' YEAR_MONTH 'YEARS-MONTHS'

 

也可以寫作

date + INTERVAL expr unit
date - INTERVAL expr unit

 

mysql> select '2008-1-31' + interval 1 month;
+--------------------------------+
| '2008-1-31' + interval 1 month |
+--------------------------------+
| 2008-02-29                     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2008-1-31',interval 1 month);
+----------------------------------------+
| date_add('2008-1-31',interval 1 month) |
+----------------------------------------+
| 2008-02-29                             |
+----------------------------------------+
1 row in set (0.00 sec)

 

獲取一天的開始和最後的時間

mysql> select '2015-4-4 0:0:0' + interval 1 day - interval 1 second;
+-------------------------------------------------------+
| '2015-4-4 0:0:0' + interval 1 day - interval 1 second |
+-------------------------------------------------------+
| 2015-04-04 23:59:59                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

 

 

• ADDTIME(expr1,expr2) --- 增加expr2到expr1上並返回結果；expr1 是一個時間或者日期時間表達式，expr2為一個時間表達式

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'

• CONVERT_TZ() Convert from one timezone to another
• CURDATE() --- 返回當前的日期 YYYY-MM-DD，

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2015-04-03 |
+------------+
1 row in set (0.00 sec)

mysql> select curdate() + 0;
+---------------+
| curdate() + 0 |
+---------------+
|      20150403 |
+---------------+
1 row in set (0.00 sec)

 

• CURRENT_DATE(), CURRENT_DATE  ---- CURDATE() 的別名
• CURRENT_TIME(), CURRENT_TIME --- CURTIME()的別名
• CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP --- NOW()的別名
• CURTIME() --- 返回當前的時間

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 14:06:30  |
+-----------+
1 row in set (0.00 sec)

mysql> select curtime() + 0;
+---------------+
| curtime() + 0 |
+---------------+
| 140641.000000 |
+---------------+
1 row in set (0.00 sec)

 

• DATE_ADD() Add time values (intervals) to a date value
• DATE_FORMAT(date,format) 根據格式化字符串 格式化日期

格式字符串中可以使用 '%' 說明符

常用的：

　　%a -- 星期幾的縮寫  (Sun..Sat)

　　%b -- 月份的縮寫 (Jan...Dec)

　　%c -- 月份 (0--12)

　　%D -- 天數 加上了英文後綴 (0th,1sh...)

　　%d,%e --  月份中的天數 (0-31)

　　%H --  24小時制 (00- 23)

　　%h,%I -- 12小時制 （00-12）　　

　　%i -- 分鐘 (00-59)

　　%M -- 月份 (january,december)

　　%m -- 月份 (00-12)

　　%S,%s -- 秒 (00-59)

　　%Y -- 年 4位

　　%y -- 年 2位

mysql> select date_format(now(),'%Y/%m/%d %H:%m:%s');
+----------------------------------------+
| date_format(now(),'%Y/%m/%d %H:%m:%s') |
+----------------------------------------+
| 2015/04/03 14:04:05                    |
+----------------------------------------+
1 row in set (0.00 sec)

 

 

• DATE_SUB() Subtract a time value (interval) from a date
• DATE() -- 提取日期部分

mysql> select date(now());
+-----------------+
| date(curdate()) |
+-----------------+
| 2015-04-03      |
+-----------------+
1 row in set (0.00 sec)

 

• DATEDIFF(expr1,expr2) 返回expr1 - expr2之間的時間差；expr1,expr2表達式只有日期部分參與運算

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)

 

• DAY() Synonym for DAYOFMONTH()
• DAYNAME() Return the name of the weekday
• DAYOFMONTH() Return the day of the month (0-31)
• DAYOFWEEK() Return the weekday index of the argument
• DAYOFYEAR() Return the day of the year (1-366)
• EXTRACT() Extract part of a date
• FROM_DAYS() Convert a day number to a date
• FROM_UNIXTIME(unix_timestamp[,format]) 將unix時間戳格式化為日期字符串

mysql> select from_unixtime(unix_timestamp(),'%Y-%m-%d');
+--------------------------------------------+
| from_unixtime(unix_timestamp(),'%Y-%m-%d') |
+--------------------------------------------+
| 2015-04-03                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

 

• GET_FORMAT() Return a date format string
• HOUR(time) -- 提取小時部分

mysql> select HOUR(now());
+-------------+
| HOUR(now()) |
+-------------+
|          14 |
+-------------+
1 row in set (0.00 sec)

 

• LAST_DAY（date）-- 返回指定月份的最後一天。如果參數無效，則返回NULL。

mysql> select last_day('2013-2-5');
+----------------------+
| last_day('2013-2-5') |
+----------------------+
| 2013-02-28           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('2004-2-5');
+----------------------+
| last_day('2004-2-5') |
+----------------------+
| 2004-02-29           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('2004-2-35');
+-----------------------+
| last_day('2004-2-35') |
+-----------------------+
| NULL                  |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

 

• LOCALTIME(), LOCALTIME Synonym for NOW()
• LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
• MAKEDATE(year,dayofyear) Create a date from the year and day of year
• MAKETIME MAKETIME(hour,minute,second)
• MICROSECOND(expr) -- 返回expr表達式的毫秒數

• MINUTE() Return the minute from the argument
• MONTH() Return the month from the date passed
• MONTHNAME(date) --- 返回月份的全稱

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| April            |
+------------------+
1 row in set (0.00 sec)

 

• NOW() -- 返回當前的日期和時間
• PERIOD_ADD() Add a period to a year-month
• PERIOD_DIFF() Return the number of months between periods
• QUARTER() Return the quarter from a date argument
• SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
• SECOND() --返回秒數 (0-59)
• STR_TO_DATE() Convert a string to a date
• SUBDATE() A synonym for DATE_SUB() when invoked with three arguments
• SUBTIME() Subtract times
• SYSDATE() Return the time at which the function executes
• TIME_FORMAT() Format as time
• TIME_TO_SEC() Return the argument converted to seconds
• TIME() Extract the time portion of the expression passed
• TIMEDIFF() Subtract time
• TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
• TIMESTAMPADD() Add an interval to a datetime expression
• TIMESTAMPDIFF() Subtract an interval from a datetime expression
• TO_DAYS() Return the date argument converted to days
• TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
• UNIX_TIMESTAMP() -- 返回unix時間戳

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1428042298 |
+------------------+
1 row in set (0.00 sec)

 

• UTC_DATE() Return the current UTC date
• UTC_TIME() Return the current UTC time
• UTC_TIMESTAMP() Return the current UTC date and time
• WEEK() Return the week number
• WEEKDAY() Return the weekday index
• WEEKOFYEAR() Return the calendar week of the date (0-53)
• YEAR() --- 獲取年份

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2015 |
+-------------+
1 row in set (0.00 sec)

 

• YEARWEEK() Return the year and week