Skip to main content

Natural Time Expression

Knowledge Checkpoint

This documentation assumes you are familiar with the following concepts:

Introduction

Using the matches filter operator and our natural time expressions, you can construct flexible time filtering conditions for your report. We categorize the time expressions by their functionality:

  • Select exactly one time unit: The expression helps users selecting one particular time unit, like a certain year, day, or hour.
  • Select a time span: The expression helps users selecting more than one time unit (like 2 years, 3 days, 4 hours), or a range from one to another time point.
Time unit

The time units we currently support are: year(s), quarter(s), months(s), week(s), day(s), hour(s), minute(s), second(s)

Select exactly one time unit​

Absolute time expressions​

PatternExampleTime coveredNotes
YYYY2024All time in the year 2024
MMM YYYY, MMMM YYYYApr 2024, April 2024All time in April of 2024
DD MMM YYYY, MMM DD YYYY30 April 2024, April 30 20242024-04-30
DD-MM-YYYY, YYYY-MM-DD30-04-2024, 2024-04-302024-04-30
DD/MM/YYYY, YYYY/MM/DD30/04/2024, 2024/04/302024-04-30
HH
HH:MM
HH:MM:SS		15
15:00
15:00:30		From the beginning the time expression to just before the next smallest unit of the expression starts.
15:00 covers 60 seconds from 15:00:00 to just before the next minute (15:01:00) starts, because minute is the smallest unit mentioned.		Without a [date] part, the time is interpreted as of the current date.
[date] [time]2024-04-30 15
2024-04-30 15:00
2024-04-30 15:00:30		The time period of the mentioned dateOnly accepted [date] formats: DD-MM-YYYY, YYYY-MM-DD, DD/MM/YYYY, YYYY/MM/DD.

Relative time expressions​

Relative time expression's resolution to an exact time will depend on the current time.

PatternExampleTime coveredNotes
beginning1970-01-01
todayToday is 2024-04-01, and we use today2024-04-01
yesterdayToday is 2024-04-01, and we use yesterday2024-03-31
tomorrowToday is 2024-04-01, and we use tomorrow2024-04-02
nowToday is 2024-04-01, the current time is 15:00:30, and we use now2024-04-01 15:00:30
this [time unit]Today is 2024-04-15, and we use this monthAll time within Apr 2024Cover all time within the same [time unit] with the current moment.
The current time is 2024-04-15 15:30:00, and we use this hourAll time within 2024-04-15, 15pm
[weekday] this weekToday is Thursday 2024-04-18, and we use monday this week or mon this week2024-04-15
[weekday] last x week(s)Today is Thursday 2024-04-18, and we use monday last 1 week2024-04-08last/next keywords behave the same as the last/next operators
[weekday] next x week(s)Today is Thursday 2024-04-18, and we use monday next 1 week2024-04-22
[time period] beginToday is 2024-04-15, and we use this month begin2024-04-01[time period] = this/next/last + [quantity] + [time unit].

Examples of [time period]: this year, this month, last 2 weeks, this hour, next 10 minutes...
The current time is 2024-01-01 15:30, and we use this hour beginOne second from 2024-01-01 15:00:00 to before 2024-01-01 15:00:01 starts
[time period] endToday is 2024-04-15, and we use this month end2024-04-30
The current time is 2024-01-01 15:30, and we use this hour endOne second from 2024-01-01 15:59:59 to before 2024-01-01 16:00:00 starts
x [time unit] agoToday is 2024-04-15, and we use 5 days ago2024-04-10Cover a time unit that happened x complete time units before now.
The current time is 2024-04-15 15:30:00, and we use 5 hours agoAll time within 2024-04-15, 10am to 11am
x [time unit] from nowToday is 2024-04-15, and we use 2 days from now2024-04-17Cover a time unit that happened x complete time units from now.
The current time is 2024-04-15 15:30:00, and we use 5 hours from nowAll time within 2024-04-15, 20pm to 21pm
begin and end

begin and end keywords are used to return the first and last unit of a time range. They can be used in combination with time span expressions to create very flexible exact-unit selection.

These exact-unit selection can also be used as the start and end for even more flexible time span selection.

Examples​

  • Year: 2024
  • Month: Apr 2024 / April 2024
  • Date: 30 April 2024, 2024-04-30
  • Relative dates:
    • today, tomorrow, yesterday, this day
    • Monday this week, Monday last 2 weeks, Tuesday next week, Tuesday next 2 weeks
    • this month begin, this month end; this week begin, this week end
    • 3 days ago / last 3 days begin
    • next 2 day end: returns the date of two complete days from the current date
  • Date time: 2024-04-30 15:30:00
  • Relative time:
    • now
    • 1 hours ago, 20 minutes ago, 30 seconds ago
    • 1 hour from now, 20 minutes from now, 30 seconds from now

