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.
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:
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:
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 :
You can also amend the column name using DPQL_ALIAS.
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.