MySQL Has Functions (Part 1) : Date & Time Functions

If you’re a web developer who uses PHP, you’re no doubt aware of the hundreds of functions available to you when creating websites and web applications. However, what you're probably not really aware of is the wealth of functions available to you within the MySQL database management system that can make your life much easier.

Date & Time Functions


Over the course of the next few articles I’m going to provide an introduction to some of these functions and show you how you can make use of them to make your code cleaner and more efficient. Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.

To keep things more simple and easy to read, I won’t be going into too much detail. One of the best ways to learn new development skills is by going through the manual and trying out examples for yourself.

As the main focus of this series will be using MySQL functions to tidy up your code, I won't dwell on any performance aspects of using MySQL's functions. I suspect that they may make things slightly faster, but probably not in any way that will affect the performance of the average website. Keep a look out for performance tests later in the series.

MySQL’s Date & Time Functions

I will start with MySQL's date and time functions because these are the ones I've found most useful in my day-to-day web development work. Oftentimes, you can avoid writing multiple lines of PHP code and get MySQL to do the work for you instead.

You can view the full list of these functions here on the MySQL documentation website.

For some of the examples in this post I'll be using the fictional scenario of a school. The school has a table of students which records various details about them. As you can see below, most of these fields are date & time related and in the format YYYY-MM-DD (HH:M:SS).

Database Table

DATE_FORMAT() is a function that is utterly a godsend.

It works just like PHP's date() function in that you pass a date and a formatting string which explains how you'd like the output formatted.

Unlike PHP, where the date must be passed as a Unix timestamp, in MySQL the date can either be a date field from your database or a string directly entered.

The formatting string is made up of special characters, each beginning with ‘%’ which represents a different date or time property such as day, week, month or year. Let's look at an example:

Let's say we want to display a list of the students in the database, showing their name and the date they enrolled at the school. The date should be in an easily read format such as '24th January 1996'.

Using a simple query like:

"SELECT student_name, student_enrol_date FROM students"

in PHP you might write something like this:

while($student = mysqli_fetch_assoc($allStudents)) {
	$enrolTimestamp = strtotime($student['student_enrol_date']);
	$enrolDate = date('jS F Y', $enrolTimestamp);
	echo $student['student_name'] . ' : ' .
	$student['student_enrol_date'] . '<br />';
}

but by modifying our SELECT query in a very small way:

"SELECT student_name, DATE_FORMAT(student_enrol_date, '%D %M %Y') as enrolDate FROM students"

We are able to get the same result with a relatively large reduction in code and barely any increase in SQL.

while($student = mysqli_fetch_assoc($allStudents)) {
	echo $student['student_name'] . ' : ' . $student['enrolDate'] . '<br />';
}

This gives us the desired output:

Joe Bloggs : 4th June 2008
John Smith : 5th February 2008
Jane Doe : 5th January 2009

It may not seem like much, but when you're doing multiple date manipulations in your web application it can make a huge difference - especially if you have to come back to the code at a much later date!

Getting the Current Date or Time (or both)

If you ever need to use the current date or time in one of your queries, there’s no need to make PHP do the work for you. Simply use one of the following functions.

  • CURDATE() returns the current date as a string in the format YYYY-MM-DD
  • CURTIME() returns the current time as a string in the format HH:MM:SS
  • NOW() or SYSTIME() returns a stamp of the current date & time in the format YYYY-MM-DD HH:MM:SS

Note: NOW() and SYSTIME() don't behave in exactly the same way.

NOW() will return a timestamp from when the query was started, so the value returned will be constant throughout the query, even if it takes an hour to run.

SYSTIME() on the other hand will always return a timestamp based on the current system time i.e. the server's internal clock.

Adding And Subtracting Dates & Times

MySQL’s DATE_ADD() and DATE_SUB() functions provide an easy way to add or subtract periods from a date or time.

They both take 2 parameters: a date field or string, and an INTERVAL. An interval is simply a textual representation of a period of time e.g. 3 YEAR, 3 MONTH etc. You can see more INTERVAL values here.

A good example of how this would be useful is if you need to retrieve data from a table, but only within a certain time frame - for example, the last 10 days.

Using PHP would involve not only creating a string for the current date, but also working out the date string for 10 days ago.

$currentTimestamp = time();
$currentDate = date('Y-m-d H:i:s', $currentTimestamp);
$tenDaysAgo = strtotime('-10 days', $currentTimestamp);
$dateTenDaysAgo = date('Y-m-d H:i:s', $tenDaysAgo);
$sql = 'SELECT required_field FROM table_name WHERE date_field < "' . $currentDate . '" AND date_field > "' . $dateTenDaysAgo . '"';

While this isn’t exactly difficult, why go to the trouble of writing code when MySQL can do it all for you:

$sql = 'SELECT required_table_field FROM table_name WHERE date_field < NOW() AND date_field > DATE_SUB(NOW(), INTERVAL 10 DAY)';

One thing to note is that because we're using the DATE_SUB() function, the INTERVAL is positive i.e. we're subtracting +10 days. If you were to use -10 DAY it would be the same as using DATE_ADD().

Bringing It All Together

Here’s a practical example. Let's assume we want to find all the students at the school who will be taking an exam in the next 7 days.

We want to output this information in the same way as the DATE_FORMAT() example, but showing the time of the exam as well in 24 hour format e.g. 13:30PM.

I'm going to use a combination of the 3 functions above to show you how easy it is to build a useful query with MySQL's functions. I'll build the query up in stages to better illustrate how it gets put together.

Query 1: Formatting the Date

"SELECT student_name, DATE_FORMAT(student_exam_date, '%D %M %Y at %k:%i%p') AS examDate FROM students"

To the query from the first example, I've added the extra formatting needed for the time portion - %k:%i:%p, and of course changed the date field being selected.

Notice that I've used the word 'at' in the formatting string. If it doesn't start with a '%' MySQL will treat it as a literal string and simply return it as is.

Query 2: Restricting the Period Selected

"SELECT student_name, DATE_FORMAT(student_exam_date, '%D %M %Y at %k:%i%p') AS examDate FROM students WHERE student_exam_date > NOW() AND student_exam_date < DATE_ADD(NOW(), INTERVAL 7 DAY)"

Now I've added the WHERE part of the query to restrict the results to only those with an exam date in the next 7 days.

This gives us the desired output of:

Joe Bloggs : 6th May 2010 at 10:30AM
John Smith : 3rd May 2010 at 9:00AM

Conclusion

There might not seem to be anything special about MySQL's functions. However, MqSQL’s functions can make your life so much easier. Unfortunately, they are something that is rarely seen used by developers.

I hope you've enjoyed reading this introduction - Stay tune for the next part of this series. Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.