Find the last day of the month in Hive

Question

My question is: is there a way to find the last day of the month in Hive, for example, an Oracle SQL function?

LAST_DAY(D_Dernier_Jour)

Thanks.

Answer 1

You can use 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 your Github repository and build it. Their wiki page contains all the information on how to build and use it with Hive.

NTN

Answer 2

If you want to avoid custom UDF below, this 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))

Answer 3

Starting with Hive 1.1.0, last_day(string date) a function is available.

last_day(string date)

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

Answer 4

Something like below may give you some inspiration. The code will give you the last day of the previous month. You can accept it to receive the last day of any month that you want.

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

Answer 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; 

Answer 6

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

You can use this project to get the first day / day for different units: day, week, month, quarter, year.

It accepts the input date as String, timestamp, date in various valid formats and displays in various valid formats.

Optionally, you can specify an additional timestamp to be added as an interval.

Using

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])

Answer 7

I think you can use this UDF in your request to hold to_date (string timestamp) . For example: - returns the date part of the string timestamp: to_date ("1970-01-01 00:00:00") = "1970-01-01". Now you need to write UDF (ie 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; 

Hope this helps your case.