Date functions in Hive

admin

Today, I will share list of queries related to “date” logic in which developers generally face issues during Hive query execution.
* Query to get the sysdate
select from_unixtime(unix_timestamp(current_timestamp()))
* Query to get the yesterday’s date. Here 86400 is the number of seconds in a day(24 Hrs * 60 Minutes(in a hour)* 60 Seconds(in a minute))
select to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’)-86400*1));
* Substract the x number of days from the particular date.
select to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’)-86400*x));
* Convert the sysdate to YYYY-MM-DD format
select to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy- MM-dd’)));
* Convert the sysdate to YYYYMMDD format
select regexp_replace(to_date(from_unixtime(unix_timestamp(current_timestamp (),’yyyy-MM-dd’))),’-‘,”);
* Convert the sysdate to YYYY/MM/DD format
select regexp_replace(to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))),’-‘,’/’);
* Convert the sysdate to YYYY_MM_DD format.
select regexp_replace(to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))),’-‘,’_’);
*Add Month in the sysdate.
select add_months(to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))),1);
*Substract Month from the sysdate.
select add_months(to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))),-1);
* Get month part from the sysdate
select month(to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))));
* Get week part from the sysdate
select weekofyear(to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))));
* Get year part from the sysdate
select year(to_date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))));
* Get Quarter part from the sysdate
select INT(( month(to_Date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd HH:mm:ss’)))))/3);
* Date difference between two dates
select datediff(to_Date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’))), to_Date(from_unixtime(unix_timestamp(current_timestamp(),’yyyy-MM-dd’)-86400*1)));




Related posts