迄今为止的字符串-Hive SQL [英] string to date - hive SQL

问题描述

我在配置单元环境中运行查询。

I am running queries in a hive environment.

我有一列带有时间戳，但在表中设置了一个字符串。我尝试了以下操作：全部返回Null

I have a column which has a timestamp but is set up a string in the tables. I tried the following : all of them return Null

SELECT 
,To_date(activitydate)

投射：

,cast(activitydate as timestamp)

这是在表中设置数据的方式：

This is the how the data is set up in the table:

赞赏我如何进行转换：

05/12/2017 00:00:00     



SELECT 
 cust_id
,to_date(activitydate)  activity_date
,type type_of_contact


FROM repl_task

WHERE to_date(activitydate) BETWEEN '2014-01-01' AND '2017-01-01' ;

如果运行此命令，我的内存不足：

I am running out of memory if I run this :

SELECT 
 cust_id
,activitydate
,SUBSTR(activitydate,4,2) AS DT
,SUBSTR(activitydate,0,2) AS MONTH 
,SUBSTR(activitydate,7,4) AS YEAR
,type 


FROM task 

WHERE activitydate >='01/01/2016 00:00:00'

推荐答案

unix_timestamp 函数将给定格式转换为unix时间戳，而 from_unixtime 函数将unix时间戳转换为给定格式：

unix_timestamp function converts given format to unix timestamp and from_unixtime function converts from unix timestamp to given format:

hive> select from_unixtime(unix_timestamp('01/01/2016 00:00:00','MM/dd/yyyy HH:m:ss'),'yyyy-MM-dd');
OK
2016-01-01
Time taken: 0.118 seconds, Fetched: 1 row(s)

这篇关于迄今为止的字符串-Hive SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！