CODEWITHSUNDEEP
sqlhadoophivehiveql
Pazaak
Pazaak

Reputation: 15

Hive SQL Integer YYYYMM previous Months

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?

Upvotes: 1

Views: 1945

Answers (3)

HISI
HISI

Reputation: 4797

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

Upvotes: 1

Robert Peake
Robert Peake

Reputation: 118

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());

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

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

Upvotes: 0

Related Questions