跳到主要内容

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

How do I reference specific dates when using reports?

作者列表

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.


有帮助 没有帮助

48 人中 24 人认为这个页面有帮助

添加一条评论

请登录或注册以提交评论。

是否需要密码提醒?