Select a time span​

These expressions help users to select a time range between two points, or select a span of N time units.

PatternExampleTime coveredNotes
[start] -/to [end]Today is 2024-04-15, and we use 2024-04-15 - this month end or today to this month endAll dates from 2024-04-15 to 2024-04-30Cover all time between two points including the end point.
[start] and [end] can be any exact-unit expression. Their time granularity can be different.
Current time is 2024-01-01 15:35:00, and we use last 3 hours to this minuteAll time from 2024-01-01 12:00:00 to (and including) 2024-01-01 15:35
[start] till/until [end]Today is 2024-04-15, and we use today till this month endAll dates from 2024-04-15 to 2024-04-29Cover all time between two points, excluding the end point.
[start] and [end] can be any exact-unit expression. Their time granularity can be different.
Current time is 2024-01-01 15:35:00, and we use last 3 hours to this minuteAll time from 2024-01-01 12:00:00 to before 2024-01-01 15:35:00 starts
last x [time unit]Today is 2024-04-15, and we use last 2 monthsAll dates in Feb 2024 and Mar 2024, since Apr 2024 is incompleteCover all time in the previous x complete units.
Current time is 2024-01-01 15:35, and we use last 3 hoursAll time from 2024-01-01 12:00 to (and including) 2024-01-01 14pm
next x [time unit]Today is 2024-04-15, and we use next 2 monthsAll dates in May 2024 and Jun 2024, since Apr 2024 is incompleteCover all dates in the next x complete units.
Current time is 2024-01-01 15:35, and we use next 3 hoursAll time from 2024-01-01 16:00 to (and including) 2024-01-01 18pm

Examples​

  • [start] and [end] in the same unit:

    • April 15 2024 to may 15 2024, 2024-04-15 to 2024-05-15
    • April 15 2024 until may 16 2024, 2024-04-15 till 2024-05-16
    • 30 april 2024 12:00 till 30 april 2024 14:00
    • last 3 days, next 3 weeks
    • last 10 hours, next 30 minutes

  • [start] and [end] in different units:

    • 2023 to september 2024
    • 2 months ago to monday last week
    • this year begin to this week end
    • yesterday to now
    • this weekend to next 2 months

Caching implications​

When using relative time expressions, your SQL will be dynamically generated according to the current time.
Therefore, relative time expressions can affect how Holistics caches your SQL results.

For example, let's say we have this filter that uses now

modeling-matches-filter

When we open the report (containing the above filter) at 2024-09-17 01:09:38.000 -07:00, the report generates a SQL with this WHERE clause:

WHERE
  (CAST ( "order_master"."order_created_at" AS timestamptz )
    >= CAST ( '2024-09-14T00:00:00.000-07:00' AS timestamptz ))
  AND
  (CAST ( "order_master"."order_created_at" AS timestamptz )
    < CAST ( '2024-09-17T01:09:38.000-07:00' AS timestamptz ))

Then when we open the report at 2024-09-17 01:17:33.000 -07:00, the report generates another SQL with this WHERE clause:

WHERE
  (CAST ( "order_master"."order_created_at" AS timestamptz )
    >= CAST ( '2024-09-14T00:00:00.000-07:00' AS timestamptz ))
  AND
  (CAST ( "order_master"."order_created_at" AS timestamptz )
    < CAST ( '2024-09-17T01:17:33.000-07:00' AS timestamptz ))

Because the above 2 SQLs are different, Holistics will not re-use cache in the latter run, regardless of the report's cache settings.

If we used today instead of now:

modeling-matches-filter-today

The report would generate this same SQL when we visit it at both 2024-09-17 01:09:38.000 -07:00 and 2024-09-17 01:17:33.000 -07:00:

WHERE
  (CAST ( "order_master"."order_created_at" AS timestamptz )
    >= CAST ( '2024-09-14T00:00:00.000-07:00' AS timestamptz ))
  AND
  (CAST ( "order_master"."order_created_at" AS timestamptz )
    < CAST ( '2024-09-18T00:00:00.000-07:00' AS timestamptz ))

And Holistics would be able to re-use cache in both runs.

Conclusion

Be mindful of the caching implications when using Natural Time Expression.

Only use high-freshness values like now when you really need high freshness.
Otherwise, you can choose lower-freshness values like today, this hour, last hour, etc.

Learn more about Holistics Report Caching here.

Let us know what you think about this document :)