Hive/SparkSQL:如何将 Unix 时间戳转换为时间戳(不是字符串)? [英] Hive/SparkSQL: How to convert a Unix timestamp into a timestamp (not string)?

问题描述

我认为这会很容易...

I thought this would be easy ...

在 Hive/SparkSQL 中，如何将 unix 时间戳 [注 1] 转换为 timestamp数据类型?

In Hive/SparkSQL, how do I convert a unix timestamp [Note 1] into a timestamp data type?

(注 1:即自 1970 年 1 月 1 日以来的秒数/毫秒数)

(Note 1: That is, number of seconds/milliseconds since Jan 1, 1970)

我认为 from_unixtime() 会这样做，但它返回一个字符串而不是时间戳.下面的实验说明问题

I thought from_unixtime() would do that, but it gives back a string instead of a timestamp. The following experiment illustrates the problem

第 0 步:准备

select 
  from_unixtime(1508673584) as fut;

结果:

-----------------------
| fut                 |
| ------------------- |
| 2017-10-22 11:59:44 |
-----------------------

第一步:用from_unixtime()

create table test
select 
  from_unixtime(1508673584) as fut;

第 2 步:检查 fut

describe test;

结果:

----------------------------------
| col_name | data_type | comment |
| -------- | --------- | ------- |
| fut      | string    | <null>  |
----------------------------------

我也试过这个

select 
  from_utc_timestamp(1508618794*1000, 'EDT');

根据手册(链接这里)，这应该有效.因为它声明:

According to the manual (link here), this should work. Because it states that:

将 UTC 中的时间戳*转换为给定的时区(从 Hive 0.8.0 开始).*时间戳是一种原始类型，包括时间戳/日期，tinyint/smallint/int/bigint、浮点数/双精度数和十进制数.分数值被视为秒.整数值被视为毫秒.. 例如 from_utc_timestamp(2592000.0,'PST'),from_utc_timestamp(2592000000,'PST') 和 from_utc_timestamp(timestamp'1970-01-30 16:00:00','PST') 都返回时间戳 1970-01-3008:00:00

Coverts a timestamp* in UTC to a given timezone (as of Hive 0.8.0). * timestamp is a primitive type, including timestamp/date, tinyint/smallint/int/bigint, float/double and decimal. Fractional values are considered as seconds. Integer values are considered as milliseconds.. E.g from_utc_timestamp(2592000.0,'PST'), from_utc_timestamp(2592000000,'PST') and from_utc_timestamp(timestamp '1970-01-30 16:00:00','PST') all return the timestamp 1970-01-30 08:00:00

但是，我得到了一个错误

However, I got an error of

Error: org.apache.spark.sql.AnalysisException: 
  cannot resolve 'from_utc_timestamp((1508618794 * 1000), 'EDT')' 
  due to data type mismatch: 
  argument 1 requires timestamp type, 
  however, '(1508618794 * 1000)' is of int type.; line 2 pos 2;
'Project [unresolvedalias(from_utc_timestamp((1508618794 * 1000), EDT), None)]
+- OneRowRelation$

SQLState:  null
ErrorCode: 0    

推荐答案

(我在这里自己提供答案.)

(I am providing an answer myself here.)

答案是使用cast().这适用于 date 和 timestamp

The answer is to use cast(). This works for both date and timestamp

select 
  from_unixtime(1508673584)                    as fut,
  cast(from_unixtime(1508673584) as date)      as futAsDate,
  cast(from_unixtime(1508673584) as timestamp) as futAsTimestamp;

结果:

------------------------------------------------------------
| fut                 | futAsDate  | futAsTimestamp        |
| ------------------- | ---------- | --------------------- |
| 2017-10-22 11:59:44 | 2017-10-22 | 2017-10-22 11:59:44.0 |
------------------------------------------------------------

数据类型验证

create table test2
select 
  from_unixtime(1508673584)                    as fut,
  cast(from_unixtime(1508673584) as date)      as futAsDate,
  cast(from_unixtime(1508673584) as timestamp) as futAsTimestamp;

然后

describe test2;  

结果:

----------------------------------------
| col_name       | data_type | comment |
| -------------- | --------- | ------- |
| fut            | string    | <null>  |
| futAsDate      | date      | <null>  |
| futAsTimestamp | timestamp | <null>  |
----------------------------------------

这篇关于Hive/SparkSQL:如何将 Unix 时间戳转换为时间戳(不是字符串)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！