When creating a query, you'll often want to base it on dates.
For example, you may want to create a query for Tickets created in a specific month.
To do this we can use the DATE function which allows us to compare fields with date values to our specified dates.
Date Formats
In FQL you can specify dates in three different ways:
Absolute Dates
You can simply use the YYYY-MM-DD format to specify a date.
If you wanted to see every ticket created a specific month so far (e.g., November 2021) you could use:
ticket.date_created >= DATE("2021-10-31") copyShortcuts
Shortcuts are an easy to read and write format for dates hard coded into the system.
The options available are as follows:
Shortcut |
|---|
today |
yesterday |
thisWeek |
lastWeek |
thisMonth |
lastMonth |
thisYear |
lastYear |
1h |
2h |
6h |
12h |
If again we wanted see every Ticket created this month in our query we could use:
ticket.date_created >= DATE("thisMonth") copyOperations
Operations are an easy way to reference relative dates in the past or in the future.
They helpful for creating rolling query e.g. Tickets created in the last 30 days.
The units available are:
day/days
week/weeks
month/months
year/years
You can use positive and negative integers alongside them so the format for an operation would be written as +1 day or -3 weeks.
If you wanted to create a query that shows tickets created in the last 30 days you could use the following
ticket.date_created >= DATE("-30days") copyDate Ranges
You can read a full list of date operators in the FQL Operators guide.
For dates a particularly useful operator to be aware of is the BETWEEN operator as this allows us to construct date ranges.
You can use this with all three date formats but it's perhaps most useful to use with absolute dates.
If you'd like to generate a list of all tickets created in November 2021 you would just need to specify the dates those days fall between.
ticket.date_created BETWEEN DATE('2021-11-01') AND DATE('2021-12-01') copyNote, tickets from the after date (so in this instance 2021-11-01) will be included in the results but those in the before date after the AND (2021-12-01) will not be.
Time Zones
When using a date in a query it is also possible to specify the time zone you want that date to be based on.
The time zones available are the same as those used throughout the rest of the system so check the full list in your Agent preferences if you want to see what is available.
You might want to view all Tickets created in the last 3 days but base it on the LA time zone to be sure your count is accurate:
ticket.date_created `>= DATE("-3 days", "America/Los_Angeles") copy
Please log in or register to submit a comment.