時(shí)間獲取

獲取當(dāng)前時(shí)間

1. now()

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-06-13 14:02:16 |
+---------------------+

2. 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 |
+---------------------+----------+---------------------+

3. 當(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í)間的輸出格式*
可以使用的格式有

字符串轉(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可惹温：

• 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