mysql时间相减得到毫秒值_Mysql 字符串、时间、时间戳相互转换，相减获取秒数...

涉及的函数

date_format(date, format) 函数

unix_timestamp() 函数

str_to_date(str, format) 函数

from_unixtime(unix_timestamp, format) 函数

1.字符串转化成时间

select

str_to_date('2019-09-12 00:16:50','%Y-%m-%d %H:%i:%s'),

str_to_date('2019-09-12 00:16:50','%Y-%m-%d %H')

2019-09-12 00:16:50 2019-09-12 00:00:00

2.字符串转化成时间戳

select

unix_timestamp('2019-09-12 00:00:50'),

unix_timestamp('2019-09-12 00:01:00');

1568217650 1568217660

3.时间转换成字符串

select

date_format(now(), '%Y-%m-%d'),

date_format(now(), '%Y-%m-%d %H:%i:%s')

2020-08-24 2020-08-24 14:51:26

4.时间转换成时间戳

select

unix_timestamp(now());

1598251906

5.时间戳转换成时间

select from_unixtime(1598250986);

2020-08-24 14:36:26

6.时间戳转成成字符串

select from_unixtime(1598250986,'%Y-%m-%d');

2020-08-24

要想获得正确的时间相减秒数，有以下三张方式：

1、time_to_sec(timediff(t2, t1))

2、timestampdiff(second, t1, t2)

3、unix_timestamp(t2) -unix_timestamp(t1)

列子：

select  time_to_sec(timediff(str_to_date('2019-09-12 00:17:50','%Y-%m-%d %H:%i:%s'), str_to_date('2019-09-12 00:16:50','%Y-%m-%d %H:%i:%s')))

60

select  timestampdiff(second, str_to_date('2019-09-12 00:17:50','%Y-%m-%d %H:%i:%s'), str_to_date('2019-09-12 00:16:50','%Y-%m-%d %H:%i:%s'))

-60

select unix_timestamp(str_to_date('2019-09-12 00:17:50','%Y-%m-%d %H:%i:%s')) -unix_timestamp(str_to_date('2019-09-12 00:16:50','%Y-%m-%d %H:%i:%s'))

60