Date-Time condition builder definitions - Support and Troubleshooting

Date-Time condition builder definitions Issue  This article is meant to clarify how certain operators function on the condition builder in regards to the Date-Time functionality.

The Condition Builder uses a date-time selection feature to allow users generating reports, including those used in the Fiscal Calendar plugin that was released as part of Jakarta, the ability to select date-time ranges for any report.

Due to some of the confusion arising out of the complexity of some of the date-time operators, here is a definition of what each type of operator means so that users will be able to understand and confirm that the information being returned in a report is accurate and true.

Fiscal periods in the Standard Fiscal Calendar are the same as Date/Time periods in a regular calendar , i.e. Current Fiscal Month = Current Month, 2 nd Fiscal Quarter = 2 nd Quarter, etc.

In this article, the terms date/time period and fiscal period are used interchangeably.

Resolution Definitions Last Fiscal Period

The last fiscal period (day, week, month, quarter, etc.) means a completed fiscal period immediately preceding the current period. When we query for the last date/time period, we entirely ignore the current period. Querying for the last date/time period means covering a range between the first second of the first day of the last completed date/time period and the last second of the last day of the last completed date/time period. Example:

If current month is June, the Last Fiscal Month period starts on May 1 at 00:00:00, ends on May 31 at 23:59:59. The same logic applies to the last two, three, etc. fiscal periods: The Last 2 Fiscal Quarters period starts on October 1, 2016 at 00:00:00, ends on March 31, 2017 at 23:59:59. Current Fiscal Period

Querying for the current fiscal period means covering a range between the first second of the first day of the current fiscal period and the last second of the last day of the current fiscal period. Example:

If current month is June, the Current Quarter period starts on April 1 at 00:00:00, ends on June 30 at 23:59:59. Next Fiscal Period

The next fiscal period means a fiscal period immediately starting after the end of the current period. Querying for the next date/time period means covering a range between the first second of the first day of the next date/time period and the last second of the last day of the next completed date/time period. Example:

If current month is June of year 2017, the Next 12 Months period starts on July 1, 2017 at 00:00:00, ends on June 30, 2018 at 23:59:59. Operators used in Condition Builders for Date/Time periods ON - Covers a range between the first second of the first day and the last second of the last day of the date/time period of interest.

BEFORE - Covers a range before the first second of the first day (non-inclusive) of the period of interest.

Example: if current month is June, the query Due date | before | last month means Due date

AT OR BEFORE - Covers a range before the last second of the last day (inclusive) of the period of interest.

Example: if current month is June, the query Due date | at or before | last month means Due date

AFTER - Covers a range after the last second of the last day (non-inclusive) of the period of interest.

Example: if current month is June, the query Due date | after | next month means Due date > 2017-07-31 23:59:59

AT OR AFTER - Covers a range after the first second of the first day (inclusive) of the period of interest.

Example: if current month is June, the query Due date | at or after | next month means Due date >= 2017-07-01 00:00:00

BETWEEN - Covers a range between the first second of the first day (inclusive) of the period on the left of the between operator AND the last second of the last day (inclusive) of the period on the right.

Example: if current month is June, the query Due date | between | last 6 months | and | next quarter means Due date >= 2017-12-01 00:00:00 AND Due date

TREND Operators - TREND operators cover time periods expressed in absolute as opposite to relative terms, i.e. generic terms like day of week, month of year, etc. Monday, Tuesday, and so on are used rather than next week, last week, etc. January, February and so on are used rather than next month, last 3 months, etc. It’s easier to understand from the following examples:

Examples:

As our test data consists of 75 incident records whose Due dates span from January 1, 2014 to December 31, 2020, the query Due date | trend | on | Monday returns all records with Due day = Monday in the table. Query Due date | trend | on or after | October returns all records whose Due dates fall on October, November, or December of all covered years. Query Due date | trend | before | 5 pm hour returns all records whose Due dates contain timestamps before 5pm non-inclusive. RELATIVE Operators - Cover date-time periods that are certain number of hours, days, months, etc. AGO or FROM NOW.

From practical standpoint, the RELATIVE ON operator doesn’t make sense because it refers to an exact moment.

Examples: