Use MySQL’s Date Functions Instead of PHP’s

Many of the MySQL queries that I create include a date and / or time. My usual place to get this information was from PHP's date() function. At least until I discovered that MySQL has many built-in functions to do the same thing. Let's look at a quick example.

Background

My goal for the last few years is to better utilize MySQL's built-in functions and features. Most of the time, my thought goes straight to PHP when developing solutions. When adding dates and times to a query, for example, my typical process was to use PHP's date() function.

$sql = "INSERT INTO userProfile SET updated='" . date('Y-m-d H:i:s') . "'";

Alternate Solution

Instead, the above query could use one of the many MySQL date and time functions, such as NOW().

$sql = "INSERT INTO userProfile SET updated=NOW()";

Note that I removed the quotes around the date value being assigned to the "updated" field.

Time Zone Issue

If you are used to setting the time zone in PHP, be aware that the setting doesn't affect MySQL queries. To get the correct time in your query, you may need to set the time zone for your database connection.

$sql = "SET time_zone = 'Your-Time-Zone-Here'";

More information about setting your time zone can be found in the manual (MySQL Server Time Zone Support).