mysql常用日期時間/數值函數 1.日期時間函數 時間轉化秒函數:time_to_sec mysql> select time_to_sec('01:01:01'); +-------------------------+ | time_to_sec('01:01:01') | +-------------------------+ | 3661 | +-------------------------+ 1 row in set (0.00 sec) 秒轉化時間函數:sec_to_time mysql> select sec_to_time(3661); +-------------------+ | sec_to_time(3661) | +-------------------+ | 01:01:01 | +-------------------+ 1 row in set (0.00 sec) 日期轉為天數函數:to_days mysql> select to_days('0000-00-00'); +-----------------------+ | to_days('0000-00-00') | +-----------------------+ | NULL | +-----------------------+ 1 row in set, 1 warning (0.01 sec) mysql> select to_days('0001-01-01'); +-----------------------+ | to_days('0001-01-01') | +-----------------------+ | 366 | +-----------------------+ 1 row in set (0.00 sec) 天數轉化日期函數：from_days mysql> select from_days(0); +--------------+ | from_days(0) | +--------------+ | 0000-00-00 | +--------------+ 1 row in set (0.00 sec) mysql> select from_days(366); +----------------+ | from_days(366) | +----------------+ | 0001-01-01 | +----------------+ 1 row in set (0.00 sec) 字符串轉換為日期函數：str_to_date mysql> select str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s'); +--------------------------------------------------------+ | str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s') | +--------------------------------------------------------+ | 2013-01-01 01:21:01 | +--------------------------------------------------------+ 1 row in set (0.00 sec) 日期轉換為字符串函數：date_format mysql> select date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s'); +----------------------------------------------------+ | date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s') | +----------------------------------------------------+ | 20130101 012101 | +----------------------------------------------------+ 1 row in set (0.00 sec) 時間轉換為字符串函數：time_format mysql> select time_format('01:21:01','%H%i%s'); +----------------------------------+ | time_format('01:21:01','%H%i%s') | +----------------------------------+ | 012101 | +----------------------------------+ 1 row in set (0.00 sec) 說明： 日期時間格式參數如下： %M 月名字(January……December) %W 星期名字(Sunday……Saturday) %D 有英語前綴的月份的日期(1st, 2nd, 3rd, 等等。） %Y 年, 數字, 4 位 %y 年, 數字, 2 位 %a 縮寫的星期名字(Sun……Sat) %d 月份中的天數, 數字(00……31) %e 月份中的天數, 數字(0……31) %m 月, 數字(01……12) %c 月, 數字(1……12) %b 縮寫的月份名字(Jan……Dec) %j 一年中的天數(001……366) %H 小時(00……23) %k 小時(0……23) %h 小時(01……12) %I 小時(01……12) %l 小時(1……12) %i 分鐘, 數字(00……59) %r 時間,12 小時(hh:mm:ss [AP]M) %T 時間,24 小時(hh:mm:ss) %S 秒(00……59) %s 秒(00……59) %p AM或PM %w 一個星期中的天數(0=Sunday ……6=Saturday ） %U 星期(0……52), 這裡星期天是星期的第一天 %u 星期(0……52), 這裡星期一是星期的第一天 %% 一個文字“%”。 提取表達式的日期部分 mysql> select date(now()); +-------------+ | date(now()) | +-------------+ | 2013-05-16 | +-------------+ 1 row in set (0.00 sec) 返回表達式的星期索引(0=星期一，1=星期二, ……6= 星期天)。 mysql> select weekday(now()); +----------------+ | weekday(now()) | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) 返回表達式是一年的第幾周 mysql> select week(now()); +-------------+ | week(now()) | +-------------+ | 19 | +-------------+ 1 row in set (0.00 sec) WEEK()允許指定星期是否開始於星期天或星期一。如果第二個參數是0，星期從星期天開始，如果第二個參數是1， 從星期一開始，如下所示： mysql> select week(now(),0); +---------------+ | week(now(),0) | +---------------+ | 19 | +---------------+ 1 row in set (0.00 sec) mysql> select week(now(),1); +---------------+ | week(now(),1) | +---------------+ | 20 | +---------------+ 1 row in set (0.00 sec) 返回表達式一年中季度 mysql> select quarter(now()); +----------------+ | quarter(now()) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec) 返回表達式一周的第一天 mysql> select dayofweek(now()); +------------------+ | dayofweek(now()) | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec) 返回表達式一個月的第幾天 mysql> select dayofmonth(now()); +-------------------+ | dayofmonth(now()) | +-------------------+ | 16 | +-------------------+ 1 row in set (0.00 sec) 返回表達式一年的第幾天 mysql> select dayofyear(now()); +------------------+ | dayofyear(now()) | +------------------+ | 136 | +------------------+ 1 row in set (0.00 sec) 返回表達式的星期名字 mysql> select dayname(now()); +----------------+ | dayname(now()) | +----------------+ | Thursday | +----------------+ 1 row in set (0.00 sec) 返回表達式月份的名字 mysql> select monthname(now()); +------------------+ | monthname(now()) | +------------------+ | May | +------------------+ 1 row in set (0.00 sec) mysql> 提取表達式的年份 mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2013 | +-------------+ 1 row in set (0.00 sec) 提取表達式的月份 mysql> select month(now()); +--------------+ | month(now()) | +--------------+ | 5 | +--------------+ 1 row in set (0.01 sec) 提取表達式的天數 mysql> select day(now()); +------------+ | day(now()) | +------------+ | 16 | +------------+ 1 row in set (0.00 sec) 提取表達式的小時 mysql> select hour(now()); +-------------+ | hour(now()) | +-------------+ | 16 | +-------------+ 1 row in set (0.00 sec) 提取表達式的分鐘 mysql> select minute(now()); +---------------+ | minute(now()) | +---------------+ | 31 | +---------------+ 1 row in set (0.00 sec) 提取表達式的秒數 mysql> select second(now()); +---------------+ | second(now()) | +---------------+ | 34 | +---------------+ 1 row in set (0.00 sec) 將當前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具體格式根據函數用在字符串或是數字語境中而定。 mysql> select curdate(); +------------+ | curdate() | +------------+ | 2013-05-16 | +------------+ 1 row in set (0.00 sec) mysql> select curdate()+1; +-------------+ | curdate()+1 | +-------------+ | 20130517 | +-------------+ 1 row in set (0.00 sec) 將當前時間以'HH:MM:SS'或 HHMMSS的格式返回，具體格式根據函數用在字符串或是數字語境中而定。 mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 16:43:10 | +-----------+ 1 row in set (0.00 sec) mysql> select curtime()+1; +---------------+ | curtime()+1 | +---------------+ | 164420.000000 | +---------------+ 1 row in set (0.00 sec) 獲取當前日期時間：sysdate(),now() mysql> select sysdate(),sleep(2),sysdate(); +---------------------+----------+---------------------+ | sysdate() | sleep(2) | sysdate() | +---------------------+----------+---------------------+ | 2013-05-16 17:16:04 | 0 | 2013-05-16 17:16:06 | +---------------------+----------+---------------------+ 1 row in set (2.00 sec) mysql> select now(),sleep(2),now(); +---------------------+----------+---------------------+ | now() | sleep(2) | now() | +---------------------+----------+---------------------+ | 2013-05-16 17:16:18 | 0 | 2013-05-16 17:16:18 | +---------------------+----------+---------------------+ 1 row in set (2.00 sec) 從上面可以看到sysdate和now的區別，now表示語句開始的時間，而sysdate實時的獲取時間 將當前日期按照'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式的值返回，具體格式根據函數用在字符串或是數字語境中而定。 mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2013-05-16 17:19:51 | +---------------------+ 1 row in set (0.00 sec) mysql> select current_timestamp+1; +-----------------------+ | current_timestamp+1 | +-----------------------+ | 20130516172008.000000 | +-----------------------+ 1 row in set (0.00 sec) unix_timestamp()，unix_timestamp(date) 如果沒有參數調用，返回一個Unix時間戳記(從'1970-01-01 00:00:00'GMT開始的秒數)。如果UNIX_TIMESTAMP()用一 個date參數被調用，它返回從'1970-01-01 00:00:00' GMT開始的秒數值。date可以是一個DATE字符串、一個DATETIME 字符串、一個TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地時間的一個數字。 mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1368696216 | +------------------+ 1 row in set (0.00 sec) mysql> select unix_timestamp('2013-05-16 01:01:01'); +---------------------------------------+ | unix_timestamp('2013-05-16 01:01:01') | +---------------------------------------+ | 1368637261 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> FROM_UNIXTIME(unix_timestamp) 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp參數所表示的值，具體格式根據函數用在字符串或是數字語境中而定 mysql> select from_unixtime(1368637261); +---------------------------+ | from_unixtime(1368637261) | +---------------------------+ | 2013-05-16 01:01:01 | +---------------------------+ 1 row in set (0.00 sec) mysql> select from_unixtime(1368637261)+1; +-----------------------------+ | from_unixtime(1368637261)+1 | +-----------------------------+ | 20130516010102.000000 | +-----------------------------+ 1 row in set (0.00 sec) mysql> select from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s'); +-----------------------------------------------+ | from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s') | +-----------------------------------------------+ | 2013-05-16 01:01:01 | +-----------------------------------------------+ 1 row in set (0.00 sec) 返回表達式所在月的最後一天 mysql> select last_day(now()); +-----------------+ | last_day(now()) | +-----------------+ | 2013-05-31 | +-----------------+ 1 row in set (0.00 sec) 日期加減運算 DATE_ADD(date,INTERVAL expr type) --加法 DATE_SUB(date,INTERVAL expr type) --減法 mysql> select date_add('2013-05-16 01:01:01',interval 1 second); +---------------------------------------------------+ | date_add('2013-05-16 01:01:01',interval 1 second) | +---------------------------------------------------+ | 2013-05-16 01:01:02 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2013-05-16 01:01:01',interval 1 day); +------------------------------------------------+ | date_add('2013-05-16 01:01:01',interval 1 day) | +------------------------------------------------+ | 2013-05-17 01:01:01 | +------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2013-05-16 01:01:01',interval 1 minute); +---------------------------------------------------+ | date_add('2013-05-16 01:01:01',interval 1 minute) | +---------------------------------------------------+ | 2013-05-16 01:02:01 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2013-05-16 01:01:01',interval 1 hour); +-------------------------------------------------+ | date_add('2013-05-16 01:01:01',interval 1 hour) | +-------------------------------------------------+ | 2013-05-16 02:01:01 | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2013-05-16 01:01:01',interval '1:1' minute_second); +--------------------------------------------------------------+ | date_add('2013-05-16 01:01:01',interval '1:1' minute_second) | +--------------------------------------------------------------+ | 2013-05-16 01:02:02 | +--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second); +----------------------------------------------------------------+ | date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second) | +----------------------------------------------------------------+ | 2013-05-17 02:02:02 | +----------------------------------------------------------------+ 1 row in set (0.00 sec) type值格式： SECOND 秒 SECONDS MINUTE 分鐘 MINUTES HOUR 時間 HOURS DAY 天 DAYS MONTH 月 MONTHS YEAR 年 YEARS MINUTE_SECOND 分鐘和秒 "MINUTES:SECONDS" HOUR_MINUTE 小時和分鐘 "HOURS:MINUTES" DAY_HOUR 天和小時 "DAYS HOURS" YEAR_MONTH 年和月 "YEARS-MONTHS" HOUR_SECOND 小時, 分鐘， "HOURS:MINUTES:SECONDS" DAY_MINUTE 天, 小時, 分鐘 "DAYS HOURS:MINUTES" DAY_SECOND 天, 小時, 分鐘, 秒 "DAYS HOURS:MINUTES:SECONDS" DATEDIFF(expr, expr2) 返回起始時間 expr和結束時間expr2之間的天數。Expr和expr2為日期或 date-and-time 表達式。計算中只用到這些值的日期部分。 mysql> select datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01'); +--------------------------------------------------------+ | datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01') | +--------------------------------------------------------+ | -1 | +--------------------------------------------------------+ 1 row in set (0.00 sec) 表示日期時間的數據類型： date time year datetime timestamp 在使用日期時間數據比較時常用如下 mysql> select * from tab ; +------+---------------------+ | name | createtime | +------+---------------------+ | aaaa | 2013-05-14 17:20:19 | | bbbb | 2013-04-14 17:20:36 | | bbbb | 2013-04-13 17:20:36 | | bbbb | 2013-04-15 17:20:36 | +------+---------------------+ 4 rows in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2013-05-14 17:10:26 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from tab where createtime > now(); +------+---------------------+ | name | createtime | +------+---------------------+ | aaaa | 2013-05-14 17:20:19 | +------+---------------------+ 1 row in set (0.00 sec) mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2013-05-14 17:10:49 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from tab where createtime > current_timestamp; +------+---------------------+ | name | createtime | +------+---------------------+ | aaaa | 2013-05-14 17:20:19 | +------+---------------------+ 1 row in set (0.00 sec) mysql> select * from tab where createtime> str_to_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s');; +------+---------------------+ | name | createtime | +------+---------------------+ | aaaa | 2013-05-14 17:20:19 | +------+---------------------+ 1 row in set (0.00 sec) mysql> select * from tab where createtime between str_to_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s') and str_to_date('2013-05-15 00:00:00','%Y-%m-%d %H:%i:%s') -> ; +------+---------------------+ | name | createtime | +------+---------------------+ | aaaa | 2013-05-14 17:20:19 | +------+---------------------+ 1 row in set (0.00 sec) mysql> select * from tab where createtime between '2013-05-14 00:00:00' and '2013-05-15 00:00:00'; +------+---------------------+ | name | createtime | +------+---------------------+ | aaaa | 2013-05-14 17:20:19 | +------+---------------------+ 1 row in set (0.00 sec) mysql> 2.數值函數 ABS(X) ：返回表達式X的絕對值 mysql> select abs(-2); +---------+ | abs(-2) | +---------+ | 2 | +---------+ 1 row in set (0.00 sec) FLOOR(X) ：返回不大於X的最大整數值 mysql> select floor(-2.45); +--------------+ | floor(-2.45) | +--------------+ | -3 | +--------------+ 1 row in set (0.00 sec) MOD(N,M)：模操作，返回N被M除後的余數。 mysql> select mod(3,2); +----------+ | mod(3,2) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) RAND()/RAND(N) ：返回一個隨機浮點值數a，范圍在 0 到1 之間 (即, 其范圍為 0 ≤ a ≤ 1.0)。若已指定一個整數參數 N，則它被用作種子值，用來產生重復序列。 mysql> select rand(); +-------------------+ | rand() | +-------------------+ | 0.294932589209576 | +-------------------+ 1 row in set (0.00 sec) mysql> select rand(2); +-------------------+ | rand(2) | +-------------------+ | 0.655586646549019 | +-------------------+ 1 row in set (0.00 sec) ROUND(X)/ROUND(X,D) ：返回參數X, 其值接近於最近似的整數。在有兩個參數的情況下，返回 X ，其值保留到小數點後D位，而第D位的保留方式為四捨五入。若要接保留X值小數點左邊的D位，可將D設為負值。 mysql> select round(2.4 ); +-------------+ | round(2.4 ) | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec) mysql> select round(2.432,2 ); +-----------------+ | round(2.432,2 ) | +-----------------+ | 2.43 | +-----------------+ 1 row in set (0.00 sec) mysql> select round(12.432,-1 ); +-------------------+ | round(12.432,-1 ) | +-------------------+ | 10 | +-------------------+ 1 row in set (0.00 sec)
