使用日期和时间、设置客户端时区、从日期或时间值中分解各个部分值、合成日期或者时间值、查出给定某周的某天的日期

mysql cookbook0006

---

使用日期和时间

6.1 选择合适的日期或者时间变量类型

• mysql中提供了date保存日期（CCYY-MM-DD），time保存时间（hh:mm:ss）；
• 还有datetime、timestamp用来同时保存日期和时间（CCYY-MM-DD :hh:mm:ss/CCYYDDhhmmss）
• datetime和timestamp虽然可以有类似的处理方式，但是还会有差异：

1. datetime的范围1000-01-01 00：00：00 到 9999-12-31 23：59：59，而timestamp是1970-01-01 00：00：00 到 2037-12-31 23：59：59

2. timestamp具有自动初始化和自动更新的功能。

3.当客户端插入数据时，服务器会通过客户端的时区来转换日期再存入数据库，取数据就是反向操作了

6.2 修改mysql中的日期格式

在数据显示可以通过date_format()/time-format()将日期格式按照非ISO格式重写。

在录入日期时可以通过str_to_date('日期','格式')：str_to_date('May 13,2007','%M %d,%Y')

以上三个函数都接受格式化串作为参数：%M %d,%Y....

mysql> insert into timestamp_val values(str_to_date('May 13,2007','%M %d,%Y')

Query OK, 1 row affected (0.11 sec)
mysql> selectdate_format(ts,'%M %d,%Y')from timestamp_val;//转换特定的格式输出
+----------------------------+
| date_format(ts,'%M %d,%Y') |
+----------------------------+
| NULL |
| March 05,1987 |
| December 31,1999 |
| June 04,2000 |
| May 13,2007 |
+----------------------------+
mysql> selectdate_format(d,'%c/%e/%y %r')from date_val;
+------------------------------+
| date_format(d,'%c/%e/%y %r') |
+------------------------------+
| 2/28/64 12:00:00 AM |
| 1/15/90 12:00:00 AM |
| 3/5/87 12:00:00 AM |
| 12/31/99 12:00:00 AM |
| 6/4/00 12:00:00 AM |
+------------------------------+
5 rows in set (0.07 sec)
mysql> select d,date_format(d,'%c/%e/%y %r')from date_val;//该表是date表但是你查出来让其显示时间，他会在后面添加这天的中点
+------------+------------------------------+
| d | date_format(d,'%c/%e/%y %r') |
+------------+------------------------------+
| 1864-02-28 | 2/28/6412:00:00 AM |
| 1990-01-15 | 1/15/90 12:00:00 AM |
| 1987-03-05 | 3/5/87 12:00:00 AM |
| 1999-12-31 | 12/31/99 12:00:00 AM |
| 2000-06-04 | 6/4/00 12:00:00 AM |
+------------+------------------------------+
5 rows in set (0.00 sec)
mysql> select ts , time_format(ts,'%T') from timestamp_val;
+---------------------+----------------------+
| ts | time_format(ts,'%T') |
+---------------------+----------------------+
| 0000-00-00 00:00:00 | 00:00:00 |
| 1987-03-05 12:30:15 | 12:30:15 |
| 1999-12-31 00:00:00 | 00:00:00 |
| 2000-06-04 15:45:30 | 15:45:30 |
| 2007-05-13 00:00:00 | 00:00:00 |
+---------------------+----------------------+
5 rows in set (0.00 sec)
mysql> select ts ,time_format(ts,'%c/%e/%y %T')from timestamp_val;
+---------------------+-------------------------------+
| ts | time_format(ts,'%c/%e/%y %T') |
+---------------------+-------------------------------+
| 0000-00-00 00:00:00 |0/0/0000:00:00 |
| 1987-03-05 12:30:15 | 0/0/00 12:30:15 |
| 1999-12-31 00:00:00 | 0/0/00 00:00:00 |
| 2000-06-04 15:45:30 | 0/0/00 15:45:30 |
| 2007-05-13 00:00:00 | 0/0/00 00:00:00 |
+---------------------+-------------------------------+
5 rows in set (0.00 sec)

mysql> selectdate_format(t1,'%c/%e/%y %T')from time_val;
+-------------------------------+
| date_format(t1,'%c/%e/%y %T') |
+-------------------------------+
| 0/0/00 15:00:00 |
| 0/0/00 05:01:30 |
| 0/0/00 12:30:20 |
+-------------------------------+
3 rows in set (0.00 sec)

这条记录说明：time_format和date_format类似但是还是存在区别的，time_format只能使用时间相关的格式化字符定义。可以处理time,datetime或者timestamp类型

假如上面的格式化还不能满足您的要求，那么只有写函数来操作时间对象，自定义格式了

在自定义函数要注意格式：该要有的分号就不能少；语法有些不同

mysql>delimiter $$//改变终结符为$$

mysql> create function date_format_ampm(t timestamp)
-> returns varchar(30)
-> begin
-> declare ampm varchar(10);
-> if time(t) <12 * 60*60 then set ampm='am';
-> else set ampm='pm';
-> end if;
-> return concat(left(date_format(t,'%c/%e/%y %r'),16),ampm);
-> end$$
Query OK, 0 rows affected (0.03 sec)
mysql> select ts,date_format(ts,'%c/%e/%y %r'), date_format_ampm(ts) from timestamp_val$$
+---------------------+-------------------------------+----------------------+
| ts | date_format(ts,'%c/%e/%y %r') | date_format_ampm(ts) |
+---------------------+-------------------------------+----------------------+
| 0000-00-00 00:00:00| 0/0/00 12:00:00 AM | 0/0/00 12:00:00 am |
| 1987-03-05 12:30:15 | 3/5/87 12:30:15 PM | 3/5/87 12:30:15 pm |
| 1999-12-31 00:00:00 | 12/31/99 12:00:00 AM | 12/31/99 12:00:0am |
| 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM | 6/4/00 03:45:30 pm |
| 2007-05-13 00:00:00 | 5/13/07 12:00:00 AM | 5/13/07 12:00:00am |
+---------------------+-------------------------------+----------------------+
5 rows in set (0.00 sec)

6.3设置客户端时区（针对timestamp日期类型）

前面已经提到过，服务器怎样保存正确的客户端时区（time-zone可以改变操作系统的时区），

其实系统有个--default-time-zone来配置数据库服务器的时区（其实默认是使用操作系统所在的时区）

mysql> select @@global.time_zone,@@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1mysql> set @@session.time_zone='+04:00';

mysql> select * from timestamp_val;
+---------------------+
| ts |
+---------------------+
| 0000-00-00 00:00:00 |
| 1987-03-05 08:30:15 |
| 1999-12-30 20:00:00 |
| 2000-06-04 11:45:30 |
| 2007-05-12 20:00:00 |
+---------------------+
5 rows in set (0.00 sec)

mysql> set @@session.time_zone='system';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from timestamp_val;
+---------------------+
| ts |
+---------------------+
| 0000-00-00 00:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 00:00:00 |
| 2000-06-04 15:45:30 |
| 2007-05-13 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

mysql>

注：我们无论是通过客户端软件还是客户端控制台我们看到的timestamp使用的都是@session通过转换得到的日期时间

6.4 获取当前的日期和时间

mysql>select current_date,current_time,current_timestamp;

+--------------+--------------+---------------------+
| current_date | current_time | current_timestamp |
+--------------+--------------+---------------------+
| 2014-11-30 | 13:20:19 | 2014-11-30 13:20:19 |
+--------------+--------------+---------------------+
1 row in set (0.00 sec)
mysql> select curdate(),curtime(),now();//这些函数与上面的函数相对应
+------------+-----------+---------------------+
| curdate() | curtime() | now() |
+------------+-----------+---------------------+
| 2014-11-30 | 13:21:06 | 2014-11-30 13:21:06 |
+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysql> select utc_date(),utc_time(),utc_timestamp();
+------------+------------+---------------------+
| utc_date() | utc_time() | utc_timestamp() |
+------------+------------+---------------------+
| 2014-11-30 | 05:22:02 | 2014-11-30 05:22:02 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

6.5 使用timestamp进行跟踪行修改时间

timestamp数据类型自动完成初始化和自动更新功能

如何使用timestamp类型自动记录每一行的创建和更新时间：

• 表中的一个timestamp可以通过一下方面加以区别对待：

1. 当插入新的一行时，timestamp自动初始化当前的时间和日期。无需insert中特定时间，假如设置为null值也会自动设置当前时间在timestamp列中

2. 当某一列发生变化时（使用原值再次赋值这个不算变化），timestamp也会自动更新为最新时间。

• 一张表中有多列timestamp日期类型，那么只有一列才具有上面的特性，其他列被设置为0.
• 如果一个timestamp类型列有not null属性，那么把该列设置为null，可以将其更新为当前的日期时间。假如有多列这样的，可以通过这样的方式来更新当前日期和时间

在前面创建的一张timestamp_val表查看他的创建语句你会发现：虽然定义为not null但是还是可以存储空值，但是数据库会自动完成更新为当前的时间

mysql> show create table timestamp_val\G
*************************** 1. row ***************************
Table: timestamp_val
Create Table: CREATE TABLE `timestamp_val` (
`ts` timestampNOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

• 验证插入数据和更新数据timestamp列是否完成初始化和更新时间
• mysql> create table tsdemo1 (
  -> val int,
  -> ts timestamp);
  Query OK, 0 rows affected (0.20 sec)
  
  mysql> insert into tsdemo1 values(1,null);
  Query OK, 1 row affected (0.08 sec)
  
  mysql> insert into tsdemo1 values(2,null);
  Query OK, 1 row affected (0.06 sec)
  
  mysql> select * from tsdemo1;
  +------+---------------------+
  | val | ts |
  +------+---------------------+
  | 1 |2014-11-30 14:12:27|
  | 2 |2014-11-30 14:12:56|
  +------+---------------------+
  2 rows in set (0.00 sec)
  mysql> update tsdemo1 set val = 3 where val=2;//当更新数据时，timestamp也会更新
  Query OK, 1 row affected (0.06 sec)
  Rows matched: 1 Changed: 1 Warnings: 0
  
  mysql> select * from tsdemo1;
  +------+---------------------+
  | val | ts |
  +------+---------------------+
  | 1 | 2014-11-30 14:12:27 |
  | 3 |2014-11-30 14:14:04 |
  +------+---------------------+
  2 rows in set (0.00 sec)
  
  mysql>update tsdemo1 set val = 3 where val = 3;//当更新的数据是原数据，那么timestampiu不更新了
  Query OK, 0 rows affected (0.00 sec)
  Rows matched: 1 Changed: 0 Warnings: 0
  mysql> select * from tsdemo1;
  +------+---------------------+
  | val | ts |
  +------+---------------------+
  | 1 | 2014-11-30 14:12:27 |
  | 3 |2014-11-30 14:14:04 |
  +------+---------------------+
  2 rows in set (0.00 sec)
• 当你只想插入的时候初始化该值，而在更新的时候不会，你可以这样做
• mysql> alter table tsdemo1 modify ts timestampdefault current_timestamp;
  Query OK, 0 rows affected (0.13 sec)
  Records: 0 Duplicates: 0 Warnings: 0
  
  mysql> show create table tsdemo1;
  +---------+----------------------------------------------------------------
  ------+
  | Table | Create Table
  |
  +---------+----------------------------------------------------------------
  ------+
  | tsdemo1 | CREATE TABLE `tsdemo1` (
  `val` int(11) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  +---------+----------------------------------------------------------------
  ------+
  1 row in set (0.00 sec)
  
  mysql> update tsdemo1 set val=2 where val=3;//更新时并没有对timestamp中的时间进行更新
  Query OK, 1 row affected (0.07 sec)
  Rows matched: 1 Changed: 1 Warnings: 0
  
  mysql> select * from tsdemo1;
  +------+---------------------+
  | val | ts |
  +------+---------------------+
  | 1 | 2014-11-30 14:12:27 |
  | 2 |2014-11-30 14:14:04 |
  +------+---------------------+
  2 rows in set (0.00 sec)

6.6 从日期或时间值中分解各个部分值

• 使用特定的函数：year() ,month() ,minute() ,day() .....

mysql> select ts, year(ts),month(ts),monthname(ts),

dayofmonth(ts),dayname(ts),weekday(ts),dayofyear(ts),

hour(ts),minute(ts),second(ts) from tsdemo1\G
*************************** 1. row ***************************

ts: 2014-11-30 14:12:27

year(ts): 2014//取年份

month(ts): 11//取月份

monthname(ts): November//取月份但是通过名称来显示

dayofmonth(ts): 30//取一个月中第几天

dayname(ts): Sunday//取星期几但是是通过名称来显示

weekday(ts): 6//取星期几0-6

dayofyear(ts): 334//取一年中的第几天

hour(ts): 14

minute(ts): 12

second(ts): 27

extract( day from ts): 30//这是另外一种分解日期时间的函数，该使用了保留字extract:提取
extract(hour from ts): 14
extract(year from ts): 2014

• 使用格式化函数date_format(),time_format() 配置一个特定的格式化串来从一个日期时间中取值

mysql> select ts,date_format(ts,'%c-%e-%y'),date_format(ts,'%H:%i'),time_format(ts,'%T') from tsdemo1;

+---------------------+----------------------------+-------------------------+----------------------+

| ts | date_format(ts,'%c-%e-%y') | date_format(ts,'%H:%i') | time_format(ts,'%T') |

+---------------------+----------------------------+-------------------------+----------------------+

| 2014-11-30 14:12:27 | 11-30-14 | 14:12 | 14:12:27 |

| 2014-11-30 14:14:04 | 11-30-14 | 14:14 | 14:14:04 |

+---------------------+----------------------------+-------------------------+----------------------+

2 rows in set (0.00 sec)

• 把日期时间当作一个字符串通过left(),right(),mid()..

mysql> select ts,left(ts,4),right(ts,9),mid(ts,6,2), subString(ts,12),subString_index(ts,':',-1) from tsdemo1;

+---------------------+------------+-------------+-------------+------------------+----------------------------+

| ts | left(ts,4) | right(ts,9) | mid(ts,6,2) | subString(ts,12) | subString_index(ts,':',-1) |

+---------------------+------------+-------------+-------------+------------------+----------------------------+

| 2014-11-30 14:12:27 | 2014 | 14:12:27 | 11 | 14:12:27 | 27 |

| 2014-11-30 14:14:04 | 2014 | 14:14:04 | 11 | 14:14:04 | 04 |

+---------------------+------------+-------------+-------------+------------------+----------------------------+

2 rows in set (0.29 sec)

注意：在mysql中啊，很多地方下标的值都是从1开始，并不是从0开始的；有个日期是从0-6的特殊的地方

6.7 合成日期或者时间值

• maketime()、makedate()函数，组合小时，分钟，秒得到一个time类型值
• 使用date_format()或者time_format()来替换其中的一部分来得到值
• 通过分解函数分解日期、时间，在通过concat()函数组合起来

下面的演示是上面三种的情况的集成：

*************************** 1. row ***************************
ts: 2014-11-30 14:12:27
maketime(hour(ts),minute(ts),second(ts)): 14:12:27//通过三个参数确定时间time
makedate(year(ts),dayofyear(ts)): 2014-11-30//通过年份和概念的第几天来确定日期
date_format(ts,'%Y-%m-%e 00:00:00'): 2014-11-30 00:00:00//通过格式化函数来组合
concat(year(ts),'/',lpad(moNth(ts),'2',0),'/',lpad(dayofmonth(ts),'2',0)): 2014/11/30//通过联合函数concat和lpad（0填充）函数组合日期

6.8 在时间数据类型和基本单位进行转换

• 在时间类型值和秒进行转换：time_to_sec(),sec_to_time();

mysql> select ts,time_to_sec(ts),time_to_sec(date_format(ts,'%T')) ,sec_to_time(time_to_sec(time_format(ts,'%H:%i:%s'))) from tsdemo1\
*************************** 1. row ***************************
ts: 2014-11-30 14:12:27
time_to_sec(ts): 51147//这个效果和下面的一样，其实该函数在接收timestamp或datetime时会自动的过滤掉日期部分
time_to_sec(date_format(ts,'%T')): 51147
sec_to_time(time_to_sec(time_format(ts,'%H:%i:%s'))): 14:12:27

• 在日期类型和天进行转换：to_days()（会忽略时间值，这就提供了一种新方法从datetime和timestamp中分解出日期）,from_days();

mysql> select ts ,to_days(ts),from_days(to_days(date(ts))) from tsdemo1;

+---------------------+-------------+------------------------------+

| ts |to_days(ts)| from_days(to_days(date(ts))) |

+---------------------+-------------+------------------------------+

| 2014-11-30 14:12:27 | 735932 | 2014-11-30 |

| 2014-11-30 14:14:04 | 735932 | 2014-11-30 |

+---------------------+-------------+------------------------------+

2 rows in set (0.00 sec)

• 在timestamp或者datetime类型值和秒之间进行转换，可以使用unix_timestamp()和from_unixtime()

mysql> select dt ,unix_timestamp(dt),from_unixtime(unix_timestamp(dt)) from datetime_val\G

*************************** 1. row ***************************

dt: 1970-01-01 00:00:00

unix_timestamp(dt): 0

from_unixtime(unix_timestamp(dt)): 1970-01-01 08:00:00

6.9 计算两个日期和时间之间的间隔

• 使用时间差函数

datediff()函数求两个日期相隔天数，也可以作用于datetime，timestamp等类型，但是他会忽略时间值

timediff()函数求时间间隔，他也支持date-and-time类型的处理，但是处理时两个参数类型要一致

mysql> select timediff(t1,t2) from time_val;

+-----------------+

| timediff(t1,t2) |

+-----------------+

| 00:00:00 |

| 02:31:10 |

| -05:00:25 |

timestampdiff()函数求时间日期间隔，在使用时可以传递时间间隔所使用的参数：

mysql> set @ts1=now();

Query OK, 0 rows affected (0.00 sec)

mysql> set @ts2='2014-11-30 20:00:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select timestampdiff(minute,@ts1,@ts2),

->timestampdiff(hour,@ts1,@ts2),

-> timestampdiff(second,@ts1,@ts2),

-> timestampdiff(day,@ts1,@ts2);

+---------------------------------+-------------------------------+---------------------------------+------------------------------+

| timestampdiff(minute,@ts1,@ts2) | timestampdiff(hour,@ts1,@ts2) | timestampdiff(second,@ts1,@ts2) | timestampdiff(day,@ts1,@ts2) |

+---------------------------------+-------------------------------+---------------------------------+------------------------------+

| 15 | 0 | 930 | 0 |

+---------------------------------+-------------------------------+---------------------------------+------------------------------+

1 row in set (0.00 sec)

注意timestampdiff的几大特性：

• 如果参数值第一个大于第二个，那么返回值是负数
• 虽然该函数是timestampdiff但是并不只限制于针对timestamp其他的都可以
• 该函数只在5.0以上的mysql数据库才支持

• 使用基本时间单位来计算时间间隔：意思就是转化为相同的基本单位进行运算（day，second）

6.10 增加日期或时间值

• 使用时间假发函数或者操作符进行时间值求和运算

使用addtime()函数,把一个时间值或者一个date-and-time类型值和一个时间值相加：

mysql> set @t1='12:00:00',@t2='13:00:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select addtime(@t1,@t2);

+------------------+

| addtime(@t1,@t2) |

+------------------+

| 25:00:00 |

+------------------+

1 row in set (0.00 sec)

mysql> set @dt1='2014-11-30 00:00:22';

Query OK, 0 rows affected (0.00 sec)

mysql> select addtime(@dt1,@t2);

+---------------------+

| addtime(@dt1,@t2) |

+---------------------+

| 2014-11-30 13:00:22 |

+---------------------+

1 row in set (0.00 sec)

使用timestamp()函数把一个时间或者一个date-and-time和一个时间相加

mysql> select timestamp(@t1,@t2),timestamp(@dt1,@t2);

+---------------------+---------------------+

| timestamp(@t1,@t2) | timestamp(@dt1,@t2) |

+---------------------+---------------------+

| 2011-11-30 13:00:00 | 2014-11-30 13:00:22 |

+---------------------+---------------------+

使用date_add(d, interval val unit)/date_sub()用来对一个日期值和一个时间值进行加减法运算//这里的d必须是date或者date-and-time

mysql> select current_date,date_add(curdate(), interval 3 day);//interval:间隔的意思

+--------------+-------------------------------------+
| current_date | date_add(curdate(), interval 3 day) |
+--------------+-------------------------------------+
| 2014-11-30 | 2014-12-03 |
+--------------+-------------------------------------+
1 row in set (0.00 sec)
mysql> select curtime(),date_add(curtime(), interval 10 minute);
+-----------+-----------------------------------------+
| curtime() | date_add(curtime(), interval 10 minute) |
+-----------+-----------------------------------------+
| 20:22:59 | NULL |
+-----------+-----------------------------------------+
1 row in set,1 warning (0.00 sec)//应该是不能用

mysql> select curtime(),date_add(now(), interval 10 second);
+-----------+-------------------------------------+
| curtime() | date_add(now(), interval 10 second) |
+-----------+-------------------------------------+
| 20:27:41 |2014-11-30 20:27:51 |
+-----------+-------------------------------------+
1 row in set (0.00 sec)

有些时间间隔包括了时期和时间、小时和分钟

mysql> select now(),date_add(now(),interval '3 04'day_hour);

+---------------------+------------------------------------------+

| now() | date_add(now(),interval '3 04' day_hour) |

+---------------------+------------------------------------------+

| 2014-11-30 20:42:10 | 2014-12-04 00:42:10 |

+---------------------+------------------------------------------+

1 row in set (0.00 sec)

mysql> select curtime(),date_add(now(), interval'10:30'minute_second);

+-----------+-------------------------------------------------+
| curtime() | date_add(now(), interval '10:30' minute_second) |
+-----------+-------------------------------------------------+
| 20:37:11 | 2014-11-30 20:47:41 |

也可以使用操作符来进行：

mysql> select now(),now() +interval '3 04' day_hour;

+---------------------+---------------------------------+

| now() | now() +interval '3 04' day_hour |

+---------------------+---------------------------------+

| 2014-11-30 20:44:38 | 2014-12-04 00:44:38 |

+---------------------+---------------------------------+

1 row in set (0.00 sec)

mysql>

还有时间间隔的值的符号是可以区反值的：

mysql> select now(),now() -interval '3 04' day_hour;

+---------------------+---------------------------------+
| now() | now() -interval '3 04' day_hour |
+---------------------+---------------------------------+
| 2014-11-30 20:45:15 | 2014-11-27 16:45:15 |
+---------------------+---------------------------------+
1 row in set (0.00 sec)
mysql> select now(),now() +interval '3 -04' day_hour;
+---------------------+----------------------------------+
| now() | now() +interval '3 -04' day_hour |
+---------------------+----------------------------------+
| 2014-11-30 20:45:24 | 2014-12-04 00:45:24 |
+---------------------+----------------------------------+

注意：在mysql5.0上还提供了一个函数timestampadd(unit,interval,d)=date_add(d,interval unit);

• 使用基本时间单位进行加减法运算：转化为相同单位再运算

mysql> select t1,t2,time_to_sec(t1)+time_to_sec(t2),t1+t2,sec_to_time(time_to_sec(t1)+time_to_sec(t2)) from time_val;

+----------+----------+---------------------------------+--------+----------------------------------------------+

| t1 | t2 | time_to_sec(t1)+time_to_sec(t2) | t1+t2 | sec_to_time(time_to_sec(t1)+time_to_sec(t2)) |

+----------+----------+---------------------------------+--------+----------------------------------------------+

| 15:00:00 | 15:00:00 | 108000 |300000| 30:00:00 |

| 05:01:30 | 02:30:20 | 27110 | 73150 | 07:31:50 |

| 12:30:20 | 17:30:45 | 108065 | 296065 | 30:01:05 |

+----------+----------+---------------------------------+--------+----------------------------------------------+

注意：1. 从上面的结果可以知道即使是相同的time也要先转换成相同的单位进行运算。

2. time表示的是经过的时间，并不是一天中的某个时间，因此相加以后并不会mod到日期上去。

提示：time类型值的取值范围是-838.59.59~838.59.59 但是提么类型数学运算的结果可能超出了这个取值范围，也就无法保存正确的信息到数据库中。

以基本单位对日期或者date-and-time做加法。

mysql> select ts, from_days(to_days(ts)+10),from_unixtime(unix_timestamp(ts) + 10) from timestamp_val;

+---------------------+---------------------------+----------------------------------------+

| ts | from_days(to_days(ts)+10) | from_unixtime(unix_timestamp(ts) + 10) |

+---------------------+---------------------------+----------------------------------------+

| 0000-00-00 00:00:00 | NULL | 1970-01-01 08:00:10 |

| 1987-03-05 12:30:15 | 1987-03-15 | 1987-03-05 12:30:25 |

| 1999-12-31 00:00:00 | 2000-01-10 | 1999-12-31 00:00:10 |

| 2000-06-04 15:45:30 | 2000-06-14 | 2000-06-04 15:45:40 |

| 2007-05-13 00:00:00 | 2007-05-23 | 2007-05-13 00:00:10 |

| 2014-11-30 14:09:20 | 2014-12-10 | 2014-11-30 14:09:30 |

+---------------------+---------------------------+----------------------------------------+

6.11 计算年龄

timestampdiff()

mysql> select name , '1864-02-28' as birth, timestampdiff(year,str_to_date('1864-02-28','%Y-%m-%d'),d)

from date_val where name not like 'zhangsan';

+---------+------------+------------------------------------------------------------+
| name | birth | timestampdiff(year,str_to_date('1864-02-28','%Y-%m-%d'),d) |
+---------+------------+------------------------------------------------------------+
| lisi | 1864-02-28 | 125 |
| wangwu | 1864-02-28 | 123 |
| mazi | 1864-02-28 | 135 |
| xiaosan | 1864-02-28 | 136 |
+---------+------------+------------------------------------------------------------+
4 rows in set (0.00 sec)

通常给定出生日期是这样算的：

if(d occurs earlier in the year than birth)

age = YEAR(d)- YEAR(birth)-1;

else

age = YEAR(d) -YEAR(birth);

不能通过dayofyear来计算，因为在不同的日期会存在相同一年中的第几天

mysql> select dayofyear('1995-03-01'),dayofyear('1996-02-29');
+-------------------------+-------------------------+
| dayofyear('1995-03-01') | dayofyear('1996-02-29') |
+-------------------------+-------------------------+
| 60 | 60 |
+-------------------------+-------------------------+

iso标准的日期字符串的比较太及时了，它可以比较日和月

mysql> select if('02-09' > '03-01','02-09','03-01');

+---------------------------------------+

| if('02-09' > '03-01','02-09','03-01') |

+---------------------------------------+

| 03-01 |

+---------------------------------------+

1 row in set (0.00 sec)

mysql> select timestampdiff(year,d,curdate()) as yearofbirth,

timestampdiff(month,d,curdate()) as monthofbirth from date_val;
+-------------+--------------+
| yearofbirth | monthofbirth |
+-------------+--------------+
| 150 | 1809 |
| 24 | 298 |
| 27 | 332 |
| 14 | 179 |
| 14 | 173 |
+-------------+--------------+
5 rows in set (0.00 sec)

mysql> select year(curdate())-year(d)-if(left(curdate(),6)>left(d,6),0,1) as yearofbirth,

(year(curdate())-year(d)) * 12 -if(dayofmonth(curdate())>dayofmonth(d),0,1) as monthofbirth from date_val;
+-------------+--------------+
| yearofbirth | monthofbirth |
+-------------+--------------+
| 150 | 1799 |
| 24 | 287 |
| 27 | 323 |
| 15 | 179 |
| 14 | 167 |
+-------------+--------------+
5 rows in set (0.00 sec)
mysql>

//这两种计算还是有差异的。

6.12 将一个日期和一个时间值切换到另一个时区中

CONVERT_TZ(datetime,'原时区','新时区');

6.13 找到一个月的第一天，最后一天，或者天数

mysql> select d,date_sub(d ,interval dayofmonth(d)-1 day) from date_val;//某个月第一天
+------------+-------------------------------------------+
| d | date_sub(d ,interval dayofmonth(d)-1 day) |
+------------+-------------------------------------------+
| 1864-02-28 | 1864-02-01 |
| 1990-01-15 | 1990-01-01 |
| 1987-03-05 | 1987-03-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
+------------+-------------------------------------------+

指定日期的那个月的前后月份

mysql> select d,date_add(date_sub(d,interval dayofmonth(d)-1 day), interval -1 month) as pervious,
-> date_add(date_sub(d,interval dayofmonth(d)-1 day) ,interval 1 month) as after from date_val;
+------------+------------+------------+
| d | pervious | after |
+------------+------------+------------+
|1864-02-28 | 1864-01-01 | 1864-03-01 |
| 1990-01-15 | 1989-12-01 | 1990-02-01 |
| 1987-03-05 | 1987-02-01 | 1987-04-01 |
| 1999-12-31 | 1999-11-01 | 2000-01-01 |

指定日期的前一个月后一个月的最后一天

mysql> select d , last_day(date_add(d,interval 1 month)) as 'after month',

last_day(date_sub(d,interval 1 month))as 'pervous month'from date_val;//重命名一定记得加引号
+------------+-------------+---------------+
| d | after month | pervous month |
+------------+-------------+---------------+
| 1864-02-28 | 1864-03-31 | 1864-01-31 |
| 1990-01-15 | 1990-02-28 | 1989-12-31 |
| 1987-03-05 | 1987-04-30 | 1987-02-28 |
| 1999-12-31 | 2000-01-31 | 1999-11-30 |
| 2000-06-04 | 2000-07-31 | 2000-05-31 |
+------------+-------------+---------------+

6.14 通过子串替换来计算日期

mysql> select d, date_format(d,'%Y-%m-01'),concat(year(d),'-',lpad(month(d),2,0),'-01') from date_val;
+------------+---------------------------+----------------------------------------------+
| d | date_format(d,'%Y-%m-01') | concat(year(d),'-',lpad(month(d),2,0),'-01') |
+------------+---------------------------+----------------------------------------------+
| 1864-02-28 | 1864-02-01 | 1864-02-01 |
| 1990-01-15 | 1990-01-01 | 1990-01-01 |
| 1987-03-05 | 1987-03-01 | 1987-03-01 |
| 1999-12-31 | 1999-12-01 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 | 2000-06-01 |
+------------+---------------------------+----------------------------------------------+
5 rows in set (0.00 sec)

6.15 计算某个日期为星期几

mysql> select d ,dayname(date_add(date_sub(d, interval dayofmonth(d) -1 day) ,interval -1 month)) from date_val;

+------------+----------------------------------------------------------------------------------+
| d | dayname(date_add(date_sub(d, interval dayofmonth(d) -1 day) ,interval -1 month)) |
+------------+----------------------------------------------------------------------------------+
| 1864-02-28 | Friday |
| 1990-01-15 | Friday |

| 1987-03-05 | Sunday |

6.16 查出给定某周的某天的日期

注：dayofweek:1-7表示星期天~下个星期六（把一个星期的第一天当作星期天）

weekday:0-6标识星期一~星期天

date_add(date_sub(d,interval dayofweek(d) day), intervalnday)

date_add(d,interval (n - dayofweek(d)))

计算该日期的前两个星期的星期三的日期：

mysql> select d, dayname(d),date_add(date_sub(d, interval 14 day),interval 4 - dayofweek(d) day) from date_val

+------------+------------+----------------------------------------------------------------------+

| d | dayname(d) | date_add(date_sub(d, interval 14 day),interval 4 - dayofweek(d) day) |

+------------+------------+----------------------------------------------------------------------+

| 1864-02-28 | Sunday | 1864-02-17 |

| 1990-01-15 | Monday | 1990-01-03 |

| 1987-03-05 | Thursday | 1987-02-18 |

| 1999-12-31 | Friday | 1999-12-15 |

| 2000-06-04 | Sunday | 2000-05-24 |

+------------+------------+----------------------------------------------------------------------+

6.17 执行闰年运算

*************************** 1. row ***************************

d: 1864-02-28
year(d) % 4 = 0: 1
year(d)%4=0 andyear(d)%400 =0 and year(d)%100 !=0: 0//这两个不可能同时成立
year(d)%4 =0 and(year(d)%400 =0 or year(d)%100 != 0): 1//两者成立一个，同时成立是不可能的
*************************** 2. row ***************************
d: 1900-01-15
year(d) % 4 = 0: 1
year(d)%4=0 and year(d)%400 =0 and year(d)%100 !=0: 0
year(d)%4 =0 and (year(d)%400 =0 or year(d)%100 != 0): 0
*************************** 3. row ***************************
d: 1987-03-05
year(d) % 4 = 0: 0
year(d)%4=0 and year(d)%400 =0 and year(d)%100 !=0: 0
year(d)%4 =0 and (year(d)%400 =0 or year(d)%100 != 0): 0
*************************** 4. row ***************************
d: 1999-12-31
year(d) % 4 = 0: 0
year(d)%4=0 and year(d)%400 =0 and year(d)%100 !=0: 0
year(d)%4 =0 and (year(d)%400 =0 or year(d)%100 != 0): 0
*************************** 5. row ***************************
d: 2000-06-04
year(d) % 4 = 0: 1
year(d)%4=0 and year(d)%400 =0 and year(d)%100 !=0: 0
year(d)%4 =0 and (year(d)%400 =0 or year(d)%100 != 0): 1
5 rows in set (0.00 sec)

6.18 接近但不是iso格式的日期格式

6.19 将日期或时间当成数值

可以通过对其+0操作或者在一个数值上下文中使用时间值。

mysql> select ts,ts + 0,floor(ts + 0) from timestamp_val;

+---------------------+----------------+----------------+

| ts | ts + 0 | floor(ts + 0) |

+---------------------+----------------+----------------+

| 0000-00-00 00:00:00 | 0 | 0 |

| 1987-03-05 12:30:15 | 19870305123015 | 19870305123015 |

| 1999-12-31 00:00:00 | 19991231000000 | 19991231000000 |

| 2000-06-04 15:45:30 | 20000604154530 | 20000604154530 |

| 2007-05-13 00:00:00 | 20070513000000 | 20070513000000 |

| 2014-11-30 14:09:20 | 20141130140920 | 20141130140920 |

+---------------------+----------------+----------------+

6 rows in set (0.00 sec)

注意：1. 假如通过一个字符串格式的日期时间+0处理，你将只能得到是时间或者日期值的第一个组成元素。

mysql> select '1990-01-01' + 0,'20:00:00' + 0 ;

+------------------+----------------+

| '1990-01-01' + 0 | '20:00:00' + 0 |

+------------------+----------------+

| 1990 | 20 |

+------------------+----------------+

1 row in set, 2 warnings (0.00 sec)

2. 有些函数+0操作返回的值也是组合的第一部分：date_format,time_format,left,right,mid....

6.20 强制将mysql将字符串当作时间值：需要在时间相关上下文中转变字符串

mysql> select '1990-01-01' + 0,'20:00:00' + 0 ;

+------------------+----------------+

| '1990-01-01' + 0 | '20:00:00' + 0 |

+------------------+----------------+

| 1990 | 20 |

+------------------+----------------+

1 row in set, 2 warnings (0.00 sec)

mysql> select '12:12:12'+0, sec_to_time(time_to_sec('12:12:12'))+0;

+--------------+----------------------------------------+

| '12:12:12'+0 | sec_to_time(time_to_sec('12:12:12'))+0 |

+--------------+----------------------------------------+

| 12 | 121212.000000 |

+--------------+----------------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> select '2014-01-01'+0,from_days(to_days('2014-01-01'))+0;

+----------------+------------------------------------+

| '2014-01-01'+0 | from_days(to_days('2014-01-01'))+0 |

+----------------+------------------------------------+

| 2014 | 20140101 |

+----------------+------------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> select '2014-01-01 12:12:12'+0,date_add('2014-01-01 12:12:12' ,interval 0 day)+0;

+-------------------------+----------------------------------------------------+

| '2014-01-01 12:12:12'+0 | date_add('2014-01-01 12:12:12' ,interval 0 day) +0 |

+-------------------------+----------------------------------------------------+

| 2014 | 20140101121212 |

+-------------------------+----------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql>show warnings;

+---------+------+---------------------------------------------------------+

| Level | Code | Message |

+---------+------+---------------------------------------------------------+

| Warning | 1292 | Truncated incorrect DOUBLE value: '2014-01-01 12:12:12' |

+---------+------+---------------------------------------------------------+

1 row in set (0.00 sec)

6.21 基于时间特性来查询行

• 将一个日期和另一个日期进行比较

select d from date_val where d between '2011-01-01' AND '2013-01-01';

查找历史50年前发生的事情

select tiltle,content,time from history where time< date_sub(curdate(),interval 50 year);

历史上的今天发生的事件

select title,content,time from history where date_format(curdate(),'%m-%e') = date_format(time,'%m-%e');

查找过去七年内的日期值

select d from date_val where d>=date_sub(curdate(),interval 7 year);

注意：where 子句中的表达式将日期列分离在比较操作符的一侧。通常这是一个很好的做法：假如该列做了索引那将大大的提高执行效率；

像这样：select d from date_val where curdate() < date_add(d,interval 7 year);d被应用于表达式中，

这就意味着每行(之所以每一行都被查询，因为这种情况索引不被使用的，那么一般索引有BTree索引，而BTree他会把关键字当作树的节点来建立索引，

这样在查找的时候就不需要对每一行中的那一列进行查找了查的是部分树节点了；但是假如索引建立了但是使用不了，那还是要每行遍历的)也就是在表达式中像这种情况

都必须要被查询以使表达式能够被求值并测试，这使得这一列上的任何索引都讲无效。

技巧：假如想查找过期了的文章：

在创建一行的时候，使用data_add函数计算到期时间，将到期时间显示的保存到每一行中，对于N天到期的行：

insert into article(expire_date) value(date_add(now,interval n day));

查询的时候：select * from article where expire_date<curdate();