Dates and times are important, they help keep things organized, and are an integral aspect of any software operation.
Efficiently working with them within the database can sometimes seem confusing, whether it’s working across the various time zones, adding / subtracting dates, and other operations.
Learn the various MySQL functions available to easily handle and manage dates / times within your database.
Working With Time Zones
To help keep things standardized, you should only ever work with dates / times in UTC time zone. Every time you establish a connection to the MySQL database, you should switch the time zone to UTC, which can be done with the following SQL statement:
SET TIME_ZONE = '+0:00'Since all dates will now be saved in UTC, you always know what you’re working with, making things more simplistic and straight forward.
When necessary you can easily convert the time zone of any datetime / timestamp value with the handy CONVERT_TZ() MySQL function. You need to know the offset first, for example, PST on the west coast of North America is UTC -08:00, so you could use:
SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');This results in 2021-02-04 13:47:23 which is exactly correct. The three arguments passed to CONVERT_TZ() are first the datetime / timestamp you’re starting with (use now() for current time), the second will always be ‘+0:00’ since all dates are forced to UTC in the database, and the last is the offset we wish to convert the date to.
Add / Subtract Dates
Many times you need to add to or subtract from dates, such as if you need to retrieve records from a week ago, or schedule something a month from now.
Thankfully MySQL has the excellent DATE_ADD() and DATE_SUB() functions making this task extremely easy. For example, you can subtract two weeks from the current date with the SQL statement:
SELECT DATE_SUB(now(), interval 2 week);If instead you wanted to add three days to an existing timestamp, you would use:
SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);Both functions work the same, the first argument is the timestamp you are starting with, and the second argument is the interval to add or subtract. The second argument is always formatted the same starting with the word interval followed by a numeric value and the interval itself, which can be any of the following: second, minute, hour, day, week, month, quarter, year.
For another example, if you wanted to retrieve all logins that occurred in the past 34 minutes you could use a SQL statement such as:
SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);As you can see, this would retrieve all records from the logins table with a login date greater than the current time minus 45 minutes, or in other words, the past 45 minutes.
Get Difference Between Dates
Sometimes you need to get how much time has elapsed between two dates. You can easily get the number of days between two different dates with the DATEDIFF function, such as the below SQL statement:
SELECT DATEDIFF(now(), '2020-12-15');The DATEDIFF function takes two arguments, both of which are date / time stamps and gives the number of days between them. The above example will show the number of days elapsed from December 15th, 2020 until today.
To get the number of seconds between two dates, the TO_SECONDS() function can come in handy, for example:
SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');This will result in the number of seconds between the two dates provided.
Extract Segments From Dates
There are various MySQL functions that allow you to easily extract specific segments from dates, such as if you only wanted the month, the day of the year, or the hour. Here’s a few examples of such functions:
SELECT MONTH('2021-02-11 15:27:52');SELECT HOUR(now());SELECT DAYOFYEAR('2021-07-15 12:00:00');The above SQL statements would result in 02, the current hour, and 196 as September 15th is the 196th day of the year. Here’s a list of all date extraction functions available, each only taking one argument, the date being extracted from:
- SECOND()
- MINUTE()
- HOUR()
- DAY()
- WEEK() - Number 0 - 52 defining the week within the year.
- MONTH()
- QUARTER() - Number 1 - 4 defining the quarter of the year.
- YEAR()
- DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
- LAST_DAY() - The last day in the given month.
- DATE() - The date in YYYY-MM-DD format without the time.
- TIME() The time in HH:II:SS format without the date.
- TO_DAYS() - The number of days since A.D. 0.
- TO_SECONDS() - The number of seconds since A.D. 0.
- UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)For example, if maybe you only wanted to retrieve the month and year which all users were created, you may use a SQL statement such as:
SELECT id, MONTH(created_at), YEAR(created_at) FROM users;This would retrieve all records within the users table and show the id#, month and year each user was created in.
Grouping Records by Date Period
One excellent use of date functions is the ability to group records by date period using GROUP BY within your SQL statements. For example, maybe you wish to pull the total amount of all orders in 2020 grouped by month. You could use a SQL statement such as:
SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);This would retrieve all orders placed in the year 2020, group them by the month they were created, and return 12 records showing the total amount ordered each month of the year.
Please note, for better index performance, it’s always best to avoid using date functions such as YEAR() within the WHERE clause of SQL statements, and instead use the BETWEEN operator as shown in the above example.
Never Be Confused With Dates Again
Using the above knowledge you can now efficiently work with, translate and perform operations in dates and times in a wide array of use cases.
Remember to always use UTC when working with dates for simplicity, and utilize the above tips to efficiently manage dates within your software, whether it’s to complete simple calculations or easily pull reports grouped by date periods.
If you’re somewhat new to SQL, make sure to check out these essential SQL commands to help improve your SQL use.