How can I use date/time criteria in scoreboard queries?

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

How can I use date related fields in scoreboard queries? I've tried comparing date fields to values, such as:

[email protected] AND active = 0 AND close_date = (\'TODAY\')

to create a scoreboard leaf for tickets which have been closed today, but this gives me a "Bad where clause" error.

Environment

Release: 14.1 or higher
Component:  CA Service Desk Manager

Resolution

In Service Desk, fields which hold date and time information are stored in Unix date format. This is an integer which represents the number of seconds since midnight, January 1st 1970 (not counting leap seconds).

i.e. 16:00:00 on the 17th September 2008 is represented as 1226937600.

As these date/time fields are stored as integers, comparing them to strings will not work, and will give the "Bad where clause" error.

There are two functions within Service Desk which will allow direct comparison of date/time fields.

StartAtTime(<time-span>) - This returns the date/time which corresponds to the start of the time-span specified.
EndAtTime(<time-span>) - This returns the date/time which corresponds to the end of the time-span specified.

When these functions are used, you can then use "greater than" (>) and "less than" (<) operators to perform comparisons.

A time span is a period of time which has a defined start and end point, and also a point at which the time span is evaluated. There are several pre-defined time spans available in Service Desk, which are suitable for use in scoreboard stored queries:

AFTER_NOON - Noon this day until midnight tonight; Re-evaluate at midnight
BEFORE_NOON - Beginning of this day until noon; Re-evaluate at midnight
LAST_HOUR - Beginning of last hour until the beginning of this hour; Re-evaluate at the top of next hour
LAST_MONTH - Beginning of last month until beginning of this month; Re-evaluate beginning of next month
LAST_YEAR - Beginning of last year until beginning of this year; Re-evaluate beginning of next year
NEXT_WEEK - Since midnight last night for the next seven days; Re-evaluate at midnight
PAST_DAY - Between this time yesterday and now; Re-evaluate every 10 minutes
PAST_HOUR - 60 minutes ago until now; Re-evaluate every 10 minutes
PAST_MONTH - 30 days ago until now; Re-evaluate at midnight
PAST_WEEK - Seven days ago until now; Re-evaluate at midnight
PAST_YEAR - A year ago until now; Re-evaluate at midnight
THIS_HOUR - Beginning of this hour until now; Re-evaluate top of next hour
THIS_MONTH - Beginning of this month until now; Re-evaluate at midnight
THIS_MONTH_LAST_YEAR - Beginning of this month last year until beginning of next month last year; Re-evaluate the beginning of next month
THIS_YEAR - Beginning of this year until now; Re-evaluate at midnight
TODAY - Since midnight last night; Re-evaluate at midnight
TODO_TODAY - Until midnight tonight; Re-evaluate at midnight
YESTERDAY - Midnight to midnight yesterday; Re-evaluate at midnight tonight

You can also define your own time spans in the Service Desk Administrator interface:

1. Log in as Administrator
2. Select "Service Desk".
3. Select "Application Data".
4. Select "Codes".
5. Select "Timespans".

This means that the initial scoreboard stored query:

[email protected] AND active = 0 AND close_date= (\'TODAY\')

should be written as:

[email protected] AND active = 0 AND close_date > StartAtTime(\'TODAY\')

This will evaluate the Unix time for the start of today (i.e. 17th November 2008 would be 1226880000) and will list all of those tickets where the "close_date" field is greater than this value, which means that it will list tickets which have been closed since the previous midnight.

Using similar logic, a scoreboard stored query to list tickets which were closed yesterday would be:

[email protected] AND active = 0 AND close_date > StartAtTime (\'YESTERDAY\') AND close_date < StartAtTime (\'TODAY\')