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 <timezone> parameter is provided, the UTC time is adjusted up or down to the specified time zone. The <timezone> can be an abbreviation (PST) or a name (America/Los_Angeles). The allowable values for a <timezone> abbreviation or name are those recognized by the Java function java.util.TimeZone.getAvailableIDs().

The <range> 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

THISYEAR

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

LASTYEAR

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 <timezone> 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-05 00: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.