Moving away from the DATE_TRUNC() SQL Clauses

As I am making my fork of FusionPBX, CoolPBX more MariaDB/MySQL compatible, I have found that the DATE_TRUNC() function doesn't work.

After googling, I found that some people suggest that the PostgreSQL DATE_TRUNC() function to MariaDB/MySQL is EXTRACT(), but it is not. For example, in PostgreSQL:

SELECT DATE_TRUNC('hour', NOW()) + (INTERVAL '1 hour') AS h;

It will return something like 2024-08-09 09:00:00-04. While  in MariaDB:

SELECT EXTRACT(hour FROM NOW() + INTERVAL 1 HOUR AS h;

It will return only 9.

Translating DATE_TRUNC() to DATE_FORMAT()

Fortunately, we have DATE_FORMAT(). This function is smart enough to not only format a date but to return it in a type that allows us to manipulate it. The equivalent to the last example using DATE_FORMAT would be:

SELECT DATE_FORMAT(NOW() + interval 1 hour, '%Y-%m-%d %H:00:00' ) AS h;

Please note that we are filling with zeroes. From MariaDB 11.3, you can add %z to fill the GMT offset.

Good luck!