dealing with dates, times and, espescially, timezones, is messy and zero fun. every serious programming language has a complex and powerful library to handle these problems for us, and we're grateful for that.
however, sometimes, we need to modify or compare dates and times directly in our mysql queries: a group-by on days of the week, or calculating age brackets by birthdate, or getting overdue invoices on a 21-day billing cycle.
fortunately, mysql has a rich and powerful set of functions and constructs for handling dates and times. in this post, we're going to do a survey mysql's date and time abilities and, by the end, have the skills to build the complex queries we might need.
the flyover
this walkthrough is going to cover a fair amount of ground. by the end of it we should be able to:
- have an understanding of mysql's different date and time column types
- figure out our server's timezone and set it to UTC
- convert datetime values to different timezones
- format our dates and times
- do some basic date math (which we can expand to more complex date math)
- get the difference between two dates
- extract the date data we need from dates and times
table of contents
- column types
- timezones and your mysql server
- timezone-conversions
- formatting dates and times
- some basic datetime math
- getting the difference between dates
- getting data out of datetimes
column types
mysql has three main column types for date and time. although all the examples in the post are going to be using DATETIME, it's good to have a grip on the differences between the different formats:
DATE this is just the date with no time component. the default format is YYYY-MM-DD (the way it should be!) and the range of valid dates is '1000-01-01' to '9999-12-31'.
DATETIME is, as the name implies, the date plus the time. the format used is 'YYYY-MM-DD hh:mm:ss', however you can use decimal seconds here as well. need to store the time of a record down to the thousandth of a second? DATETIME's got you covered. the valid range, like with date, is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. DATETIME is always stored and returned as UTC, regardless of what your system's timezone is.
TIMESTAMP looks a lot like DATETIME. there are two big differences, though. first, is the range. since timestamp is basically the unix epoch, the valid range of values is limited to 1970-01-01 00:00:01' to '2038-01-19 03:14:07'. secondly, TIMESTAMP stores your date as UTC but it returns it as your local timezone. this means if you change the timezone of your system time, you may get back different timestamps than you stored. will this happen in real life? probably not. but it's a fun fact that's a great conversation starter at parties.
for the rest of this article, we're going to be using DATETIME. however, all of the functions that we'll be covering will work with TIMESTAMP as well.
timezones and your mysql server
every developer hates timezones. it's enough to make us embrace half-baked ideologies like flat-earthism or swatch internet time. unfortunately, the earth is a sphere, so we're going to need to deal with our mysql timezones.
at the end of this section, we'll have our mysql server set up to be running on UTC and configured so that we can easily do timezone conversions.
get named timezones working
out of the box, mysql functions that take timezone arguments can work using offsets from UTC. so, for instance, if you're on mountain standard time, your offset from UTC would be -07:00. UTC, of course, would be +00:00.
this works, but is clumsy and difficult. we would much rather be able to reference timezones by using their names, for instance MST or America/Edmonton instead of -07:00.
fun fact about offsets: if your mysql is not on UTC currently, you can find out the UTC offset by getting the time difference between NOW(), which is the current time in mysql's timezone, and UTC_TIMESTAMP(), which is the current time in UTC, like so:
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP) as offset;
+-----------+
| offset |
+-----------+
| -07:00:00 |
+-----------+
end of fun fact.
in order to get named timezones to work, we need to seed all those names and UTC offsets (and more!) into mysql.
that data is currently stored in your linux-like operating system's zoneinfo directory. we want to put it into mysql's time_zone table in the mysql database.
fortunately, doing this is a straihtforward copy-paste job:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
if you have trouble running the above command because you sudo mysql to login to mysql as root, you can just wrap the command in a root shell like so:
sudo sh -c 'mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql'
we see here that we call the mysql_tzinfo_to_sql command with the path to our zoneinfo directory. this command takes the directory structure in zoneinfo and turns it into a large sql statement. we then pipe that statement to mysql to do the inserting. job done! we can now reference timezones by name, not just UTC offset.
find out your mysql timezone
if our mysql server isn't set to use UTC, we certainly will want to know exactly which timezone it's using.
this data is stored in two of mysql's system variables. system variables can be selected by prepending @@ to their path. let's get our global and session timezones:
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
this is not particularly useful data. what we want is the name or the offset of the timezone; something like 'MST' or '-07:00'. what we get is the word SYSTEM.
this, of course, is telling us that mysql is using the timezone of the operating system it's running on.
to get the full name of the system's timezone we can check the mysql variable called, aptly enough, system_time_zone. let's select it:
show variables like '%system_time_zone%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| system_time_zone | MST |
+------------------+-------+
put together, these two selects tell us that mysql is using the timezone of the operating system, and the operating system's time zone is MST.
set mysql to utc
it's pretty standard to run all your server software on UTC. and who are we to go against the grain? let's get our mysql server running on UTC.
there are a number of ways to do this, but we're going to focus on setting the timezone in the configuration file my.cnf
first, find your my.cnf file. it's almost certainly at /etc/mysql/my.cnf. what we're looking for is the config section labelled [mysqld].
some mysql installs have the [mysqld] right in the my.cnf file. others have it in a special server config file that is included in my.cnf. on my pop_os system, it's in /etc/mysql/mysql.conf.d/mysqld.cnf. you will need to look around!
once we have the [mysqld] section, at the bottom, we're going to add this configuration value:
default-time-zone = +00:00
this directive is pretty straightforward: we're setting the default timezone of mysql to UTC.
after saving the file, we restart the mysql daemon.
sudo systemctl restart mysql.service
now, if we login to mysql and look at our timezones, we see:
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +00:00 | +00:00 |
+--------------------+---------------------+
and if we select NOW() (current time in mysql) and UTC_TIMESTAMP() (current time in UTC), voila!, they are the same:
select NOW(), UTC_TIMESTAMP();
+---------------------+---------------------+
| NOW() | UTC_TIMESTAMP() |
+---------------------+---------------------+
| 2022-12-18 23:23:20 | 2022-12-18 23:23:20 |
+---------------------+---------------------+
timezone conversions
now that we've got everything running on UTC, it's time to start messing with timezones in our DATETIME columns.
the way to convert timezones in mysql is with the CONVERT_TZ function. this function takes a datetime or timestamp, and two timezones: the one we are converting from and the one we are converting to. let's look at an example:
SELECT NOW() as datetime_utc, CONVERT_TZ(NOW(), 'UTC', 'MST') AS datetime_mst;
+---------------------+---------------------+
| datetime_utc | datetime_mst |
+---------------------+---------------------+
| 2022-12-18 23:31:15 | 2022-12-18 16:31:15 |
+---------------------+---------------------+
looking at this statement, we can see we're selecting two values. first, we're selecting NOW(). this gives the current time in the mysql server's timezone, which, for us, is UTC.
the second value is more interesting. here, we're using CONVERT_TZ() to convert the value of NOW() to a different timezone. the second argument of CONVERT_TZ() is the timezone we're convering from, and the third argument is the timezone we're converting to. in this example, we're converting from UTC to MST.
it gets even better, though. the timezone MST is not the most descriptive. what about daylight savings, time when it magically switches to MDT? daylight savings time rules are inconsistent and occasionally change (my province in canada had a referendum on permanent DST last year. it was defeated. thankfully)
fortunately, all that info we dumped into mysql from zoneinfo allows us to define our timezone by a reference city. so, instead of saying MST, we would define our timezone as America/Edmonton. defining our timezone by a reference city allows us to automatically incorporate daylight savings time and any other irregularities automatically.
SELECT NOW() as datetime_utc, CONVERT_TZ(NOW(), 'UTC', 'America/Edmonton') AS datetime_mst;
+---------------------+---------------------+
| datetime_utc | datetime_mst |
+---------------------+---------------------+
| 2022-12-18 23:31:15 | 2022-12-18 16:31:15 |
+---------------------+---------------------+
if you're so inclined, you can peruse the full list of zoneinfo cities. there are a lot. this data set was created and maintained by dave rolsky. if you see him, you should buy him a coffee maybe.
knowing our timezone
of course, for CONVERT_TZ() to work, we need to hardcode our 'from' timezone. it would be much better if we could access that data from the system. so let's do that instead.
back when we were configuring mysql to use UTC as it's default timezone, we selected the time zone system variables to inspect what timezone our server was on. we can use those system variables, in this case GLOBAL.time_zone, in our query.
SELECT NOW() AS datetime_utc, CONVERT_TZ(NOW(), @@GLOBAL.time_zone, 'MST') AS datetime_mst;
now instead of us needing to know the time zone of the mysql server, we can just ask mysql itself.
of course, we aren't restricted to just using CONVERT_TZ() on NOW(). we can apply it to a column we select, as well. ie.
SELECT CONVERT_TZ(my_date_column, @@GLOBAL.time_zone, 'MST') AS datetime_mst FROM my_table;
fun fact about NOW(): all of these mysql functions are just synonyms for NOW():
CURRENT_TIMESTAMP()LOCAL_TIME()LOCAL_TIMESTAMP()
end of fun fact
getting dates
we've been working with datetimes so far, but there will certainly be times when we want just the date.
we can easily cast a DATETIME column to just a DATE with the well-named function DATE(). here, for instance, we select the registered_at datetime column twice, once as its regular datetime self, and once as just its DATE component.
SELECT DATE(registered_at), registered_at FROM mytable;
+---------------------+------------------------+
| date(registered_at) | registered_at |
+---------------------+------------------------+
| 2022-12-19 | 2022-12-19 05:59:18 |
+---------------------+------------------------+
getting dates for the right timezone
this is a good and handy feature. however, we should pay attention to timezones (again) and the expectations of the user when it comes to the definition of a date.
if a user who lives in edmonton, alberta is looking for a list of all the people in edmonton, alberta who registered for their event on saturday, they probably mean saturday in edmonton, alberta, not greenwich, england (the city on which UTC time is centered).
the difference in the time between timezones can translate easily to a difference between dates. observe:
SELECT registered_at,
DATE(registered_at)
AS registered_at_utc,
DATE(CONVERT_TZ(registered_at_utc, @@GLOBAL.time_zone, 'America/Edmonton'))
AS registered_at_mst
FROM mytable;
+---------------------+-------------------+-------------------+
| registered_at | registered_at_utc | registered_at_mst |
+---------------------+-------------------+-------------------+
| 2022-12-19 05:59:18 | 2022-12-19 | 2022-12-18 |
+---------------------+-------------------+-------------------+
here we see that 5:59 am on the 19th in UTC is the 18th in the MST timezone; a different date. not great.
the solution, of course, is to do a timezone conversion on a datetime before casting it to a date used in a WHERE clause.
for instance, the user in edmonton, alberta who wants to know how many people registered on saturday in edmonton, alberta would do something like:
SELECT count(*) AS reg_total
FROM mytable
WHERE DATE(CONVERT_TZ(registered_at, @@GLOBAL.time_zone, 'America/Edmonton')) = '2022-12-17'
here, all we did was take the registered_at column, cast it from mysql's default timezone to edmonton's timezone, convert it to a date and then compare it to the date for saturday.
formatting dates and times
every popular programming language has some methodology for formatting dates and times. in mysql, it's DATE_FORMAT(). let's use it to format a date:
SELECT DATE_FORMAT(mydatetime, '%a, %b %D %l:%S %p')
FROM mytable;
+-----------------------------------------------+
| DATE_FORMAT(mydatetime, '%a, %b %D %l:%S %p') |
+-----------------------------------------------+
| Mon, Dec 19th 5:18 AM |
+-----------------------------------------------+
the first argument to DATE_FORMAT() is the column containing the datetime or timestamp and the second argument is a string defining the format.
components of the date format are made with single letter codes preceded by a % character. so, for instance, %b formats the day of the month as 19th, and %p gives us either AM or PM. if you've done date formatting in other languages, this is probably familiar to you.
its worthwhile to look at the full list of formatting codes. there are a lot and some of them will certainly be different than those in other languages you may have used.
using prebuilt date and time formats
mysql makes date formatting a little bit less tedious by offering some pre-built formats with the GET_FORMAT() function. this function returns a formatting string you can use with DATE_FORMAT(). here's an example of one of those pre-built formats:
SELECT GET_FORMAT(DATE, 'usa');
+-------------------------+
| GET_FORMAT(DATE, 'usa') |
+-------------------------+
| %m.%d.%Y |
+-------------------------+
GET_FORMAT() takes two arguments. the first is what we want to format. the options are DATE, TIME and DATETIME. the second argument is the region. currently, we have usa, eur, jis and iso. the basic definition of GET_FORMAT() looks like:
GET_FORMAT(DATE|TIME|DATETIME, 'usa'|'eur'|'jis'|'iso')
putting this together with DATE_FORMAT() is pretty straightforward. here we are formatting our datetime column to an american-style date:
SELECT DATE_FORMAT(mydatetime, GET_FORMAT(DATE, 'usa')) FROM mytable;
+--------------------------------------------------+
| DATE_FORMAT(mydatetime, GET_FORMAT(DATE, 'usa')) |
+--------------------------------------------------+
| 12.19.2022 |
+--------------------------------------------------+
if you need to format dates or datetimes in standardized ways, GET_FORMAT() is easier and better than manually writing your own format.
putting formats and timezones together
let's put it all together by selecting the date in MST formatted the american way:
SELECT DATE_FORMAT(
CONVERT_TZ(mydatetime, @@GLOBAL.time_zone, 'America/Edmonton'),
GET_FORMAT(DATE, 'usa')
) AS date_mst_usa_format
FROM mytable;
+---------------------+
| date_mst_usa_format |
+---------------------+
| 12.18.2022 |
+---------------------+
this looks a bit more complicated, but all we're doing here is combining stuff we already know. we convert the timezone of our datetime column mydatetime to America/Edmonton, then we format that new datetime to the american date format, using the format provided by GET_FORMAT().
some basic datetime math
being able to do date math, adding or subtracting days or hours or months, is pretty useful. software clients always want reports for things like "all the users that registered in the week starting may 10th" or whatever.
fortunately, we can do some pretty powerful date and time math in mysql using just arithmatic operators. for instance:
mysql> SELECT NOW(), NOW() + INTERVAL 1 hour;
+---------------------+-------------------------+
| NOW() | NOW() + INTERVAL 1 hour |
+---------------------+-------------------------+
| 2022-12-19 17:15:44 | 2022-12-19 18:15:44 |
+---------------------+-------------------------+
here we added to the current datetime an INTERVAL of one hour. the result is a datetime one hour in the future. exactly what we want!
the INTERVAL expression here is a bit counterintuitive. the structure is:
INTERVAL <amount> <unit>
the 'amount' is a number, either positive or negative. you can use a float here if you wish, ie 1.3, but it will be rounded to an integer.
the 'unit' is one of the units from a set list. popular ones are 'hour', 'day', 'year' and 'month'. it's pretty intuitive.
since INTERVAL expressions are just values, we can add as many as we like to a date or datetime. for instance, if we want a date one year and one hour in the future, we can do:
mysql> SELECT NOW(), NOW() + INTERVAL 1 year + INTERVAL 1 hour;
+---------------------+-------------------------------------------+
| NOW() | NOW() + INTERVAL 1 year + INTERVAL 1 hour |
+---------------------+-------------------------------------------+
| 2022-12-19 17:15:44 | 2023-12-19 18:15:44 |
+---------------------+-------------------------------------------+
with this, we can start to create any relative date we want.
getting the difference between dates
the other major component of date math is getting the difference between two dates. for this, we'll be using the mysql function TIMESTAMPDIFF().
TIMESTAMPDIFF() takes three arguments: the two dates we want to compare, and the units we want the difference in. an example:
SELECT TIMESTAMPDIFF(day, NOW(), '2021-09-01 12:34:56');
+--------------------------------------------------+
| TIMESTAMPDIFF(day, NOW(), '2021-09-01 12:34:56') |
+--------------------------------------------------+
| -474 |
+--------------------------------------------------+
we can see here that we are comparing two datetimes: NOW() and a date created from a string literal. the first argument to TIMESTAMPDIFF is the units we want the difference in. when we run this query, we see that our date '2021-09-01 12:34:56' was 474 days ago (as of this writing).
for the units argument we can use:
- microsecond
- second
- minute
- hour
- day
- week
- month
- quarter
- year
like all datetime functions in mysql, the dates we pass to functions can be either: DATEs, DATETIMEs, TIMESTAMPs; results of functions that return datetimes, like NOW(); or even literal strings that describe dates.
getting data out of datetimes
datetimes are made up of various components: the year, the hour and so on. we will definitely want to be able to extract these from datetime columns. let's start with getting the name of the day:
SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Tuesday |
+----------------+
it may not be tuesday when you read this, but it certainly was when i wrote it. the DAYNAME() function takes one argument, the datetime or timestamp value, and returns the name of the day.
there are a lot of mysql functions to get components out of a date or datetime. if you're ambitious, here's a list you can pursue:
- DATE()
- DAYNAME()
- DAYOFMONTH()
- DAYOFWEEK()
- DAYOFYEAR()
- EXTRACT()
- HOUR()
- LAST_DAY
- MICROSECOND()
- MINUTE()
- MONTH()
- MONTHNAME()
- QUARTER()
- SECOND()
- TIME()
- WEEK()
- WEEKDAY()
- WEEKOFYEAR()
- YEAR()
- YEARWEEK()
extracting data from dates is useful stuff. want to get all the registrations that happend on the weekend, or a list of orders placed after midnight? mysql has a function you can put in your where clause.
conclusion
mysql has a lot of functions and constructs for dealing with dates and times and, like all things, mastering them requires practice and experimentation.
however, once we get the hang of managing mysql's date and time functions, we can begin combining them to do powerful things. if your client wants to show their investors all the users who made orders after midnight local time on weekend nights in may, you can put that together, and look like a hero.
Top comments (3)
Created an account on dev.to just to Thank you. Very comprehensive and clear 🚀🚀
Thank you, I appreciate your article!
it's quite strange to see the lowercase article but the content is top notch. thanks :)