[
https://issues.apache.org/jira/browse/CALCITE-5951?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5951:
---------------------------------
Description:
We propose a {{PRECEDES}} function, for period-to-date calculations in built-in
and custom time frames. Common period-to-date calculations are YTD
(year-to-date), QTD (quarter-to-date), MTD (month-to-date), WTD (week-to-date).
Like the existing {{EXTRACT}}, {{FLOOR}}, and {{CEIL}} functions, {{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]).
was:
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]).
> 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
> Priority: Major
>
> We propose a {{PRECEDES}} function, for period-to-date calculations in
> built-in and custom time frames. Common period-to-date calculations are YTD
> (year-to-date), QTD (quarter-to-date), MTD (month-to-date), WTD
> (week-to-date).
> Like the existing {{EXTRACT}}, {{FLOOR}}, and {{CEIL}} functions,
> {{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)
