Time Range Expressions

The time range for IN operator understands both relative time and absolute time. Absolute time is the same as in BETWEEN operator.

Relative time is defined as: <sign><number><unit>, for example: -5d means 5 days ago.

Note: All dates and times are defined in the local time zone of the machine where the system is installed and it is not based on the browser time zone.

The following time units are available:

  • s - second
  • m - minute
  • h - hour
  • d - day
  • w - week
  • M - month
  • q - quarter (3 months)
  • y - year

The supported timestamp formats are:

  • Any day of the week; for example, MON, TUE, WED, THU, FRI, SAT, SUN
  • NOW specifies up to the current time
  • Today specifies as the end of the day (23:59:59)
  • yyyy-MM-dd HH:mm:ss, {d yyyy-MM-dd HH:mm:ss}, {t yyyy-MM-dd HH:mm:ss}, or {ts yyyy-
  • MM-dd HH:mm:ss}
  • MM/dd/yyyy HH:mm:ss
  • BETWEEN and IN support dates (yyyy-MM-dd or MM/dd/yyyy). The interpretation depends on whether it is used as beginning or end of time period. When used in beginning it is equivalent to yyyy-MM-dd 00:00:00; when used at the end - yyyy-MM-dd 23:59:59.

Examples

Time Range Expression Definition
-23h Last 23 hours.

For example, if the current time is 2014-10-20 08:00:00, -23h is the period from 2014-10-19 09:00:00, which is exactly the last 23 hours.

-1d Last 1 day including today.
Note: Starts from the midnight of the previous date till the current time. Therefore, the period is always more than 24 hours.

For example, if the current time is 2014-10-20 08:00:00, -1d is the period from 2014-10-19 00:00:00, which is 32 hours.

-1M Last month.
"2014-10-20" From 2014-10-20 00:00:00 and 2014-10-20 23:59:59.
"2014-10-20":"2014-10-25" From 2014-10-20 00:00:00 until 2014-10-25 23:59:59.
"2014-10-20 14:00:00":"2014-10-25 20:00:10" From 2014-10-20 14:00:00 until 2014-10-25 20:00:10.
"2014-10-20 14:00:00":NOW From 2014-10-20 14:00:00 until now (the time the query was issued).
MON:NOW From beginning of last Monday till the current time.