Cast hive for date dd-MM-yyyy

Question

How can I pass a string in the format "dd-MM-yyyy" to the date type and in the format "dd-MM-yyyy" in Hive?

Something along the lines of:

CAST('12-03-2010' as date 'dd-mm-yyyy') 

Answer 1

to try:

 from_unixtime(unix_timestamp('12-03-2010' , 'dd-MM-yyyy')) 

Answer 2

If I understand correctly, you are trying to convert a string representing a given date to another type.

Note: (as @Samson Scharfrichter mentioned)

• default date representation - ISO8601
• date is stored in binary format (not as a string)

There are several ways to do this. And you are close to a solution. I would use CAST (which translates to DATE_TYPE):

 SELECT cast('2018-06-05' as date); 

Result: 2018-06-05 DATE_TYPE

or (depending on your template)

 select cast(to_date(from_unixtime(unix_timestamp('05-06-2018', 'dd-MM-yyyy'))) as date) 

Result: 2018-06-05 DATE_TYPE

And if you decide to convert ISO8601 to a date type:

 select cast(to_date(from_unixtime(unix_timestamp(regexp_replace('2018-06-05T08:02:59Z', 'T',' ')))) as date); 

Result: 2018-06-05 DATE_TYPE

Hive has its own functions, I wrote several examples to illustrate these date- and cast- functions:

Examples of date and time functions:

Convert string / timestamp / date to date

 SELECT cast(date_format('2018-06-05 15:25:42.23','yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE SELECT cast(date_format(current_date(),'yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE SELECT cast(date_format(current_timestamp(),'yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE 

Convert string / timestamp / date to BIGINT_TYPE

 SELECT to_unix_timestamp('2018/06/05 15:25:42.23','yyyy/MM/dd HH:mm:ss'); -- 1528205142 BIGINT_TYPE SELECT to_unix_timestamp(current_date(),'yyyy/MM/dd HH:mm:ss'); -- 1528205000 BIGINT_TYPE SELECT to_unix_timestamp(current_timestamp(),'yyyy/MM/dd HH:mm:ss'); -- 1528205142 BIGINT_TYPE 

Convert string / timestamp / date to string

 SELECT date_format('2018-06-05 15:25:42.23','yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE SELECT date_format(current_timestamp(),'yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE SELECT date_format(current_date(),'yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE 

Convert BIGINT unixtime to STRING

 SELECT to_date(from_unixtime(unixtime,'yyyy/MM/dd HH:mm:ss')); -- 2018-06-05 STRING_TYPE 

Convert string to BIGINT unixtime

 SELECT unix_timestamp('2018-06-05 15:25:42.23','yyyy-MM-dd') as TIMESTAMP; -- 1528149600 BIGINT_TYPE 

Convert string to TIMESTAMP

 SELECT cast(unix_timestamp('2018-06-05 15:25:42.23','yyyy-MM-dd') as TIMESTAMP); -- 1528149600 TIMESTAMP_TYPE 

Idempotent (String → String)

 SELECT from_unixtime(to_unix_timestamp('2018/06/05 15:25:42.23','yyyy/MM/dd HH:mm:ss')); -- 2018-06-05 15:25:42 STRING_TYPE 

Idempotent (Date → Date)

 SELECT cast(current_date() as date); -- 2018-06-26 DATE_TYPE 

Current Date / Timestamp

 SELECT current_date(); -- 2018-06-26 DATE_TYPE SELECT current_timestamp(); -- 2018-06-26 14:03:38.285 TIMESTAMP_TYPE 

Answer 3

AFAIK, you must reformat your string in ISO format in order to be able to cast it to a date:

 cast(concat(substr(STR_DMY,7,4), '-', substr(STR_DMY,1,2), '-', substr(STR_DMY,4,2) ) as date ) as DT 

To display the date as a string with a specific format, then vice versa, if you do not have bush 1. 2+ and you can use date_format()

=> By the way, did you check the documentation ?

Answer 4

Lets say you have a column 'birth_day' in your table that is in string format, you should use the following query to filter using Birth_day

 date_Format(birth_day, 'yyyy-MM-dd') 

You can use it in the request as follows

 select * from yourtable where date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';