Прескочи към главното съдържание

DATE_FORMAT

в List of Functions
Списък с автори
Публикувани: 17.07.2019 г.|Последно обновено: 3.11.2022 г.

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
copy

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
copy

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
copy

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.

ПолезноНе беше полезно
следваща страницаDPQL_ALIAS
предишна страницаDPQL_MATRIX

Моля, логнете се или се регистрирайте, за да оставите коментар.