Skip to main content

How do I reference specific dates when using reports? - Knowledgebase / Developer & Reporting / Creating Reports - Deskpro Support

How do I reference specific dates when using reports?

Authors list

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'


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 DAYINTERVAL 1 MONTHINTERVAL 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.

Note

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.


Helpful Unhelpful

24 of 48 people found this page helpful

Add a comment

Please log in or register to submit a comment.

Need a password reminder?