Julian Hyde created CALCITE-5951: ------------------------------------ Summary: PRECEDES function, for period-to-date calculations Key: CALCITE-5951 URL: https://issues.apache.org/jira/browse/CALCITE-5951 Project: Calcite Issue Type: Improvement Reporter: Julian Hyde
We propose a {{PRECEDES}} function, for period-to-date calculations in built-in and custom time frames. Like {{{}EXTRACT{}}}, {{{}FLOOR{}}}, and {{{}CEIL{}}}, {{PRECEDES}} works with built-in and custom time frames (see CALCITE-5155). Specifically: * {{PRECEDES(d1, d2, timeFrame)}} returns whether {{d1}} is earlier or equal in the time frame than {{{}d2{}}}; * {{PRECEDES(d1, timeFrame)}} is shorthand for {{PRECEDES(d1, NOW(), timeFrame)}} Examples: * {{PRECEDES(hire_date, WEEK)}} expands to {{EXTRACT(DAY_OF_WEEK FROM hire_date) <= EXTRACT(DAY_OF_WEEK FROM Now())}} * Similarly, {{PRECEDES(hire_date, MONTH)}} expands using a {{DAY_OF_MONTH}} calculation * The calculation for {{YEAR}} is a little more complex; one date in a leap year may have a greater offset in its year than another date in a non-leap year and still precede it. The behavior of {{timeFrame}} is defined by the calendar of the user’s organization. Organizations can define whether weeks start on Monday or Tuesday, the start of a quarter and a year, and can define new time frames (e.g. months in a [4-4-5 calendar|https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar]). -- This message was sent by Atlassian Jira (v8.20.10#820010)