mysql: getting a grip on dates, times, and timezones
Tags: mysql
Published: 2023-01-03 17:35:51


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
* <a hame="column types" href="#column-types">column types</a>
* <a hame="timezones and your mysql server" href="#timezones-and-your-mysql-server">timezones and your mysql server</a>
  * <a hame="get named timezones working" href="#get-named-timezones-working">get named timezones working</a>
  * <a hame="find out your mysql timezone" href="#find-out-your-mysql-timezone">find out your mysql timezone</a>
  * <a hame="set mysql to utc" href="#set-mysql-to-utc">set mysql to utc</a>
* <a hame="timezone conversions" href="#timezone-conversions">timezone-conversions</a>
  * <a hame="getting dates" href="#getting-dates">getting dates</a>
  * <a hame="getting dates for the right timezone" href="#getting-dates-for-the-right-timezone">getting dates for the right timezone</a>
* <a hame="formatting dates and times" href="#formatting-dates-and-times">formatting dates and times</a>
  * <a hame="using prebuilt date and time formats" href="#using-prebuilt-date-and-time-formats">using prebuilt date and time formats</a>
  * <a hame="putting formats and timezones together" href="#putting-formats-and-timezones-together">putting formats and timezones together</a>
* <a hame="some basic datetime math" href="#some-basic-datetime-math">some basic datetime math</a>
* <a hame="getting the difference between dates" href="#getting-the-difference-between-dates">getting the difference between dates</a>
* <a hame="getting data out of datetimes" href="#getting-data-out-of-datetimes">getting data out of datetimes</a>





## 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](https://en.wikipedia.org/wiki/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:

```sql
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:

```bash
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:

```bash
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:

```sql
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:

```sql
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.

```bash
sudo systemctl restart mysql.service  
```

now, if we login to mysql and look at our timezones, we see:

```sql
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:

```sql
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:

```sql
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](https://calgarycitizen.com/article/albertans-vote-against-daylight-saving-time/). 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.

```sql
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](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List). 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.  

```sql
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.

```sql
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.

```sql
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](https://en.wikipedia.org/wiki/Greenwich_Mean_Time)).

the difference in the time between timezones can translate easily to a difference between dates. observe:

```sql
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:

```sql
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:

```sql
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](https://www.w3resource.com/mysql/date-and-time-functions/mysql-date_format-function.php). 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://www.javatpoint.com/mysql-interval). 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:

```sql
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:

```sql
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: `DATE`s, `DATETIME`s, `TIMESTAMP`s; 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:

```sql
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()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date)
* [DAYNAME()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayname)
* [DAYOFMONTH()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofmonth)
* [DAYOFWEEK()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofweek)
* [DAYOFYEAR()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofyear)
* [EXTRACT()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_extract)
* [HOUR()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_hour)
* [LAST_DAY](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day)
* [MICROSECOND()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_microsecond)
* [MINUTE()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_minute)
* [MONTH()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_month)
* [MONTHNAME()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_monthname)
* [QUARTER()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_quarter)
* [SECOND()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_second)
* [TIME()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_time)
* [WEEK()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week)
* [WEEKDAY()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_weekday)
* [WEEKOFYEAR()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_weekofyear)
* [YEAR()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_year)
* [YEARWEEK()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_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.