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
-5d Last 5 days including today.
-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.