Find last day of a month in Hive

问题:

My question is : Is there a way to do find the last day of a month in Hive, like Oracle SQL function ? :

LAST_DAY(D_Dernier_Jour)

Thanks.

回答1:

You could make use of last_day(dateString) UDF provided by Nexr. It returns the last day of the month based on a date string with yyyy-MM-dd HH:mm:ss pattern.

Example:
SELECT last_day('2003-03-15 01:22:33') FROM src LIMIT 1;
2003-03-31 00:00:00

You need to pull it from their Github Repository and build. Their wiki page contains all the info on how to build and use it with Hive.

HTH

回答2:

As of Hive 1.1.0, last_day(string date) function is available.

last_day(string date)

Returns the last day of the month which the date belongs to. date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time part of date is ignored.

回答3:

If you want to avoid custom UDF below is another solution: to_date(date_sub(add_months(concat(from_unixtime(unix_timestamp('2015-07-28','yyyy-MM-dd'), 'yyyy-MM'),'-01'),1),1))

回答4:

Something like the below could give you some inspiration. The code will give you the last day of the previous month. You can adopt it to get the last day of any month you want.

date_sub(concat(from_unixtime(unix_timestamp(), 'yyyy-MM'), '-01'), 1)

回答5:

select 
CASE WHEN month(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')) IN(4,6,9,11)
THEN
date_add((FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),30 - day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'))) 
WHEN month(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')) IN(1,3,5,7,8,10,12)
THEN 
date_add((FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),31 - day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'))) 
WHEN month(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')) = 2 and day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'))=28
THEN 
date_add((FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),28 - day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')))
WHEN month(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')) = 2 and day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'))=29
THEN 
date_add((FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),29 - day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')))
END as calc_date from table_name
limit 2;

回答6:

https://github.com/manojkumarvohra/day-extremes

You can use this project to get firstday/lastday for variety of units: day, week, month, quarter, year.

It accepts input date as String, timestamp, date in variety of valid formats and output in variety of valid formats.

Optionally you can mention an optional timestamp to be added as an interval.

Usage

FIRST_DAY_OF( unit, date, input_format[optional], output_format[optional], include_interval [optional], interval[optional])

LAST_DAY_OF( unit, date, input_format[optional], output_format[optional], include_interval [optional], interval[optional])

回答7:

I think you can use this UDF in your Hive query to_date(string timestamp) . For example :-Returns the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01". Now you need to write a UDF say(i.e lastDay)which will implement the following:-

    Date today = new Date();  

    Calendar calendar = Calendar.getInstance();  
    calendar.setTime(today);  

    calendar.add(Calendar.MONTH, 1);  
    calendar.set(Calendar.DAY_OF_MONTH, 1);  
    calendar.add(Calendar.DATE, -1);  

    Date lastDayOfMonth = calendar.getTime();  

    DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    return sdf.format(lastDayOfMonth)

Now your UDF will be something like this:-

select lastDay(to_date(string timestamp)) from xxx;

I hope this will help your cause