CODEWITHSUNDEEP
hivehiveql
stackq
stackq

Reputation: 491

Constructing a date with year and month

I want to construct a date from just a year and month. I'm currently running a query like this:

SELECT
    yyyy_mm_dd AS 
    t.year,
    t.month,
    t.id,
    t.name,
    ...,
    ...
    ...
FROM(
    SELECT
        p.last_yyyy_mm_dd,
        p.id,
        p.name,
    FROM(
        SELECT
            t.*,
            MAX(yyyy_mm_dd) OVER (PARTITION BY last_day(yyyy_mm_dd)) as last_yyyy_mm_dd
        FROM
            table1 t
        WHERE
            yyyy_mm_dd IS NOT NULL
            AND name = 'XXX'
    ) p
    WHERE
        yyyy_mm_dd = last_yyyy_mm_dd
) p
RIGHT JOIN(
    SELECT
        YEAR(yyyy_mm_dd) as year,
        MONTH(yyyy_mm_dd) as month,
        id,
        name,
        ...,
        ...,
        ...,
    FROM
        table2
    WHERE
        yyyy_mm_dd IS NOT NULL
        AND name = 'XXX'
    GROUP BY
        1,2,3,4
    ) t
ON
    p.id = t.id
    AND YEAR(p.last_yyyy_mm_dd) = t.year
    AND MONTH(p.last_yyyy_mm_dd) = t.month

On the top level select, I'd like to also have a date (YYYY-MM-DD) field constructed from the year and month. The day part should be the last day of the given month. I.e. for January it'll be 31.

I've looked here https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions and can see last_day which might be useful, however there doesn't seem to be a make date function like in MySQL - https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html. How can I construct a date like this in Hive?

Upvotes: 1

Views: 1129

Answers (2)

leftjoin
leftjoin

Reputation: 38290

If you have normal date, you can extract yyyy-MM using substr or date_format:

select substr('2020-01-15',1,7);
OK
2020-01

select date_format('2020-01-15','yyyy-MM');
OK
2020-01

To get last month day from normal date, you can use last_day 

select last_day(concat(substr('2020-01-15',1,7),'-01'));
OK
2020-01-31

And if you have yyyy-MM and want last month day, concat it with '-01' and apply last_day:

select last_day(concat('2020-01','-01'));
OK
2020-01-31

Upvotes: 1

Tsvetelina Mariyanova
Tsvetelina Mariyanova

Reputation: 359

try the next code. It works for me: 

select to_date(date_sub(add_months(concat(from_unixtime(unix_timestamp('2020-01','yyyy-MM'), 'yyyy-MM'),'-01'),1),1))

Output: 

+-------------+--+
|     _c0     |
+-------------+--+
| 2020-01-31  |
+-------------+--+

Upvotes: 0

Related Questions