DATE_FORMAT

List of functions

By default when you reference a date field in Deskpro (eg tickets.date_created) the system will use the Full date and time format (as specified in the system settings area in admin).

You can use DATE_FORMAT to reference different formats though.

This can be useful to change how a date will be displayed on a report.

It can also be helpful in structuring our data output as well.

Example

I want to create a report counting the number of tickets created by my customers grouped by date.

If I try the following:

SELECT DPQL_COUNT()
FROM tickets
GROUP BY tickets.date_created
ORDER BY tickets.date_created

It outputs the below:

format-7.png

This is grouping by date but because the date format used is to the minute the only tickets grouped together will be those created in the same minute.

It would be more sensible to group by month for example.

To do this we could use the query below:

SELECT DPQL_COUNT()
FROM tickets
GROUP BY DATE_FORMAT(tickets.date_created, '%Y-%M')
ORDER BY tickets.date_created

In the GROUP BY field here we're using DATE_FORMAT then using tickets.date_created in the bracket to specify the field and then '%Y-%M' to specify the format (Year and Month).

This will output the below:

format-3.png

We can play around with this though so we can view the data in different ways.

For example I could change the format specified to the below:

SELECT DPQL_COUNT()
FROM tickets
GROUP BY DATE_FORMAT(tickets.date_created, '%m-%Y')
ORDER BY tickets.date_created

To output the month as :

format-8.png

You can also amend the column name using DPQL_ALIAS.

Further Reading

www.w3schools.com has a full list of the formatting opitons on the following page: https://www.w3schools.com/sql/func_mysql_date_format.asp

There's also some examples you can try in their sandbox.

Published: 17/07/2019

Last updated: 26/07/2019