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'
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
This adjusts the times from UTC to 5 hours later, ie EST.
A timezone that is behind UTC needs the time difference added to the comparison date/time; a timezone that is ahead needs the time difference subtracted.
Add a comment
Please log in or register to submit a comment.