Skip to main content

Relative dates with placeholders

Date and time operations

It’s often more useful to have a report that matches helpdesk data for a relative time period, e.g. the current week or the last month, rather than specific dates.

You can write a report like this using date placeholders such as %LAST_WEEK% (see See List of date placeholders for a full list).

For example, if your WHERE clause is

WHERE tickets.date_created = %PAST_24_HOURS%

it will match all the tickets created within the 24 hours before you run the query.

Note that you use = (equals sign) with placeholders, not < or > as you do with absolute dates/times.

Placeholders use your timezone, as set in your agent account Preferences.

For example, if you run a query to find all the tickets created %TODAY%, it will match all the tickets since the current day began in your timezone.

See List of date placeholders for a full list of the available placeholders.

If there isn’t a placeholder for the interval you need, you can use the SQL NOW function to get the current date, then subtract an INTERVAL. See Ticket backlog with a custom timeframe [old] for an example of how to do this.

Authors list

First published: 16/03/2017

Last updated: Mar 16, 2017 by Colin Dunn