Hive SQL Integer YYYYMM previous Months

Question

I want to create an SQL statement that looks for the last 2 months.

For example:

Select * 
from x
where sampledate<= YYYYMM-2

currently i am using this:

(year(from_unixtime(unix_timestamp()))*100+month(from_unixtime(unix_timestamp())))-1

but it returns wrong statements for the first 2 months of a year :(

My idea is to calculate with a date and then change it to a yyyymm integer format.

Any ideas?

score 1 · Accepted Answer · edited Apr 17 '18 at 14:08

1

Could you try this:

SELECT colomn
FROM table
WHERE date > (SELECT add_months(from_unixtime(unix_timestamp()),-2));

or you can use:

SELECT colomn
FROM table
WHERE date > to_date(SELECT year(add_months(from_unixtime(unix_timestamp()),-2))+month(add_months(from_unixtime(unix_timestamp()),-2)));

Combined with regex&substring:

SELECT colomn
FROM table
where sampledate>=substr(regexp_replace(add_months(from_unixtime(unix_timestamp()),-2), '-',''),1,6)

to get a YYYYMM date

edited Apr 17 '18 at 14:08

MarmiK

5,639
6
40
49

answered Apr 17 '18 at 11:32

HISI

4,557
4
35
51

1

i needed the sampledate like YYYYMM so i used your code with regex&substring to transform the 2017-12-30 to 201712 with: sampledate>=substr(regexp_replace(add_months(from_unixtime(unix_timestamp()),-2), '-',''),1,6) – Pazaak Apr 17 '18 at 13:02
You can edit this answer by adding the regex&substring and you can also accepte it if you think that it's helpful – HISI Apr 17 '18 at 13:09

MatBailie · Answer 2 · 2018-04-17T11:38:23.707

If you want to avoid converting an integer, in YYYYMM format, to and from a date, you can just use maths and CASE statements...

For example YYYYMM % 100 will give you MM. Then you can check if it's 2 or less. If it is 2 or less, deduct 100 to reduce by a year, and add 12 to get the month as 13 or 14. Then, deducting 2 will give you the right answer.

Re-arranging that, you get YYYYMM - 2 + (88, if the month is 1 or 2)

sampledate <= YYYYMM - 2 + CASE WHEN YYYYMM % 100 <= 2 THEN 88 ELSE 0 END

The better idea may just be to reshape your data so that you actually have a (real) date field, and just use ADD_MONTHS(aRealDate, -2)...

EDIT:

If your actual issue is generating the YYYYMM value for "two months ago", then deduct the 2 months before you use the YEAR() and MONTH() functions.

year(  ADD_MONTHS(from_unixtime(unix_timestamp()), -2) )*100
+
month( ADD_MONTHS(from_unixtime(unix_timestamp()), -2) )

score 0 · Answer 3 · answered Apr 17 '18 at 12:16

Try something like this. First, a utility to get the date n months in the future/past:

public Date nMonthsFromDate(Date date, int interval) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    // E.G. to get 2 months ago, add -2
    cal.add(Calendar.MONTH, interval); 
    Date result = cal.getTime();
    return result;
}

Criteria query on the entity, here Member:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Member> q = cb.createQuery(Member.class);
Root<Member> memberRoot = q.from(Member.class);
Date theDate = nMonthsFromToday(-2);
Predicate pred = cb.greaterThanOrEqualTo(
    memberRoot.<Date>get("timeStamp"), theDate);
q.where(pred);
TypedQuery<Member> theQuery = em.createQuery(q);
String qStr = theQuery
    .unwrap(org.apache.openjpa.persistence.QueryImpl.class)
    .getQueryString();
LOG.info("Query: " + qStr);
List<Member> results = null;
try {
    results = theQuery.getResultList();
} catch (Exception e) {
    LOG.severe(e.getMessage());
    e.printStackTrace();
}
return results;

Finally, beware of comparing a date [java.util.Date] to a timestamp [util.sql.Date]. Due to a quirk in Java, for equivalent dates, date.equals(timeStamp) returns true, BUT timeStamp.equals(date) returns FALSE. To conform both dates to a java.util.Date:

public java.util.Date getStandardDate(Date date) {
    return new java.util.Date(date.getTime());

Thank you for your detailed answer. I was looking for a where condition for an SQL query. Is that scala code? Best regards pazaak — Pazaak, Apr 17 '18 at 13:29

Hive SQL Integer YYYYMM previous Months

3 Answers3

Linked