时间获取

获取当前时间

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()函数不同的是，now()在执行开始时得到，sysdate()在函数执行时动态得到。

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. 当前时间戳
   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 |
+---------------------+---------------------+

获取指定时间信息

• 星期几：DAYOFWEEK(Date)
  1=星期天,2=星期一,……7=星期六,ODBC标准

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)

• 当月第几天：DAYOFMONTH(Date)

mysql> select DAYOFMONTH('2017-06-06');
+--------------------------+
| DAYOFMONTH('2017-06-06') |
+--------------------------+
|                        6 |
+--------------------------+

• 当年第几天：DAYOFYEAR(Date)

mysql> select DAYOFYEAR('2017-06-06');
+-------------------------+
| DAYOFYEAR('2017-06-06') |
+-------------------------+
|                     157 |
+-------------------------+

• 月份数值：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                    |
+-------------------------+

• 第几个季度：QUARTER(Date)

mysql> select QUARTER('2017-06-06');
+-----------------------+
| QUARTER('2017-06-06') |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

• 第几周：WEEK(Date,first)
  fisrt默认值，表示周一是周的开始，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 |
+--------------------+

-小时数：HOUR(Time)

mysql> select HOUR('13:13:13');
+------------------+
| HOUR('13:13:13') |
+------------------+
|               13 |
+------------------+

• 分钟数：MINUTE(Time)

mysql> select MINUTE('13:13:13');
+--------------------+
| MINUTE('13:13:13') |
+--------------------+
|                 13 |
+--------------------+

-秒数：SECOND(Time)

mysql> select SECOND('13:13:13');
+--------------------+
| SECOND('13:13:13') |
+--------------------+
|                 13 |
+--------------------+

时间转换

日期/时间转换成字符串

MySQLDate/Time To Str（日期/时间转换成字符串）函数：date_format(date,format),time_format(time,format)
date参数是合法的日期，format规定日期/时间的输出格式*
可以使用的格式有

字符串转换为时间

str_to_date(str,format)函数作为date_format(date,format)的逆函数，能够吧字符串转换为时间
str参数为待转义的字符串，format是转换格式，格式含义同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

其他

• 天数/时间转换函数

  • to_days(date)：返回日期到0000-00-00的天数
  • from_days(days)：根据天数返回日期

  select to_days('2017-06-06'); -- 736851
  select from_days(736851); -- '2017-06-06'
  

• 秒数/时间转化函数

  • time_to_sec(time)：返回时间到零点的秒数
  • sec_to_time(seconds)：返回秒数的时间

  time_to_sec('06:06:06'); -- 21966
  select sec_to_time(21966); -- 06:06:06 
  

• 时间，日期拼凑函数

  • makedate(year,dayfromyear)
  • maketime(hour,minutes,seconds)

select makedate(2017,160); -- 2017-06-06
select maketime(13,13,13); -- 13:13:13

时间计算

时间增减

• DATE_ADD(date,INTERVAL expr type)
  date参数是合法的日期表达式，epxr参数是希望添加的时间间隔
  type可取：

• date_sub(date,INTERVAL expr type)
  用法跟date_add相同，作用是减去指定的时间间隔

时间差

• datediff(date1,date2)：返回date1-date2的天数

datediff('2017/05/02', '2016/11/15');  -- 168

• timediff(time1,time2)：返回time1-time2时间差（h:m:s）

timediff('11:12:13','10:03:14');  -- 01:08:59

时间戳转换，增，减函数

• timestamp(date)：把日期转换成时间戳

imestamp('2017-06-06'); -- 2017-06-06 00:00:00

• timestamp(datetime1,datetime2)：datetime1加上datetime2之后的时间

select timestamp('2017-06-06 08:08:08', '12:12:12'); -- 2017-06-06 20:20:20

• timestampadd(unit,intervavl,datetime_expr)：按照格式计算时间差

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