mysql 日期类型字段相关运算

mysql 日期类型字段两个字段相减得到指定需要部分

一下都基于表aa的操作，表数据如下图：

1.datadiff，两个日期间隔天数；

SELECT DATEDIFF(`update_time`,create_time) FROM aa;

1.2  to_days，两个日期字段之间间隔天数（将日期类型数据先转换成天数，再相减）

 SELECT TO_DAYS(`update_time`),TO_DAYS(`create_time`) FROM aa;

2. timediff，两个日期之间时间间隔以时分秒表示：

SELECT TIMEDIFF(update_time,`create_time`) FROM aa;

3.sec_to_time，将数值当做秒数，转换成时间格式的值；

SELECT SEC_TO_TIME(120) FROM DUAL;  

==> '00:02:00';

4. time_to_sec，将时分秒格式的值转换成秒为单位的数值：

SELECT TIME_TO_SEC('00:02:01')  FROM DUAL; 

==> 121;

5.timestampdiff，取两个datetime类型字段之间时间间隔（可以取天、时、分、秒）

SELECT TIMESTAMPDIFF(MONTH,`update_time`,`create_time`) FROM aa;
SELECT TIMESTAMPDIFF(WEEK,`update_time`,`create_time`) FROM aa;

SELECT TIMESTAMPDIFF(DAY,`update_time`,`create_time`) FROM aa;
SELECT TIMESTAMPDIFF(HOUR,`update_time`,`create_time`) FROM aa;
SELECT TIMESTAMPDIFF(MINUTE,`update_time`,`create_time`) FROM aa;
SELECT TIMESTAMPDIFF(SECOND,`update_time`,`create_time`) FROM aa;

6.UNIX_TIMESTAMP，

select unix_timestamp()；（不带任何参数的时候）得到自1970-1-1 8:00:00开始到当前系统时间为止的秒数。

6.1 UNIX_TIMESTAMP，带参数的时候（类型：date型，time型，datetime型，timestamp型）

1970-1-1 8:00:00开始到update_time为止的秒数。

SELECT UNIX_TIMESTAMP(`update_time`) FROM aa;

7.UNIX_TIMESTAMP,取连个字段之间时间差，秒数：

SELECT UNIX_TIMESTAMP(`update_time`)-UNIX_TIMESTAMP(`create_time`) FROM aa;

8.from_unixtime，与UNIX_TIMESTAMP作用相反，将秒数，转换成时间格式值。

.两个日期字段，时间间隔，表示为‘n天n时n分n秒’：

（方式很多，使用原理都在上面，但是我今天遇到的情况就是要这么做，还必须是先得到秒数，因为要算一个平均使用时长……）

1.先得到两个时间字段间隔天数，直接用to_days，

时分秒，是去掉天数之后的秒数再来计算，因此去掉60*60*24秒，

秒数转换成时间格式，用sec_to_time，

SELECT CONCAT((TO_DAYS(`update_time`)-TO_DAYS(`create_time`)),' day ',
SEC_TO_TIME((UNIX_TIMESTAMP(`update_time`)-UNIX_TIMESTAMP(`create_time`))%86400)) FROM aa;

1-1. 或者，先得到天数部分，使用datediff，

然后时分秒部分是整个时间间隔的秒数，去掉那个天数的秒数，再来表示成时间格式：

SELECT CONCAT((DATEDIFF(`update_time`,`create_time`)) 
,' day ',
SEC_TO_TIME((UNIX_TIMESTAMP(`update_time`)-UNIX_TIMESTAMP(`create_time`))%86400)) AS used_time FROM aa;

2.不怕麻烦的方法==>先还是连个时间字段相减，得到总秒数，

天数的部分：总秒数/(60*60*24)得到结果的整数部分；

不足一整天的时间，就是剩余的秒数，

剩余秒数转换为时间格式，使用sec_to_time。

SELECT CONCAT((UNIX_TIMESTAMP(`update_time`)-UNIX_TIMESTAMP(`create_time`)) DIV 86400
,' day ',
SEC_TO_TIME((UNIX_TIMESTAMP(`update_time`)-UNIX_TIMESTAMP(`create_time`))%86400)) FROM aa;