配置单元时间戳格式 [英] Hive timestamp format

问题描述

我有一些格式如下的数据:

28/04/2017 00:00:00|20550|22/05/2017 00:00:00|

我将|设置为分隔符.对于本示例中第一行和第三行中的数据，我将timestamp作为数据类型放在HUE中，但是得到null s作为答案.

我环顾四周，在我看来Hive支持Unix格式的时间戳(来自此的地方)

但是，我收到一条无效日期"消息(到目前为止，我正在使用HUE进行此操作，但对于这种技术，我仍然是陌生的).如果我尝试将此数据设置为字符串，则可以看到值，但是当我尝试使用to_date()时，问题仍然存在.

我有什么要忽略的吗?

解决方案

唯一受支持的时间戳格式为yyyy-MM-dd HH:mm:ss，可选秒数.
其他所有内容都应阅读为字符串，并在以后转换.

演示

with t as (select '28/04/2017 00:00:00' as mydate)
select  from_unixtime(to_unix_timestamp (mydate,'dd/MM/yyyy HH:mm:ss'))
from    t

2017-04-28 00:00:00

I have some data with the following format:

28/04/2017 00:00:00|20550|22/05/2017 00:00:00|

I am setting | as a separator. For the data in the first and third row in this example, I put timestamp as a data type in HUE, but I get nulls as answer.

I have looked around and it seems to me like Hive supports Unix format timestamp (from places like this )

But, I get a "Invalid date" message (I'm doing it with HUE so far, I'm still new with this technologies). If I try to make this data String, I can see values, but when I try to use to_date() the problem persists.

Is there anything I'm ignoring?

解决方案

The only supported timestamp format is yyyy-MM-dd HH:mm:ss with optional fraction of seconds.
Anything else should be read as string and converted later.

Demo

with t as (select '28/04/2017 00:00:00' as mydate)
select  from_unixtime(to_unix_timestamp (mydate,'dd/MM/yyyy HH:mm:ss'))
from    t

2017-04-28 00:00:00

这篇关于配置单元时间戳格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！