時(shí)間獲取
獲取當(dāng)前時(shí)間
- now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2017-06-13 14:02:16 |
+---------------------+
- sysdate()
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2017-06-13 14:02:40 |
+---------------------+
1 row in set (0.00 sec)
同now()函數(shù)不同的是,now()在執(zhí)行開始時(shí)得到呐伞,sysdate()在函數(shù)執(zhí)行時(shí)動態(tài)得到敌卓。
mysql> select now(), sleep(3),now();
+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2017-06-13 14:10:40 | 0 | 2017-06-13 14:10:40 |
+---------------------+----------+---------------------+
mysql> select sysdate(),sleep(3),sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2017-06-13 14:11:02 | 0 | 2017-06-13 14:11:05 |
+---------------------+----------+---------------------+
- 當(dāng)前時(shí)間戳
current_timestamp,current_timestamp()
mysql> select current_timestamp,current_timestamp();
+---------------------+---------------------+
| current_timestamp | current_timestamp() |
+---------------------+---------------------+
| 2017-06-13 14:15:52 | 2017-06-13 14:15:52 |
+---------------------+---------------------+
獲取指定時(shí)間信息
- 星期幾:DAYOFWEEK(Date)
1=星期天,2=星期一,……7=星期六,ODBC標(biāo)準(zhǔn)
mysql> select DAYOFWEEK('2017-06-06');
+-------------------------+
| DAYOFWEEK('2017-06-06') |
+-------------------------+
| 3 |
+-------------------------+
- 星期幾:WEEKDAY(Date)
0=星期一,1=星期二,……6= 星期天
mysql> select WEEKDAY('2017-06-06');
+-----------------------+
| WEEKDAY('2017-06-06') |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
- 當(dāng)月第幾天:DAYOFMONTH(Date)
mysql> select DAYOFMONTH('2017-06-06');
+--------------------------+
| DAYOFMONTH('2017-06-06') |
+--------------------------+
| 6 |
+--------------------------+
- 當(dāng)年第幾天:DAYOFYEAR(Date)
mysql> select DAYOFYEAR('2017-06-06');
+-------------------------+
| DAYOFYEAR('2017-06-06') |
+-------------------------+
| 157 |
+-------------------------+
- 月份數(shù)值:MONTH(Date)
mysql> select MONTH('2017-06-06');
+---------------------+
| MONTH('2017-06-06') |
+---------------------+
| 6 |
+---------------------+
- 星期名:DAYNAME(Date)
mysql> select DAYNAME('2017-06-06');
+-----------------------+
| DAYNAME('2017-06-06') |
+-----------------------+
| Tuesday |
+-----------------------+
- 月份名:MONTHNAME(Date)
mysql> select MONTHNAME('2017-06-06');
+-------------------------+
| MONTHNAME('2017-06-06') |
+-------------------------+
| June |
+-------------------------+
- 第幾個(gè)季度:QUARTER(Date)
mysql> select QUARTER('2017-06-06');
+-----------------------+
| QUARTER('2017-06-06') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)
- 第幾周:WEEK(Date,first)
fisrt默認(rèn)值伶氢,表示周一是周的開始趟径,0是周日開始
mysql> select WEEK('2017-06-06',1);
+----------------------+
| WEEK('2017-06-06',1) |
+----------------------+
| 23 |
+----------------------+
- 年份:YEAR(Date)
mysql> select YEAR('2017-06-06');
+--------------------+
| YEAR('2017-06-06') |
+--------------------+
| 2017 |
+--------------------+
-小時(shí)數(shù):HOUR(Time)
mysql> select HOUR('13:13:13');
+------------------+
| HOUR('13:13:13') |
+------------------+
| 13 |
+------------------+
- 分鐘數(shù):MINUTE(Time)
mysql> select MINUTE('13:13:13');
+--------------------+
| MINUTE('13:13:13') |
+--------------------+
| 13 |
+--------------------+
-秒數(shù):SECOND(Time)
mysql> select SECOND('13:13:13');
+--------------------+
| SECOND('13:13:13') |
+--------------------+
| 13 |
+--------------------+
時(shí)間轉(zhuǎn)換
日期/時(shí)間轉(zhuǎn)換成字符串
MySQLDate/Time To Str(日期/時(shí)間轉(zhuǎn)換成字符串)函數(shù):date_format(date,format),time_format(time,format)
date參數(shù)是合法的日期,format規(guī)定日期/時(shí)間的輸出格式*
可以使用的格式有
格式 | 描述 |
---|---|
%a? | 縮寫星期名 |
%b? | 縮寫月名 |
%c? | 月癣防,數(shù)值 |
%D? | 帶有英文前綴的月中的天 |
%d? | 月的天蜗巧,數(shù)值(00-31) |
%e? | 月的天,數(shù)值(0-31) |
%f? | 微秒 |
%H? | 小時(shí) (00-23) |
%h? | 小時(shí) (01-12) |
%I? | 小時(shí) (01-12) |
%i? | 分鐘蕾盯,數(shù)值(00-59) |
%j? | 年的天 (001-366) |
%k? | 小時(shí) (0-23) |
%l? | 小時(shí) (1-12) |
%M? | 月名 |
%m? | 月幕屹,數(shù)值(00-12) |
%p? | AM 或 PM |
%r? | 時(shí)間,12-小時(shí)(hh:mm:ss AM 或 PM) |
%S? | 秒(00-59) |
%s? | 秒(00-59) |
%T? | 時(shí)間, 24-小時(shí) (hh:mm:ss) |
%U? | 周 (00-53) 星期日是一周的第一天 |
%u? | 周 (00-53) 星期一是一周的第一天 |
%V? | 周 (01-53) 星期日是一周的第一天级遭,與 %X 使用 |
%v? | 周 (01-53) 星期一是一周的第一天望拖,與 %x 使用 |
%W? | 星期名 |
%w? | 周的天 (0=星期日, 6=星期六) |
%X? | 年,其中的星期日是周的第一天挫鸽,4 位说敏,與 %V 使用 |
%x? | 年,其中的星期一是周的第一天丢郊,4 位盔沫,與 %v 使用 |
%Y? | 年,4 位 |
%y? | 年枫匾,2 位 |
字符串轉(zhuǎn)換為時(shí)間
str_to_date(str,format)函數(shù)作為date_format(date,format)的逆函數(shù)迅诬,能夠吧字符串轉(zhuǎn)換為時(shí)間
str參數(shù)為待轉(zhuǎn)義的字符串,format是轉(zhuǎn)換格式婿牍,格式含義同date_format一樣侈贷。
select str_to_date('06/06/2017', '%m/%d/%Y'); -- 2017-06-06
select str_to_date('06/06/17' , '%m/%d/%y'); -- 2017-06-06
select str_to_date('06.06.2017', '%m.%d.%Y'); -- 2017-06-06
select str_to_date('08:08:30', '%h:%i:%s'); -- 08:08:30
select str_to_date('06.06.2017 06:06:30', '%m.%d.%Y %h:%i:%s'); -- 2017-06-06 06:06:30
其他
-
天數(shù)/時(shí)間轉(zhuǎn)換函數(shù)
- to_days(date):返回日期到0000-00-00的天數(shù)
- from_days(days):根據(jù)天數(shù)返回日期
select to_days('2017-06-06'); -- 736851 select from_days(736851); -- '2017-06-06'
-
秒數(shù)/時(shí)間轉(zhuǎn)化函數(shù)
- time_to_sec(time):返回時(shí)間到零點(diǎn)的秒數(shù)
- sec_to_time(seconds):返回秒數(shù)的時(shí)間
time_to_sec('06:06:06'); -- 21966 select sec_to_time(21966); -- 06:06:06
-
時(shí)間,日期拼湊函數(shù)
- makedate(year,dayfromyear)
- maketime(hour,minutes,seconds)
select makedate(2017,160); -- 2017-06-06
select maketime(13,13,13); -- 13:13:13
時(shí)間計(jì)算
時(shí)間增減
- DATE_ADD(date,INTERVAL expr type)
date參數(shù)是合法的日期表達(dá)式,epxr參數(shù)是希望添加的時(shí)間間隔
type可惹温:
Type取值 |
---|
MICROSECOND |
SECOND |
MINUTE |
HOUR |
DAY |
WEEK |
MONTH |
QUARTER |
YEAR |
SECOND_MICROSECOND |
MINUTE_MICROSECOND |
MINUTE_SECOND |
HOUR_MICROSECOND |
HOUR_SECOND |
HOUR_MINUTE |
DAY_MICROSECOND |
DAY_SECOND |
DAY_MINUTE |
DAY_HOUR |
YEAR_MONTH |
- date_sub(date,INTERVAL expr type)
用法跟date_add相同撑蚌,作用是減去指定的時(shí)間間隔
時(shí)間差
- datediff(date1,date2):返回date1-date2的天數(shù)
datediff('2017/05/02', '2016/11/15'); -- 168
- timediff(time1,time2):返回time1-time2時(shí)間差(h:m:s)
timediff('11:12:13','10:03:14'); -- 01:08:59
時(shí)間戳轉(zhuǎn)換,增搏屑,減函數(shù)
- timestamp(date):把日期轉(zhuǎn)換成時(shí)間戳
imestamp('2017-06-06'); -- 2017-06-06 00:00:00
- timestamp(datetime1,datetime2):datetime1加上datetime2之后的時(shí)間
select timestamp('2017-06-06 08:08:08', '12:12:12'); -- 2017-06-06 20:20:20
- timestampadd(unit,intervavl,datetime_expr):按照格式計(jì)算時(shí)間差
timestampdiff(hour,'2017-06-06 12:00:00','2017-06-07 13:13:00'); -- 25
select timestampdiff(day,'2017-06-06 12:00:00','2017-06-07 13:13:00'); -- 1