【Hive】Date from plastic surgery to Date type

When creating a table, we often set the date field to INT type, such as20180601Such a numeric value represents the date, so there is no problem when doing date comparison operations, but to perform certain date calculations, you have to convert it to the date type before you can calculate it. How to convert it?

Data preparation

Next, create a table in Hive, which contains an INT-type date field and insert two rows of data.

create table tb (dt INT);
insert into tb values (20180701);
insert into tb values (20180715);

• 1
• 2
• 3

AI writing code

Convert type

The first method here is toINTConvert date value of type toSTRINGType, built-in with Hiveunix_timestampfunctionConvert to timestamp type, and finally use the timestampfrom_unixtimeConvert toyyyy-MM-dddate type.

The second type is more direct,INTConvert date value of type toSTRINGType, then intercept the string, use-Spliced ​​together.

select dt,
from_unixtime(unix_timestamp(cast(dt as string),'yyyyMMdd'),'yyyy-MM-dd') as a,
concat(substr(cast(dt as string),1,4), '-',substr(cast(dt as string),5,2), '-',substr(cast(dt as string),7,2)) as b
from tb;

• 1
• 2
• 3
• 4

AI writing code

Running results

Of course, it's a bit difficult to write like this every time. You can create UDFs or macros in Hive and just call them during conversion.

Create macro command

Macro commands are simpler and more convenient than UDF, butMacros can only be temporary macros, and are only visible and valid in this session. So you need to put the macro script at the head of the SQL script。

DROP TEMPORARY MACRO IF EXISTS date_trans;
CREATE TEMPORARY MACRO date_trans(dt int)
    if(dt is not null and length(dt)=8,
       concat(substr(cast(dt as string),1,4), '-',substr(cast(dt as string),5,2), '-',substr(cast(dt as string),7,2)), 
       null);

--Call
select dt,date_trans(dt) as a from tb;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

AI writing code

If a function or macro command of the same function is called multiple times, it will be very convenient to maintain and the statements are much simpler.

More

A list of built-in date functions in Hive