Selecting the First Day of the Month in Hive

Selecting the first day of the month in HIVE

To get the first day of the month, you can use:

date_add(<date>,
         1 - day(<date>) )

Applied to your expression:

date_add(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd'),
         1 - day(FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd')) 
        )

But this will work for any column in the right format.

How to get the first day month of X months ago in Hive?

To get X months ago of a given month, you have just to add minus, for example:

select add_months('2018-03-11',-4);

How to calculate certain date using month in Hive

Use add_month to get previous month date, use TRUNC to get first day:

select trunc(add_months(current_date,-1),'MM') --returns 2021-06-01 for current_date = 2021-07-22

If you do not have TRUNC function, then use add_month to get previous month date, use substr to get 'yyyy-MM', concatenate with '-01' to get first day of month:

select concat(substr(add_months(current_date,-1),1,7),'-01') --returns 2021-06-01 for current_date = 2021-07-22

Also you can subtract 2 months, get last_day() and add one day to get previous month first day:

select date_add(last_day(add_months(current_date, -2)),1) --returns 2021-06-01 for current_date = 2021-07-22

And one more simple method:

select date_format(add_months(current_date, -1),'yyyy-MM-01') 

Selecting YYYYMM of the previous month in HIVE

You could do (year('2015-04-30')*100+month('2015-04-30'))-1 for the above mentioned date, it will return 201503 or something like (year(from_unixtime(unix_timestamp()))*100+month(from_unixtime(unix_timestamp())))-1 for today's previous month. Assuming your date column is in 'yyyy-mm-dd' format you can use the first example and substitute the date string with your table column name; for any other format the second example will do, add the column name in the unix_timestamp() operator.

How to get the start date of the week by week number of year in Hive? First day of week should be Monday

Try the code below, where year and week are the corresponding column names of your table.

select date(
    from_unixtime(
        unix_timestamp(concat(year,'-',week,'-','1'), 'yyyy-w-u')
    )
) from <your_table_name>;

hive get each month end date

Use last_day() and day() functions:

hive> select day(last_day(current_date)) ;
OK
31
Time taken: 0.079 seconds, Fetched: 1 row(s)

Apply to_date() to convert your column before applying last_day().

Select first day of month in presto

Use date_trunc('month', date_value)

presto> select date_trunc('month', DATE '2018-05-24');
   _col0
------------
 2018-05-01
(1 row)

---

Related Topics