日常工作中接触到的时间类型一般而言有4种,分别是:
- unix时间戳【从1970年1月1日(UTC/GMT的午夜)开始所经过的秒数,不考虑闰秒】:
–1595496374
(1单位1秒)
–1595496442071
(一单位1ms,1ms=1/1000 s) - 日期:
2020-07-23
- 时间:
17:30(.155)
- 日期+时间:
2020-07-23 17:31:33(.155)
下面简单讲一些它们相互转化的方法,本文主要针对mysql语法,其他的语法可能会略有不同。
(ps:关于保留毫秒的做法,目前传统的函数不能一步到位,得根据具体情况具体分析,我会在最后讲这个情况,不过也只是提一下自己的想法~)
1 日期(+时间)→unix时间戳
常用的函数是unix_timestamp,可以传入1-2个参数:
- 如果格式是为"yyyy-MM-dd HH:mm:ss"的日期,例如下面代码所示,则不需要传入额外的参数,函数会自动将其转化为对应的unix时间戳:
select unix_timestamp('2020-07-23 17:30:23')
- 如果格式区别于以上,例如
2020-07-23
,2020/7/23
或者少了一个秒的2020-07-23 17:30
,都需要另外传入一个时间pattern,否则会输出NULL:
select unix_timestamp('2020-07-23','yyyy-MM-dd')
select unix_timestamp('2020-07-23 17:30','yyyy-MM-dd HH:mm')
2 unix时间戳→日期、时间、日期+时间
利用的是from_unixtime()
函数,函数需要传入2个值,一个是unixtime时间戳,一个是时间pattern,也就是你要转化成的格式,这里 ‘yyyy-MM-dd’
SELECT from_unixtime(1595496623, 'yyyy-MM-dd')
如果需要进一步精确,可以一直精确到秒:
SELECT from_unixtime(1595496623, 'yyyy-MM-dd HH:mm:ss')
当然你也可以只输出时间,把前面的去掉即可:
SELECT from_unixtime(1595496623, 'HH:mm:ss')
需要注意的是:这里传入的必须是以秒为单位的unix时间戳而非毫秒为单位的时间戳
3 时间格式化
如果把时间看作是lego积木,那么第一部分我们其实是在拆解积木,第二部分我们是在搭积木,第三部分自然而然的就是,如何把一个拼好的积木A变成等价或者更粗略的积木B。
使用from_unixtime
嵌套unix_timestamp
函数我们便可以很快的进行时间格式化,例如我需要把20200723格式化成2020-07-23,可以用下面的代码:
from_unixtime(unix_timestamp('20200723','yyyyMMdd'),'yyyy-MM-dd')
不论怎么操作,基本逻辑都是先变成一个unix时间戳,然后把它变成需要的日期格式。
需要注意的是,上面一头一尾说的都是str格式的日期时间,不是date格式,如果需要转化成date格式,只需要再加一个to_date
函数即可:
SELECT to_date('2020-07-23 17:30:23')
函数里面只需要包含yyyy-MM-dd
即可,后面可有可无,但是前面不能是20200723或者2020/07/23一类的日期,这些会返回NULL。必须是yyyy-MM-dd形式
4 毫秒的处理
如果处理毫秒级的时间戳,具体需要先将毫秒时间戳转化成unix时间戳,具体方法很简单:可以在from_unixtime
内嵌套一个cast函数或者floor函数,然后将时间戳除以1000即可:
SELECT from_unixtime(cast(1595502014839/1000 as bigint))
SELECT from_unixtime(floor(1595502014839/1000))
而对于将毫秒级时间戳变成标准日期格式加上毫秒后缀的形式,这里提供一种思路,即利用concat
函数,先取出毫秒(839),然后和标准时间进行拼接,如果时间戳是字符串则可以用substring
来切割,更加方便:
SELECT concat(from_unixtime(floor(1595502014839/1000)),
'.',
cast((1595502014839-floor(1595502014839/1000)*1000) as CHAR(3)))