Absolute dates/times can be referenced in two formats:

YYYY-MM-DD - e.g. 2013-10-31; refers to a date only. This implicitly has a time of 00:00:00 of the specified day.

YYYY-MM-DD HH:MM:SS - e.g. 2012-10-31 23:35:52; refers to a date and a specific time in 24-hour format.

Note that you must specify these dates in the UTC timezone - however, the results returned will be shown adjusted to your timezone (as set in the Preferences section of the agent interface).

This is an example DPQL query to list the tickets created from October 1st to 15th, 2012 in UTC:

SELECT tickets.id
FROM tickets
WHERE tickets.date_created >= '2012-10-01 00:00:00'
AND tickets.date_created < '2012-10-16 23:59:59'
 copy

Note

In date/time comparisons, > (greater than) matches dates/times that are later, and < (less than) matches dates/times earlier.

You can add or subtract periods of time using the MySQL INTERVAL argument. For example:

'2012-10-01' + INTERVAL 2 WEEK
 copy

means a date/time two weeks after October 1st 2012.

You can use INTERVAL 4 HOUR, INTERVAL 3 DAY, INTERVAL 1 MONTH, INTERVAL 2 YEAR etc.

This is useful when adjusting absolute dates to match your timezone. If you wanted to adjust the above example query to find tickets created from October 1st to 15th Eastern Standard Time, you could change it to:

WHERE tickets.date_created >= '2012-10-01' + INTERVAL 5 HOUR
AND tickets.date_created < '2012-10-16' + INTERVAL 5 HOUR
 copy

This adjusts the times from UTC to 5 hours later, ie EST.