Date information tracking and storage is integral to companies, services, and applications. Imagine if your employer did not keep up with how many hours you worked? Or which days? Neither party involved could benefit. I’m confident such a company would have vacancies abound. This blog post will visit many of the available date functions offered in MySQL, to assist managing this all-important data type.
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.3 LTS (Xenial Xerus) and MySQL 5.7.21 for these exercises.
Photo by Curtis MacNewton on Unsplash
Functions Covered
Covering every MySQL date function in one blog post would be a tall order at best. Instead, I will look at common functions you’re likely to encounter and use. The MySQL Date and Time Functions documentation has a wealth of information and list those functions provided, so be sure and visit that resource.
Here are those we will visit, in no particular order.
DATEDIFF()CURDATE()DAYOFWEEK()DAYNAME()MONTH()MONTHNAME()STR_TO_DATE()NOW()
Perhaps a more plausible example opposed to corporations neglecting days, hours, and time would be a simple friends table with a birthday column.
Let’s get a description of the table we are targeting for this post:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| friend_id | int(11) | NO | PRI | NULL | auto_increment |
| f_first_name | varchar(30) | YES | | NULL | |
| f_last_name | varchar(30) | YES | | NULL | |
| f_cell_phone | char(12) | YES | | NULL | |
| f_age | int(11) | YES | | NULL | |
| f_bday | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
How Many Days Are Left Until…?
This question surely runs through our heads at least weekly. For me, it’s more like daily.
MySQL solves this type of question with the DATEDIFF() function.
DATEDIFF() subtracts two date values and returns the number of days between them.
Let’s find out how many days are between the current date (for the system used in this example at the time of writing) and the f_bday column.
-> FROM friends
-> LIMIT 5;
+-----------------+
| days_difference |
+-----------------+
| NULL |
| 13375 |
| 16189 |
| 14289 |
| 10382 |
+-----------------+
5 rows in set (0.00 sec)
That’s a lot of days.
How is that useful?
Probably not much at all like this.
But, we can perform math on the result, like dividing by 365, to get an idea of how old one of our friends is.
-> FROM friends
-> LIMIT 5;
+-------+
| years |
+-------+
| NULL |
| 37 |
| 45 |
| 39 |
| 29 |
+-------+
5 rows in set (0.00 sec)
∗ Note: NULL is returned since that friend does not have a birthday entered for their column. The ROUND() math function is used to round off the result to a numeric whole number.
Or, we can SELECT additional columns and ALIAS them for better understanding:
f_bday AS friends_bday,
DATEDIFF(NOW(), f_bday) AS days_difference
FROM friends
ORDER BY f_bday IS NULL, f_bday ASC
LIMIT 5;
+-------------+--------------+-----------------+
| todays_date | friends_bday | days_difference |
+-------------+--------------+-----------------+
| 2018-03-01 | 1951-11-04 | 24224 |
| 2018-03-01 | 1953-04-04 | 23707 |
| 2018-03-01 | 1953-07-04 | 23616 |
| 2018-03-01 | 1956-12-05 | 22366 |
| 2018-03-01 | 1973-08-03 | 16281 |
+-------------+--------------+-----------------+
5 rows in set (0.00 sec)
FYI:
I use the ORDER BY clause to merely push those NULL values to the bottom of the results set so we don’t have to see them.
Something interesting to highlight quickly in that last query.
Both CURDATE() and NOW() are similar, but do have a subtle difference.
You can see it with these query’s:
+------------+
| CURDATE() |
+------------+
| 2018-03-01 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2018-03-01 15:49:55 |
+---------------------+
1 row in set (0.01 sec)
NOW() includes the current time stamp, where CURDATE() just returns the current date. Now you know.
We Never Forget Birthdays Right?
Suppose we want to know the day of the week a friends’ birthday is on. Perhaps we look at the friends table every week and get an idea of who has a birthday, if any, and make a note of what day it is.
We wouldn’t be a very good friend forgetting birthdays and all.
The DAYOFWEEK() function returns a numeric value for the date value parameter.
Those numbers represent:
1 = Sunday,
2 = Monday,
etc…
Great! Now we know what all that means. But, perhaps something a bit easier on the eyes at a glance would be better? With an actual weekday name instead of deciphering the magic number code?
We can put a CASE expression to use here.
-> CASE
-> WHEN DAYOFWEEK(f_bday) = '1' THEN 'Sunday'
-> WHEN DAYOFWEEK(f_bday) = '2' THEN 'Monday'
-> WHEN DAYOFWEEK(f_bday) = '3' THEN 'Tuesday'
-> WHEN DAYOFWEEK(f_bday) = '4' THEN 'Wednesday'
-> WHEN DAYOFWEEK(f_bday) = '5' THEN 'Thursday'
-> WHEN DAYOFWEEK(f_bday) = '6' THEN 'Friday'
-> WHEN DAYOFWEEK(f_bday) = '7' THEN 'Saturday'
-> ELSE 'not a day of week'
-> END AS day_of_week
-> FROM friends
-> LIMIT 10;
+--------------+-------------+------------+-------------------+
| f_first_name | f_last_name | f_bday | day_of_week |
+--------------+-------------+------------+-------------------+
| John | Tom | NULL | not a day of week |
| John | Roberts | 1981-04-17 | Friday |
| Billy | Bogus | 1973-08-03 | Friday |
| Jennifer | Timie | 1978-10-16 | Monday |
| Mark | Harmon | 1989-06-27 | Tuesday |
| Jeremy | Hall | 1977-02-07 | Monday |
| Josh | Remey | 1977-03-17 | Thursday |
| Jenny | Marcum | 1980-09-27 | Saturday |
| Mike | Comb | 1987-10-05 | Monday |
| Thurmon | Docker | 1992-08-19 | Wednesday |
+--------------+-------------+------------+-------------------+
10 rows in set (0.00 sec)
Oh, nice! That works perfectly.
But that’s kind of ‘the long way around’ for retrieving the name for a day of the week huh?
MySQL has a DAYNAME() function suited for this.
Simply feed it a date value and you’re golden.
Here you are:
-> FROM friends
-> LIMIT 10;
+--------------+-------------+-----------------+
| f_first_name | f_last_name | DAYNAME(f_bday) |
+--------------+-------------+-----------------+
| John | Tom | NULL |
| John | Roberts | Friday |
| Billy | Bogus | Friday |
| Jennifer | Timie | Monday |
| Mark | Harmon | Tuesday |
| Jeremy | Hall | Monday |
| Josh | Remey | Thursday |
| Jenny | Marcum | Saturday |
| Mike | Comb | Monday |
| Thurmon | Docker | Wednesday |
+--------------+-------------+-----------------+
10 rows in set (0.01 sec)
Not quite as involved like the previous query eh?
Another Busy Month
The MONTH() function is used to extract the Month number value from a supplied date value. As in 1 meaning (January) and 12 for (December) with everything else in between.
-> FROM friends
-> GROUP BY 1
-> ORDER BY 1 ASC;
+-------+---------------------+
| month | number_of_birthdays |
+-------+---------------------+
| NULL | 2 |
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 3 |
| 5 | 2 |
| 6 | 1 |
| 7 | 2 |
| 8 | 3 |
| 9 | 2 |
| 10 | 3 |
| 11 | 5 |
| 12 | 3 |
+-------+---------------------+
13 rows in set (0.00 sec)
In this query, the COUNT() aggregate function returns a count of how many birthdays for each month are present in the table.
Numbers numbers numbers…
Can I just get the name of the month, please?
Sure can.
Employ the MONTHNAME() function, obtaining the actual Months’ name from the passed in date value, versus the Month number via MONTH().
-> FROM friends
-> GROUP BY month;
+-----------+---------------------+
| month | number_of_birthdays |
+-----------+---------------------+
| NULL | 2 |
| April | 3 |
| August | 3 |
| December | 3 |
| February | 1 |
| January | 2 |
| July | 2 |
| June | 1 |
| March | 1 |
| May | 2 |
| November | 5 |
| October | 3 |
| September | 2 |
+-----------+---------------------+
13 rows in set (0.00 sec)
With this information, we can determine how many birthdays we need to attend for a given month.
Let’s see how many in November:
-> FROM friends
-> WHERE MONTHNAME(f_bday) = 'November';
+------------+
| month_name |
+------------+
| 5 |
+------------+
1 row in set (0.00 sec)
November is looking to be a busy month of cake and ice cream.
A New Date
We have made a new friend woot-woot!!
We are more than happy to enter their information into our friends table.
But, the birthday is in a string format like ‘March 12, 1979’.
Hmmm…
No worries, STR_TO_DATE() will take care of it.
Check this out.
+---------------------------------------+
| STR_TO_DATE('May 1, 1974','%M %d,%Y') |
+---------------------------------------+
| 1974-05-01 |
+---------------------------------------+
1 row in set (0.00 sec)
Let’s make sense of the second set of parameters passed to this function. The first is pretty well self-explanatory.
Those cryptic '%M %d,%Y' are specifiers for the format argument of the two.
They mean,
%M– Month name.%d– Number day of the month.%Y– 4 digit number year.
∗ Note: The documentation provides a list of available format specifiers you can peruse to learn what is available, in addition to those used here.
We can use a user-defined session variable (Check out this blog post I wrote on them.) with this information and update the friends table with this new information.
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT STR_TO_DATE('May 1, 1974','%M %d,%Y') INTO @bday;
Query OK, 1 row affected (0.01 sec)
+------------+
| @bday |
+------------+
| 1974-05-01 |
+------------+
1 row in set (0.00 sec)
All loaded up.
Now, utilize this session variable in an INSERT statement:
-> f_age,f_bday)
-> VALUES('Julius','May','390-878-3388',44,@bday);
Query OK, 1 row affected (0.05 sec)
We can confirm with this SELECT query:
-> WHERE f_first_name = 'Julius';
+-----------+--------------+-------------+--------------+-------+------------+
| friend_id | f_first_name | f_last_name | f_cell_phone | f_age | f_bday |
+-----------+--------------+-------------+--------------+-------+------------+
| 31 | Julius | May | 390-878-3388 | 44 | 1974-05-01 |
+-----------+--------------+-------------+--------------+-------+------------+
1 row in set (0.00 sec)
Sweet success!!
Important Dates…
Tracking date information is important to everyone. Make sure and leverage the MySQL Date and Time functions when dealing with date values for extracting details and insight from dates. Visit the official MySQL 5.7 Online Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
2 thoughts on “Handy MySQL Date functions with examples.”