How do I reference specific dates when using reports?

Dates can be referenced in two formats:

YYYY-MM-DD e.g. 2012-10-30 - if you specify the date only, comparisons are made using 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 times/dates in UTC - however, the results returned will be shown adjusted to your timezone.

(Relative date references like %TODAY% always refer to your timezone, as set in your agent account Preferences.)

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'
AND tickets.date_created <= '2012-10-15 23:59:59'

 To adjust for your local time, you can simply add or subtract an INTERVAL e.g.:

WHERE tickets.date_created  >= '2012-10-01' + INTERVAL 5 HOUR

This adjusts the time from the implicit 00:00:00 on October 1st UTC to 5 hours later, ie 05:00 UTC or 00:00 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.

More information here.

Helpful Unhelpful

1 of 2 people found this page helpful

Add a comment

You need to log in before you can submit a comment.

Need a password reminder?