如何在Hive中将字符串转换为毫秒级时间戳

Question

我有一个字符串 '20141014123456789'，它代表一个带毫秒的时间戳，我需要将其转换为Hive（0.13.0）中的时间戳，而不会丢失毫秒。

我尝试过使用unix_timestamp，但是它返回一个整数，导致我丢失了毫秒：

from_unixtime(unix_timestamp('20141014123456789', 'yyyyMMddHHmmssSSS'))      >> 2014-10-14 12:34:56    

字符串转换是有效的：

cast('2014-10-14 12:34:56.789' as timestamp)      >> 2014-10-14 12:34:56.789

但是我的字符串不是那种格式。

我想我需要将字符串从“20141014123456789”重新格式化为“2014-10-14 12:34:56.789”。我的挑战是如何在没有凌乱的子字符串连接的情况下完成这个操作。

- David Ford

Answer 1

我发现了一种避免使用字符串拼接混乱的方法，可以使用以下代码：

select cast(regexp_replace('20141014123456789', 
                           '(\\d{4})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{2})(\\d{3})',
                           '$1-$2-$3 $4:$5:$6.$7') as timestamp) 

- David Ford

---

那很有趣。你能否详细说明一下你正在做什么？ - ℕʘʘḆḽḘ

他使用正则表达式来修改字符串的格式。 regexp_replace函数提供了一种使用特殊模式语言（??）即正则表达式来替换字符串的可能性。 然后，他使用括号来创建组（前4个字符-年份，接下来2个字符-月份，...）。例如(\d{4})将前四个字符分组为一个年份字符串，可以在之后使用$符号和组的序数来引用它，例如年份... $1。 - Sale

Answer 2

一个简单的策略是使用date_format(arg1, arg2)，其中arg1是时间戳，可以是格式化字符串、日期或时间戳，arg2是字符串的格式（在arg1中）。请参考SimpleDateFormat Java文档以了解格式参数中可接受的内容。

因此，在这种情况下：

date_format('20141014123456789', 'yyyyMMddHHmmssSSS')

将会得到以下字符串：'2014-10-14 12:34:56.789'，然后可以将其转换为时间戳：

cast(date_format('20141014123456789', 'yyyyMMddHHmmssSSS') as timestamp)

上述语句将返回时间戳（如所需）。

- FMeh

Answer 3

我在这个表单中有一个日期字段，格式为2015-07-22T09:00:32.956443Z（以字符串形式存储）。我需要进行一些日期操作。 尽管以下命令有点混乱，但对我来说很有效:)

select cast(concat(concat(substr(date_created,1,10),' '),substr(date_created,12,15)) as timestamp) from tablename;

这看起来很复杂，但是如果你将它分解开来就很容易理解。提取日期和毫秒时间，并在它们之间添加一个空格，然后将整个内容连接起来并转换为时间戳。现在可以用它来进行日期或时间戳的操作。

- Vini

Answer 4

假设你的表中有一列名为“birth_date”，它的格式是字符串，那么你应该使用以下查询来使用“birth_date”进行筛选。

date_Format(birth_date, 'yyyy-MM-dd HH:mm:ssSSS')

您可以在查询中以以下方式使用它。

select * from yourtable
where 
date_Format(birth_date, 'yyyy-MM-dd HH:mm:ssSSS') = '2019-04-16 07:12:59999';

- Terminator17

---

如何利用这个来将精度提高到9毫秒？ - semicolon

Answer 5

我认为这个任务很难做到不凌乱。因为根据unix_timestamp()函数的文档，它返回的时间是以秒为单位的，因此会省略毫秒部分。

"将给定模式的时间字符串转换为Unix时间戳（以秒为单位），如果失败则返回0：unix_timestamp('2009-03-20'，'yyyy-MM-dd') = 1237532400."

在这里，最好的选择是编写一个UDF来处理这个问题，如果你想避免混乱的连接。然而，连接（虽然凌乱）会更好地完成工作。

- Amar

---

我更喜欢UDF方法，因为我可以利用Java的SimpleDateFormat。你为什么认为连接更好？UDF会更慢吗？ - David Ford