'Finding the date before/after a date stored in int representation (Hive SQL)

I need to select between a range of dates centered on a specific date, but my partition column of dates is stored as int.

For example, selecting between the dates before and after '20210901' (20210831 to 20210902)

Is there any way to do that which is less convoluted than what I came up with below?

between cast(date_format(date_sub(date_format(from_unixtime(unix_timestamp(cast('20210901' as string),'yyyyMMdd')),'yyyy-MM-dd'),1),'yyyyMMdd') as int) and \
cast(date_format(date_add(date_format(from_unixtime(unix_timestamp(cast('20210901' as string),'yyyyMMdd')),'yyyy-MM-dd'),1),'yyyyMMdd');
sqldatehivecastingtimestamp


Solution 1:[1]

You seem to have an awful lot of steps in there. I think this should get you there. Still not very pretty, but it sure seems clearer to me.

WHERE
from_Unixtime(unix_timestamp(cast (<integer date column> as string),'yyyyMMdd'), 'yyyy-MM-dd') BETWEEN
date_add( from_Unixtime(unix_timestamp(cast (20210901 as string),'yyyyMMdd'), 'yyyy-MM-dd') ,-1) AND
date_add( from_Unixtime(unix_timestamp(cast (20210901 as string),'yyyyMMdd'), 'yyyy-MM-dd') ,-1)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Andrew

Related Questions