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 <timezone> 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-03 22: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.