MySQL: 日期相关处理

MySQL 日期、时间转换函数：

1. date_format(date,format), time_format(time,format) 

能够把一个日期/时间转换成各种各样的字符串格式。

select date_format('2017-05-08 22:23:01','%Y%m%d%H%i%s');

20170508222301

2. str_to_date(str,format) 转换函数

可以把一些杂乱无章的字符串转换为日期格式。

select str_to_date('08/09/2008','%m/%d/%Y');--2008-08-09

select str_to_date('08/09/08','%m/%d/%y');--2008-08-09

select str_to_date('08.09.2008','%m.%d.%Y');--2008-08-09

select str_to_date('08:09:30','%h:%i:%s');--08:09:30

select str_to_date('08.09.2008 08:09:30','%m.%d.%Y %h:%i:%s');--2008-08-09 08:09:30

3. CAST(item AS TYPE), 把 item 转换成 TYPE类型, 表达形式不变

select CAST('2008-05-06' AS DATE);

2008-05-06

select CAST(2008-05-06 AS CHAR)

'2008-05-06'

4. 下面介绍MySql查询时间段的两种常见方法，其他数据库也可以类似实现。

方法一：传统方式，即指定开始时间和结束时间，用"between”或者"<"，">"来建立条件，比如查询2010年3月1日到2010年3月2日的数据条数，则可以使用

select count(*) from sometable where datetimecolumn>='2010-03-01 00:00:00' and

datetimecolumn<'2010-03-02 00:00:00'

但是，这种方法由于时间不是整数型数据，所以在比较的时候效率较低，所以如果数据量较大，可以将时间转换为整数型的UNIX时间戳，这就是方法二。

方法二：UNIX时间戳，每个时间对应了一个唯一的UNIX时间戳，该时间戳是从'1970-01-01 00:00:00' 为0开始计时，每秒增加1。MySql内置了传统时间和UNIX时间的互换函数，分别为

UNIX_TIMESTAMP(datetime)

FROM_UNIXTIME(unixtime)

比如运行SELECT UNIX_TIMESTAMP('2010-03-01 00:00:00')

返回1267372800

运行SELECT FROM_UNIXTIME(1267372800)

返回'2010-03-01 00:00:00'

于是，我们可以将时间字段里的数据替换为整型的UNIX时间，这样，比较时间就成为整数比较了，建立索引后能大大提高效率。在查询的时候，需要把起点时间和结尾时间分别转换为UNIX时间再进行比较，如

select count(*) from sometable where datetimecolumn>=UNIX_TIMESTAMP('2010-03-01 00:00:00') and datetimecolumn < UNIX_TIMESTAMP('2010-03-02 00:00:00'）;

5. date_add 时间加

select date_add(@dt, interval '01:15:30' hour_second);

2008-08-0913:28:03

select date_add(@dt, interval '1 01:15:30' day_second);

2008-08-1013:28:03

6.date_sub 时间减

select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

1997-12-3022:58:59

7.datediff

MySQLdatediff(date1,date2)：两个日期相减 date1-date2，返回天数。

selectdatediff('2008-08-08','2008-08-01');--7

selectdatediff('2008-08-01','2008-08-08');---7