Make Your Life Easier with MySQL’s Date Functions
Date functions in MySQL are a set of functions that allow you to manipulate and extract date and time values from expressions, columns, or literals. Date functions help perform various operations on dates, such as finding the difference between two dates, adding or subtracting intervals from a date, formatting a date in a specific way, or converting a date from one format to another.
Some of the date functions in MySQL are:
- CURDATE(): This function returns the current date as a value in the ‘YYYY-MM-DD’ format.
- CURTIME(): This function returns the current time as a value in ‘HH:MM:SS’ format.
- NOW(): This function returns the current date and time as a value in the ‘YYYY-MM-DD HH:MM:SS’ format.
- DATE(): This function extracts the date part of a date or datetime expression and returns it as a value in ‘YYYY-MM-DD’ format.
- TIME(): This function extracts the time part of a date or datetime expression and returns it as a value in ‘HH:MM:SS’ format.
- DATE_ADD(): This function adds a specified time interval to a date or datetime expression and returns the result as a value in ‘YYYY-MM-DD HH:MM:SS’ format.
- DATE_SUB(): This function subtracts a specified time interval from a date or datetime expression and returns the result as a value in ‘YYYY-MM-DD HH:MM:SS’ format.
- DATEDIFF(): This function returns the days between two date or datetime expressions.
- DATE_FORMAT(): This function formats a date or datetime expression according to a specified format string and returns the result as a string.
- STR_TO_DATE(): This function converts a string to a date or datetime value according to a specified format string.
Let’s see some examples of how these functions can be used.
Example 1: Suppose we have a table called orders that stores information about orders placed by customers. The table has the following columns:
- order_id: an integer that uniquely identifies an order
- customer_id: an integer that references the id of a customer
- product_id: an integer that references the id of a product
- quantity: an integer that indicates how many units of the product were ordered
- order_date: a datetime that shows when the order was placed
The table contains some sample data, as shown below:
| order_id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 101 | 1 | 2 | 2021-03-01 10:15:32 |
| 2 | 102 | 2 | 1 | 2021-03-02 11:20:45 |
| 3 | 103 | 3 | 3 | 2021-03-03 12:25:58 |
| 4 | 104 | 4 | 4 | 2021-03-04 13:31:11 |
| 5 | 105 | 5 | 5 | 2021-03-05 14:36:24 |
If we want to find out how many orders were placed on each day, we can use the DATE() function to extract the date part of the order_date column and group by it. The query would look like this:
SELECT DATE(order_date) AS order_day, COUNT(*) AS order_count
FROM orders
GROUP BY order_day;The output would look like this:
| order_day | order_count |
|---|---|
| 2021-03-01 | 1 |
| 2021-03-02 | 1 |
| 2021-03-03 | 1 |
| 2021-03-04 | 1 |
| 2021-03-05 | 1 |