MySQL provides us with a wide range of functions to deal with dates, maths calculations, string manipulation etc. However, MySQL lacks some functions that are available in other DBMSs. The date_trunc function is one of the missing features of MySQL which is available in PostgreSQL DBMS.
In this tutorial, we will see what are some alternatives we can use to fill the gap in the date_trunc function in MySQL. So, let’s get started!
What is the date_trunc Function?
The date_trunc function is one of the best features available in the PostgreSQL database to deal with dates. This function can be used to round the timestamps to the required interval, say year, day, hours or seconds.
In MySQL, there is no such function available to round the date and time to the interval you want. However, there are plenty of other functions available that we can use to get a similar result or output.
According to StackOverflow users, MySQL is poor in dealing with date ranges. However, that’s not the problem now. We will see some good alternatives of the date_trunc function one by one.
Alternative 1 – MySQL EXTRACT() Function
As the name suggests, the function extracts the required part from the given timestamp. Let’s see its syntax first and then we will see some examples.
EXTRACT(unit FROM value)Code language: SQL (Structured Query Language) (sql)Here,
The unit denotes the unit argument such as MINUTE, HOUR, SECOND etc. You can see all unit values and the expected output format from the MySQL temporal intervals section.
Let’s take some examples now.
SELECT EXTRACT(YEAR FROM '2022-10-23 02:09:45') AS extractYear;
SELECT EXTRACT(HOUR_MINUTE FROM '2022-10-23 02:09:45') AS extractHourMinute;
SELECT EXTRACT(YEAR_MONTH FROM '2022-10-23 02:09:45') AS extractYearMonth;
Code language: SQL (Structured Query Language) (sql)Here,
In the first query, we are extracting the year from the specified date-time value. Therefore, we should get 2022 in the output.
In the second query, we are extracting hour minute value. You can go through the above reference article of MySQL to understand the output format.
In the third query, we are extracting year month value. So, we should get 2022 and 10 combined in the output.
As you can see in the output, we got the result in the correct format.
Alternative 2 – DATE_FORMAT() Function
The DATE_FORMAT() function is very similar to the extract function. However, instead of just extracting the unit part, it formats the given date to the specified format string.
Let’s see its syntax.
DATE_FORMAT(date,format)Code language: SQL (Structured Query Language) (sql)Here, the first argument is the date value. Whereas, the second argument is the format string. The format string is nothing but a set of specifiers.
You can check this table on the MySQL blog about specifiers and their descriptions. Note that, percent (%) sign is required before each format specifier character.
Let’s take some examples now.
SELECT DATE_FORMAT('2022-10-23 02:09:45', "%Y-%m-%d") AS getDate;
SELECT DATE_FORMAT('2022-10-23 02:09:45', "%d-%m-%Y") AS getDMY;Code language: SQL (Structured Query Language) (sql)Here,
In the first query, we are formatting the date-time value to only date and skipping the time.
In the second query, we are formatting the date-time value to DD:MM:YYYY format. The MMDDYYYY format is mostly used in the united states. Whereas the DDMMYYYY is used most elsewhere in the countries such as India, China, Korea, etc.
As you can see in the output, we got the correct format of the dates as expected.
In the DATE_FORMAT function, you can use constant values as well. These can be helpful in your applications. For example, if you want to get the number of sign-ups between 1st January 2022 to 31st January 2022. In this case, you can simply use the constant 01 and 31 in the date_format function. Let’s see.
SELECT DATE_FORMAT('2022-10-23 02:09:45', "%Y-%m-01") AS constantDate;
SELECT DATE_FORMAT('2022-10-23 02:09:45', "%Y-%m-31") AS constantDate;Code language: SQL (Structured Query Language) (sql)Look carefully, we have the date value available in our time stamp which is the 23rd. However, we are replacing it with the constant date in both queries. Let’s see the output if it really works.
As you can see, the given date values are replaced with our constant values. Note that, you don’t need the percent (%) sign before the constant values.
Summary
In this tutorial, we have learned some good alternatives to the date_trunc function. Note that, there are a lot of other functions available in MySQL that you can use according to your use case such as STR_TO_DATE(), TIME_FORMAT() etc. You can see all the available date-time functions on the MySQL dev blog. Don’t forget to try as many of them as possible so that you will quickly understand which function is useful in your application.
References
MySQL official documentation on the EXTRACT() function.
List of Date-Time functions available in MySQL.
Temporal Intervals in MySQL.
Date-time Format specifiers in MySQL.