317 views

Date-Time Condition Builder Definitions

Overview

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, 2nd Fiscal Quarter = 2nd Quarter, etc.

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

Note: For visual representation of the fiscal periods used with the ON, BEFORE, AT OR BEFORE, AFTER, AT OR AFTER operators, please refer to the attachment which indicates how the operators work if your current date falls in February 2017.

 

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 < 2017-05-01 00:00:00

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 <= 2017-05-31 23:59:59

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 <= 2017-09-30 23:59:59

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:

  • Query Due date | relative | after | 3 | Months | from now returns all records whose Due dates are 3 months greater than the moment you submit the query.
  • Query Due date | relative | before | 6 | Hours | ago returns all records whose Due dates are more than 6 hours back from the moment you submit the query.
 

Article Information

Last Updated:2018-01-02 11:43:50
Published:2018-01-02