时间戳转换成时间格式在线(时间转换为时间戳工具)

时间戳转换成时间格式在线(时间转换为时间戳工具)

select unix_timestamp(右边格式的日期时间,'yyyy-MM-dd HH:mm:ss'); -- 返回10位时间戳select unix_timestamp(右边格式的日期时间,'yyyyMMdd HH:mm:ss');-- 返回10位时间戳select from_unixtime(时间戳,'yyyyMMdd HH:mm:ss'); -- 返回右边格式的日期时间，时间戳需要是bigint类型！！！select from_unixtime(时间戳,'yyyy-MM-dd HH:mm:ss') -- 返回右边格式的日期时间，时间戳需要是bigint类型！！！select from_unixtime(unix_timestamp(右边格式的日期时间,'yyyyMMdd HH:mm:ss'),'yyyy-MM-dd HH:mm:ss') ;select from_unixtime(unix_timestamp(右边格式的日期时间,'yyyy-MM-dd HH:mm:ss'),'yyyyMMdd HH:mm:ss') ;1 yyyyMMdd HH:mm:ss >> yyyy-MM-dd HH:mm:sshive> select from_unixtime(unix_timestamp('20190430 23:59:02','yyyyMMdd HH:mm:ss'),'yyyy-MM-dd HH:mm:ss') as report_tm;OK2019-04-30 23:59:02Time taken: 0.056 seconds, Fetched: 1 row(s)hive> 2 yyyy-MM-dd HH:mm:ss >> yyyyMMdd HH:mm:sshive> select from_unixtime(unix_timestamp('2019-04-30 23:59:02','yyyy-MM-dd HH:mm:ss'),'yyyyMMdd HH:mm:ss') as report_tm;OK20190430 23:59:02Time taken: 0.06 seconds, Fetched: 1 row(s)hive> 3 yyyy-MM-dd HH:mm:ss >> 10位时间戳hive> select unix_timestamp('2019-04-30 23:59:02','yyyy-MM-dd HH:mm:ss');OK1556639942Time taken: 0.053 seconds, Fetched: 1 row(s)hive> 4 yyyyMMdd HH:mm:ss >> 10位时间戳hive> select from_unixtime(1556639942,'yyyyMMdd HH:mm:ss') as report_tm;OK20190430 23:59:02Time taken: 0.059 seconds, Fetched: 1 row(s)-- 注意：from_unixtime的时间戳入参不能是string类型！！！hive> select from_unixtime('1556639942','yyyyMMdd HH:mm:ss') as report_tm;FAILED: SemanticException [Error 10014]: Line 1:7 Wrong arguments ''yyyyMMdd HH:mm:ss'': No matching method for class......hive> 5 时间戳转换为yyyyMMdd HH:mm:ss 或 yyyy-MM-dd HH:mm:ss-- 需要使用cast(xxx as bigint)hive> select from_unixtime(cast(1556639942000/1000 as bigint),'yyyyMMdd HH:mm:ss') as report_tm;OK20190430 23:59:02Time taken: 0.064 seconds, Fetched: 1 row(s)-- 如果源值是string类型，还需要先使用cast(xxxx as bigint),然后再除以1000！！！！hive> select from_unixtime(cast(cast('1556639942000' as bigint)/1000 as bigint),'yyyyMMdd HH:mm:ss') as report_tm;OK20190430 23:59:02Time taken: 0.086 seconds, Fetched: 1 row(s)hive> -- 注意：from_unixtime的时间戳不能是13位的整数！！！hive> select from_unixtime(1556639942123,'yyyyMMdd HH:mm:ss') as report_tm;OK512971215 07:55:23 -- 结果错误Time taken: 0.101 seconds, Fetched: 1 row(s)hive> 6 错误示例：hive> select from_unixtime(1556639942123/1000,'yyyyMMdd HH:mm:ss') as report_tm;FAILED: SemanticException [Error 10014]hive> select from_unixtime(1556639942000/1000,'yyyyMMdd HH:mm:ss') as report_tm;FAILED: SemanticException [Error 10014]报错如下，可知：输入的入参是double类型的，但是第一个参数需要是int或bigint类型的：No matching method for class org.apache.hadoop.hive.ql.udf.UDFFromUnixTime with (double, string). Possible choices: _FUNC_(bigint) _FUNC_(bigint, string) _FUNC_(int) _FUNC_(int, string)7 为何6报错？？为何cast(xxx as bigint)正确？hive> select 1556639942000/1000 ;OK1.556639942E9 -- 是double类型的，所以6报错Time taken: 0.04 seconds, Fetched: 1 row(s)hive> select 1556639942123/1000 ;OK1.556639942123E9 -- 是double类型的，所以6报错Time taken: 0.05 seconds, Fetched: 1 row(s)hive> select cast(1556639942123/1000 as bigint);OK1556639942 -- 是bigint类型的 ，所以5正确！！Time taken: 0.05 seconds, Fetched: 1 row(s)hive>

历史文章：

Mysql查询年的第多少周

MySQL分组统计你会吗？sum,if会用吗？

MySQL,case when你真的会用吗？附避坑指南

时间戳转换成时间格式在线(时间转换为时间戳工具)