Passa al contenuto principale

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?

Elenco degli autori

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.


Utile Inutile

24 su 48 persone hanno trovato utile questa pagina

Aggiungi un commento

Accedi o registrati per inviare un commento.

Hai dimenticato la password?