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)
