Time-Based Relative Searches (Hour, Day, Month)
The purpose of this page is to help you create time-based relative searches in iDempiere. Examples include:
- Show all orders in the last 7 days.
- Show all payment made in the last 3 hours.
Solution description
The easiest and fastest way is to add virtual columns (Column SQL) to the desired table based on the Created column. For example, you can add the following virtual columns to C_Payment.
- chuboe_relative_hours => any document in same hour = 0
- (floor(extract(epoch from now()-created)/3600))
- chuboe_relative_days => any document in current day = 0
- (extract(days from date_trunc('day',now())-date_trunc('day',created)))
- chuboe_relative_months - two options
- any documents in current month = 0 => example: 2021/02/01 to 2021/02/18 where today is Feb 18th (preferred)
- (extract(year from age(date_trunc('month',now()),date_trunc('month',created)))*12 + extract(month from age(date_trunc('month',now()),date_trunc('month',created))))
- any document in last ~30 days = 0 => example: 2021/01/19 to 2021/02/18
- (extract(year from age(now(),created))*12 + extract(month from age(now(),created)))
- any documents in current month = 0 => example: 2021/02/01 to 2021/02/18 where today is Feb 18th (preferred)