涉及的函数
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