select unix_timestamp(右边格式的日期时间,&39;); -- 返回10位时间戳select unix_timestamp(右边格式的日期时间,&39;);-- 返回10位时间戳select from_unixtime(时间戳,&39;); -- 返回右边格式的日期时间,时间戳需要是bigint类型!!!select from_unixtime(时间戳,&39;)-- 返回右边格式的日期时间,时间戳需要是bigint类型!!!select from_unixtime(unix_timestamp(右边格式的日期时间,&39;),&39;) ;select from_unixtime(unix_timestamp(右边格式的日期时间,&39;),&39;) ;1 yyyyMMdd HH:mm:ss >> yyyy-MM-dd HH:mm:sshive> select from_unixtime(unix_timestamp(&39;,&39;),&39;) 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(&39;,&39;),&39;) 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(&39;,&39;);OK1556639942Time taken: 0.053 seconds,Fetched: 1 row(s)hive>4 yyyyMMdd HH:mm:ss >> 10位时间戳hive> select from_unixtime(1556639942,&39;) as report_tm;OK20190430 23:59:02Time taken: 0.059 seconds,Fetched: 1 row(s)-- 注意:from_unixtime的时间戳入参不能是string类型!!!hive> select from_unixtime(&39;,&39;) as report_tm;FAILED: SemanticException [Error 10014]: Line 1:7 Wrong arguments &39;yyyyMMdd HH:mm:ss&39;: 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),&39;) 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(&39; as bigint)/1000 as bigint),&39;) as report_tm;OK20190430 23:59:02Time taken: 0.086 seconds,Fetched: 1 row(s)hive>-- 注意:from_unixtime的时间戳不能是13位的整数!!!hive> select from_unixtime(1556639942123,&39;) as report_tm;OK512971215 07:55:23-- 结果错误Time taken: 0.101 seconds,Fetched: 1 row(s)hive>6 错误示例:hive> select from_unixtime(1556639942123/1000,&39;) as report_tm;FAILED: SemanticException [Error 10014]hive> select from_unixtime(1556639942000/1000,&39;) 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> select1556639942000/1000 ;OK1.556639942E9-- 是double类型的,13位毫秒时间戳,所以6报错Time taken: 0.04 seconds,Fetched: 1 row(s)hive> select1556639942123/1000 ;OK1.556639942123E9 -- 是double类型的,所以6报错Time taken: 0.05 seconds,Fetched: 1 row(s)hive> selectcast(1556639942123/1000 as bigint);OK1556639942-- 是bigint类型的 ,所以5正确!!Time taken: 0.05 seconds,Fetched: 1 row(s)hive>
历史文章:
时间戳的位数是根据算法计算的,没有固定的位数。可信时间戳计算出来的电子文件的数字指纹是
「欢迎关注,每天更新工作实用技能」
js获取的时间戳是13位的,精确到毫秒,而php获取的时间戳用strtotime是10位的,这样就导致一般我们在计算倒计时这样的问题时会出现差错,我们可以把js获取的时间戳除以1000,然后再计算 。