Timestamp Clauses (OLAP)

[Table of Contents](https://github.com/dell-oss/Doradus/wiki/OLAP Databases: Table-of-Contents) | Previous | Next
Doradus Query Language: Timestamp Clauses

---

This section describes special clauses that can be used with timestamp fields.

Subfield Clauses

Timestamp fields possess date/time _subfields _that can be used in equality clauses. A subfield is accessed using "dot" notation and an upper-case mnemonic. Each subfield is an integer value and can be compared to an integer constant. Only equality (=) comparisons are allowed for subfields. Examples:

ReceiptDate.MONTH = 2	// month = February, any year
SendDate.DAY = 15		// day-of-month is the 15th
NOT SendDate.HOUR = 12	// hour other than 12 (noon)  

The recognized subfields of a timestamp field and their possible range values are:

• YEAR: any integer
• MONTH: 1 to 12
• DAY: 1 to 31
• HOUR: 0 to 23
• MINUTE: 0 to 59
• SECOND: 0 to 59

Though timestamp fields will store sub-second precision, there are no subfields that allow querying the sub-second portion of specific values.

NOW Function

A timestamp field can be compared to the current time, optionally adjusted by an offset, using the NOW function. The NOW function dynamically computes a timestamp value, which can be used anywhere a timestamp literal value can be used. The NOW function uses the general format:

NOW([<timezone> | <GMT offset>] [<unit adjust>])

The basic formats supported by the NOW function are:

• NOW(): Without any parameters, NOW creates a timestamp equal to the current time in the UTC (GMT) time zone. (Remember that Doradus considers all timestamp fields values as belonging to the UTC time zone.)
• NOW(<timezone>): A time zone mnemonic (PST) or name (US/Pacific) can be passed, which creates a timestamp equal to the current time in the given time zone. The values supported for the parameter are those returned by the Java function java.util.TimeZone.getAvailableIDs().
• NOW(<GMT offset>): This format creates a timestamp equal to the current in UTC, offset by a specific hour/minute value. The <GMT offset> must use the format:

GMT<sign><hours>[:<minutes>]

where <sign> is a plus ('+') or minus ('-') sign and <hours> is an integer. If provided, <minutes> is an integer preceded by a colon.

• NOW((<unit adjust>): This format creates a timestamp relative to the current UTC time and adjusts a single unit by a specific amount. The <unit adjust> parameter has the format:

<sign><amount><unit>

where <sign> is a plus ('+') or minus ('-') sign, <amount> is an integer, and <unit> is a singular or plural time/date mnemonic (uppercase). Recognized values (in plural form) are SECONDS, MINUTES, HOURS, DAYS, MONTHS, or YEARS.

• NOW(<timezone> <unit adjust>) and NOW(<GMT offset> <unit adjust>): Both the <timezone> and <GMT offset> parameters can be combined with a <unit adjust>. In this case, the current UTC time is first adjusted to the specified timezone or GMT offset and then adjusted by the given unit adjustment.

Below are example NOW functions and the values they generate. For illustrative purposes, assume that the current time on the Doradus server to which the NOW function is submitted is 2013-12-04 01:24:35.986 UTC.

Function	Timestamp Created	Comments
NOW()	2013-12-04 01:24:35.986	Current UTC time (no adjustment)
NOW(PST)	2013-12-03 17:24:35.986	Pacific Standard Time (-8 hours)
NOW(Europe/Moscow)	2013-12-04 05:24:35.986	Moscow Standard Time (+4 hours)
NOW(GMT+3:15)	2013-12-04 04:39:35.986	UTC incremented by 3 hours, 15 minutes
NOW(GMT-2)	2013-12-03 23:24:35.986	UTC decremented by 2 hours
NOW(+1 DAY)	2013-12-05 01:24:35.986	UTC incremented by 1 day
NOW(+1 MONTH)	2014-01-04 01:24:35.986	UTC incremented by 1 month
NOW(GMT-3:00 +1 YEAR)	2014-12-0322:24:35.986	UTC decremented by 3 hours, incremented by 1 year
NOW(ACT -6 MONTHS)	2013-06-04 11:24:35.986	FMT adjusted to Australian Capitol Territory Time (+10 hours) then decremented by 6 months

(Remember to escape the '+' sign as %2B in URIs since, un-escaped, it is interpreted as a space.)

The value generated by the NOW function can be used wherever a literal timestamp value can appear. Below are some examples:

SendDate > NOW(-1 YEAR)
SendDate >= NOW(PST +9 MONTHS)
ReceiptDate = [NOW() TO NOW(+1 YEAR)]
ReceiptDate = ["2013-01-01" TO NOW(Europe/Moscow)]  

Because the NOW function computes a timestamp relative to the time the query is executed, successive executions or the same query could create different results. This could also affect the results of paged queries.

PERIOD Function

The PERIOD function generates a timestamp value range, computed relative to the current time. It is a shortcut for commonly-used range clauses that occur close to (or relative to) the current date/time. A timestamp field can be compared to a PERIOD function to see if its value falls within the corresponding range. A timestamp range clause using the PERIOD function uses the following form:

field = PERIOD([<timezone>]).<range>

With no parameter, the PERIOD function computes a timestamp range relative to a snapshot of the current time in UTC. If a parameter is provided, the UTC time is adjusted up or down to the specified time zone. The can be an abbreviation (PST) or a name (America/Los_Angeles). The allowable values for a abbreviation or name are those recognized by the Java function java.util.TimeZone.getAvailableIDs().

The parameter is a mnemonic that chooses how the range is computed relative to the "now" snapshot. There are two types of range mnemonics: THIS mnemonics and LAST mnemonics. All mnemonics must be uppercase.

THIS mnemonics compute a range around the current time, that is a range that includes the current time. The recognized THIS mnemonics are:

THISMINUTE
THISHOUR
TODAY
THISWEEK
THISMONTH
THIS YEAR  

Note that TODAY is used as the mnemonic for "this day". THIS mnemonics use no additional parameters. They define a timeframe (minute, hour, day, week, month, or year) that includes the current time. The timeframe is inclusive of the timeframe start but exclusive of the timeframe end. For example, if the current time is 2013-12-17 12:40:13, the function PERIOD.THISHOUR defines the range:

["2013-12-17 12:00:00" TO "2013-12-17 13:00:00"}

Note the exclusive bracket (}) on the right hand side.

LAST mnemonics compute a range that ends at the current time. That is, they choose a timeframe that leads up to "now", going back an exact number of units. The recognized LAST mnemonics are:

LASTMINUTE
LASTHOUR
LASTDAY
LASTWEEK
LASTMONTH
LAST YEAR  

By default, the LAST mnemonics reach back one unit: 1 minute, 1 hour, etc. Optionally, they allow an integral parameter that extends the timeframe back a whole number of units. For example, LASTMINUTE(2) means "within the last 2 minutes", LASTMONTH(3) means "within the last 3 months", etc. LAST periods are inclusive at both ends of the range. For example, if the current time is 2013-12-17 12:40:13, the function PERIOD.LASTHOUR defines the range:

["2013-12-17 11:40:13" TO "2013-12-17 12:40:13"]

Example timestamp clauses using the PERIOD function are shown below:

ExpireDate = PERIOD().TODAY						// Field has the same year, month and date as now (UTC)
CreationStamp = PERIOD().LASTWEEK				// Field falls within the last week (UTC)
MaturityDate = PERIOD(PST).LASTMONTH(2)			// Field falls within the last 2 months (PST)
SendDate = PERIOD(Europe/Moscow).LASTYEAR(3)	// Field falls within the last 3 years (Moscow time)  

Example ranges generated by each mnemonic are given below. For illustrative purposes, assume that the current time on the Doradus server to which the PERIOD function is submitted is 2013-12-04 01:24:35 UTC. If a parameter is included, the "now" value would first be adjusted to the corresponding timezone, and the range would be computed relative to the adjusted value.

range Mnemonic	Timestamp Range	Comments
THISMINUTE	["2013-12-04 01:24:00" TO "2013-12-04 01:25:00"}	Same year, month, day, hour, and minute as now.
LASTMINUTE	["2013-12-04 01:23:35" TO "2013-12-04 01:24:35"]	Within the last minute (60 seconds).
THISHOUR	["2013-12-04 01:00:00" TO "2013-12-04 02:00:00"}	Same year, month, day, and hour as now.
LASTHOUR	["2013-12-04 00:24:35" TO "2013-12-04 01:24:35"]	Within the last hour (60 minutes).
TODAY	["2013-12-04 00:00:00" TO "2013-12-0500:00:00"}	Same year, month, and day as now.
LASTDAY	["2013-12-03 01:24:35" TO "2013-12-04 01:24:35"]	Within the last day (24 hours).
THISWEEK	["2013-12-02 00:00:00" TO "2013-12-09 00:00:00"}	Same week as now (based on ISO 8601).
LASTWEEK	["2013-11-27 01:24:35" TO "2013-12-04 01:24:35"]	Within the last 7 days.
THISMONTH	["2013-12-00 00:00:00" TO "2014-01-01 00:00:00"}	Same year and month as now.
LASTMONTH	["2013-11-04 01:24:35" TO "2013-12-04 01:24:35"]	Within the last calendar month.
THISYEAR	["2013-01-01 00:00:00" TO "2014-01-01 00:00:00"}	Same year as now.
LASTYEAR	["2012-12-04 01:24:35" TO "2013-12-04 01:24:35"]	Within the last year.