TRUNCATE Function

The TRUNCATE function truncates a timestamp field to a given granularity, yielding a value that can be used as a grouping field. Before the timestamp field is truncated, the TRUNCATE function can optionally shift the value to another time first. The syntax for the function is:

TRUNCATE(<timestamp field>, <precision> [, <time shift>])

For example:

GET /Email/Message/_aggregate?m=COUNT(*)&f=TRUNCATE(SendDate,DAY,GMT-2)
&q=SendDate >= "2010-07-17"&shards=2014-06-01

This query finds all messages whose SendDate is >= "2010-07-17" in the shard named 2014‑06‑01. For each one, it subtracts 2 hours from the SendDate value and then truncates (“rounds down”) to the nearest day. The count of all objects for each modified timestamp is computed in a separate group.

The <precision> value must be one of the following mnemonics:

Precision	Meaning
SECOND	The milliseconds component of the timestamp is set to 0.
MINUTE	The milliseconds and seconds components are set to 0.
HOUR	The milliseconds, seconds, and minutes components are set to 0.
DAY	All time components are set to 0.
WEEK	All time components are set to 0, and the date components are set to the Monday of the calendar week in which the timestamp falls (as defined by ISO 8601). For example 2010-01-02 is truncated to the week 2009-12-28.
MONTH	All time components are set to 0, and the day component is set to 1.
QUARTER	All time components are set to 0, the day component is set to 1, and the month component is rounded “down” to January, April, July, or October.
YEAR	All time components are set to 0 and the day and month components are set to 1.

 

The optional <time shift> parameter adds or subtracts a specific amount to each object’s timestamp value before truncating it to the requested granularity. Optionally, the parameter can be quoted in single or double quotes. The syntax of the <time shift> parameter is:

<timezone> | <GMT offset>

Where <GMT offset> uses the same format as the NOW function:

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

The meaning of each format is summarized below:

•

<timezone>: A timezone abbreviation (e.g., "PST") or name (e.g., "America/Los_Angeles") can be given. Each object’s timestamp value is assumed to be in GMT (UTC) time and adjusted by the necessary amount to reflect the equivalent value in the given timezone. The allowable values for a <timezone> abbreviation or name are those recognized by the Java function java.util.TimeZone.getAvailableIDs().

•	GMT+<hour> or GMT-<hour>: The term GMT followed by a plus or minus signed followed by an integer hour value adjust each object’s timestamp up or down by the given number of hours.

•	GMT+<hour>:<minute> or GMT-<hour>:<minute>: This is the same as the previous format except that each object’s timestamp is adjusted up or down by the given hour and minute value.

Note that in the GMT versions, the sign (‘+’ or ‘-‘) is required, and in URIs, the ‘+’ sign must be escaped as %2B.

The timestamp field passed to the TRUNCATE function can belong to the perspective table, or it can be at the end of a field path (e.g., TRUNCATE(Messages.SendDate)).

When a grouping field uses the TRUNCATE function, the truncated value is used for the field value within each group. An example in XML is shown below:

<results>

<aggregate query="SendDate >= 2010-07-17" metric="COUNT(*)" group="TRUNCATE(SendDate,HOUR)"/>

<groups>

<group>

<field name="SendDate">2010-07-17 00:00:00</field>

<metric>5</metric>

</group>

<group>

<field name="SendDate">2010-07-17 01:00:00</field>

<metric>4</metric>

</group>

<group>

<field name="SendDate">2010-07-17 02:00:00</field>

<metric>4</metric>

</group>

...

</groups>

<summary>6030</summary>

</results>

In JSON:

{"results": {

"aggregate": {

"group": "TRUNCATE(SendDate,HOUR)",

"metric": "COUNT(*)",

"query": "SendDate >= 2010-07-17"

},

"groups": [

{"group": {

"field": {"SendDate": "2010-07-17 00:00:00"},

"metric": "5"

}},

{"group": {

"field": {"SendDate": "2010-07-17 01:00:00"},

"metric": "4"

}},

{"group": {

"field": {"SendDate": "2010-07-17 02:00:00"},

"metric": "4"

}},

...

]

}}

When the precision parameter is DAY, WEEK, MONTH, QUARTER, or YEAR, the timestamp values in group names are returned as dates only. For example:

GET /Email/Message/_aggregate?m=COUNT(*)&f=TRUNCATE(SendDate,WEEK)&range=0

This returns a response such as the following:

<results>

<aggregate metric="COUNT(*)" query="*" group="TRUNCATE(SendDate,WEEK)"/>

<totalobjects>6030</totalobjects>

<summary>6030</summary>

<totalgroups>2</totalgroups>

<groups>

<group>

<metric>5972</metric>

<field name="TRUNCATE(SendDate,WEEK)">2010-07-19</field>

</group>

<group>

<metric>58</metric>

<field name="TRUNCATE(SendDate,WEEK)">2010-07-26</field>

</group>

</groups>

</results>