https://i0.wp.com/joshuaotwell.com/wp-content/uploads/2021/12/calendar-g738f0c8ad_640.png?w=640&ssl=1
Anyone working with data is going to encounter date values at some point. Dates are necessary for many reasons. If you’re a PHP/MySQL Developer, you have date function options for both the database and your programming language of choice. In this post, I cover 5 PHP date functions and their (somewhat) MySQL equivalents with examples of each. Continue reading…
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
The Newsletter for PHP and MySQL Developers
PHP: date_format() | MySQL: DATE_FORMAT()
We always hope that the data comes to us in the format we need. In reality, we know better and that normally, data is formatted (if at all) in a way that we can’t use or don’t wish to use. With that being said, you can use either of these functions and put date data in pretty much any format you need.
Both functions have a myriad number of different formatting options and covering them all in one blog post would be convoluted at best. Instead, I’ll cover a couple of the common ones I am familiar with and leave the exploration up to you.
date_format()
Syntax PHP date_format():
date_format($object, $format)
- Returns a date as specified by the format.
- Both parameters are required.
Using the date_create() function, I make a practice date we can use for the examples:
$some_date = date_create('2021-12-04');
echo date_format($some_date, 'Y-d-m');
Echoing out in the browser returns:
2021-04-12
In order to format the date value to a full month name, 2-digit day (with suffix), and 4-digit year, we can use these format parameters:
echo date_format($some_date, 'F jS, Y');
Which returns:
December 4th, 2021
Oftentimes, you see dates in the 2-digit month 2-digit day, and 4-digit year format (MM/DD/YYYY). These format strings shown in the following example produce that format for you:
echo date_format($some_date, 'd/m/Y');
04/12/2021
(There are several format options for PHP’s date_format(). Visit the date_format() documentation to see them all.)
DATE_FORMAT()
Syntax MySQL DATE_FORMAT():
DATE_FORMAT(date, format)
- Returns a date as specified by the format.
- Both parameters are required.
For simplicity’s sake, I am using the ‘store’ table from the sakila practice database with this data:
mysql> SELECT last_update
-> FROM store;
+---------------------+
| last_update |
+---------------------+
| 2006-02-15 04:57:12 |
| 2006-02-15 04:57:12 |
+---------------------+
2 rows in set (0.00 sec)
For a 3-letter month abbreviation, a 2-digit day – with the suffix – and the full 4-digit year, we can use these format specifiers:
mysql> SELECT DATE_FORMAT(last_update, '%b %D, %Y') AS frmt
-> FROM store;
+----------------+
| frmt |
+----------------+
| Feb 15th, 2006 |
| Feb 15th, 2006 |
+----------------+
2 rows in set (0.00 sec
As you have seen in the 1st exploratory query on the ‘store’ table, the ‘last_update’ column date values are stored in a ‘YYYY-MM-DD’ format. If these values were not already stored as such, you can easily format them with the format specifiers shown in the next query:
mysql> SELECT DATE_FORMAT(last_update, '%Y-%m-%d') AS frmt
-> FROM store;
+------------+
| frmt |
+------------+
| 2006-02-15 |
| 2006-02-15 |
+------------+
2 rows in set (0.00 sec)
A common format you see is the ‘DD-MON-YY’ format (a lot of Oracle SQL dates are stored in this format). There are format specifiers for that pattern as well:
mysql> SELECT DATE_FORMAT(last_update, '%d-%b-%y') AS frmt
-> FROM store;
+-----------+
| frmt |
+-----------+
| 15-Feb-06 |
| 15-Feb-06 |
+-----------+
2 rows in set (0.00 sec)
(Visit this MySQL DATE_FORMAT() resource for more information.)
PHP: date_diff() | MySQL: DATEDIFF()
PHP’s date_diff() function returns the difference between 2 DateTime objects. The difference can be a number of different values such as days, months, or even hours (and many more).
date_diff()
Syntax PHP date_diff()
date_diff($origin_date, $target_date)
I’ll create 2 dates and then call the format() method on the resulting object from the date_diff() function call:
$current_date = date_create('2021-12-04');
$xmas_date = date_create('2021-12-25');
$num_days = date_diff($current_date, $xmas_date);
echo $num_days->format('%R%a days');
The a format echoed in the browser returns:
+21 days
Using the m format, we can know the number of months difference between the date objects:
$current_date = date_create('2021-12-04');
$six_mnths_later = date_create('2021-06-25');
$num_mnths = date_diff($current_date, $six_mnths_later);
echo $num_mnths->format('%m months');
5 months
(Note: date_diff() is in the procedural style. Read more in the official date_diff() documentation.)
DATEDIFF()
MySQL’s DATEDIFF() function returns the number of days between 2 date values. This function is handy for calculating how many days an event happened in the past or will happen in the future (i.e., a birthday, holiday, anniversary, etc…).
Syntax MySQL DATEDIFF():
DATEDIFF(date_1, date_2)
At the time of writing this post, MySQL’s CURRENT_DATE() function returns this value in my local learning/development environment:
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2021-12-04 |
+----------------+
1 row in set (0.00 sec)
Christmas is on December the 25th and we can calculate the remaining number of days until the holiday using DATEDIFF():
mysql> SELECT DATEDIFF('2021-12-25', CURRENT_DATE()) AS days_to_xmas;
+--------------+
| days_to_xmas |
+--------------+
| 21 |
+--------------+
1 row in set (0.00 sec)
Hope y’all were good this year so Santa visits!
(More information on DATEDIFF().)
You can support my content and this blog by tossing any spare change you have in my Tip Jar. I really appreciate your support!
PHP: date() | MySQL: NOW()
PHP’s date() function returns the current time, formatted according to the $format_string argument, if no $timestamp argument value is provided in the function call.
date()
Syntax PHP date():
date(string $format_string, $timestamp = null)
As of the time of writing this segment of the post, the date() function returns this date in my local development environment:
$current_date = date('Y-m-d');
echo $current_date;
2021-12-10
Just as several other example PHP date functions, there are many formatting options for date().
$current_date = date('F jS, Y');
echo $current_date;
December 10th, 2021
(Visit the PHP date() function documentation for more information on this function.)
NOW()
The MySQL NOW() function returns the current date and time. NOW() accepts no arguments.
Syntax MySQL NOW():
NOW()
Let’s see an example of NOW() in my local development environment:
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2021-12-04 19:36:44 |
+---------------------+
1 row in set (0.00 sec)
The NOW() function is easy to use and provides a handy date/time value you can use to calculate information based off on well, now.
(Learn more about the NOW() function here.)
PHP: date_add() | MySQL: DATE_ADD()
If you have a date value that you want to add another unit (DAY, MONTH, YEAR, etc) to, both PHP’s date_add() and MySQL’s DATE_ADD() functions return this type of calculation.
date_add()
Syntax PHP date_add():
date_add($date_object, $interval_amount)
(Note: date_add() is in the procedural style.)
There are several date intervals you can pass to date_add() as the 2nd parameter. In this example, I specify ’21 days’ in the date_interval_create_from__date_string() function (which is the 2nd parameter for date_add()):
$some_date = date_create('2021-12-04');
date_add($some_date, date_interval_create_from_date_string('21 days'));
echo date_format($some_date,'Y-m-d');
Returns:
2021-12-25
Months are a perfectly valid date interval and in the following example, we calculate a date 3 months in the future:
$some_date = date_create('2021-12-04');
date_add($some_date, date_interval_create_from_date_string('3 months'));
echo date_format($some_date,'F jS, Y');
And in the browser we see:
March 4th, 2022
(See the online date_add() documentation for more information.)
DATE_ADD()
Syntax MySQL DATE_ADD():
DATE_ADD(date_value, INTERVAL value add_unit_value)
DATE_ADD() accepts a date or time INTERVAL value, adds that INTERVAL value to the specified date, and returns a date based on the calculation.
Adding a 6 DAY INTERVAL to the date ‘2021-12-25’ using DATE_ADD() returns the date ‘2021-12-31’ (New Year’s Eve):
mysql> SELECT DATE_ADD('2021-12-25', INTERVAL 6 DAY) AS new_yr_eve;
+------------+
| new_yr_eve |
+------------+
| 2021-12-31 |
+------------+
1 row in set (0.00 sec)
The INTERVAL value must be singular. A plural form returns an error:
mysql> SELECT DATE_ADD('2021-12-25', INTERVAL 6 DAYS) AS new_yr_eve;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DAYS) AS new_yr_eve' at line 1
You can add whole months as the INTERVAL value as well:
mysql> SELECT DATE_ADD('2021-12-25', INTERVAL 6 MONTH) AS half_way;
+------------+
| half_way |
+------------+
| 2022-06-25 |
+------------+
1 row in set (0.01 sec)
(Learn more about DATE_ADD() by visiting this site.)
Promotional: I am making Gmail HTML Email Signature Templates over in my Etsy shop. Make your emails stand out and pop with your very own.
PHP: date_sub() | MySQL: DATE_SUB()
With PHP’s date_sub() function, you have all the same capabilities you do with date_add() as far as calculations are concerned. Except with date_sub(), you are subtracting the date intervals.
date_sub()
Syntax PHP date_sub():
date_sub($date_object, $interval_amount)
$some_date = date_create('2021-12-04');
date_sub($some_date, date_interval_create_from_date_string('3 months'));
echo date_format($some_date,'F jS, Y');
This echo of date_format() on the $some_date object is now:
September 4th, 2021
And if we subtract 3 days using date_sub(), we get:
$some_date = date_create('2021-12-04');
date_sub($some_date, date_interval_create_from_date_string('3 days'));
echo date_format($some_date,'F jS, Y');
December 1st, 2021
DATE_SUB()
Syntax MySQL DATE_SUB():
DATE_SUB(date_value, INTERVAL value subtract_unit_value)
We can add an INTERVAL to a date/time value using DATE_ADD() and are returned a calculated date. On the same token, we can subtract an INTERVAL using DATE_SUB(). Just as with DATE_ADD(), the specified INTERVAL unit must be in the singular.
Again, I won’t cover every available INTERVAL unit. Visit any of the links throughout the post for more information.
mysql> SELECT DATE_SUB('2021-12-25', INTERVAL 1 WEEK) AS wk_bf_xmas;
+------------+
| wk_bf_xmas |
+------------+
| 2021-12-18 |
+------------+
1 row in set (0.02 sec)
For example, an INTERVAL value of ‘2 MONTH’ returns a date 2 months prior to the target date parameter:
mysql> SELECT DATE_SUB('2021-12-25', INTERVAL 2 MONTH) AS 2mnths_bf_xmas;
+----------------+
| 2mnths_bf_xmas |
+----------------+
| 2021-10-25 |
+----------------+
1 row in set (0.00 sec)
(Learn more about DATE_SUB() and the many INTERVAL values you can use in the function call.)
Are you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!!
I hope you have learned something new about working with date values in PHP and MYSQL because I know I have. If you have any questions or see any mistakes in the code, please leave a comment below. Constructive comments help me better provide accurate information and are much appreciated. 
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
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 posts/technical writing I have completed for clients.
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
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.
The Newsletter for PHP and MySQL Developers
How can I help you?
- Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered.
- Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out.
- Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.
- I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.
- Grab a free pack of mobile Creator wallpapers.
Disclosure: Some of the services and products links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.
The post 5 PHP Date Functions and Their MySQL Equivalents appeared first on Digital Owl’s Prose.
Planet MySQL
In this blog post, we will compare the performance of performing a backup from a MySQL database using mysqldump, MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. All these available options are open source and free to use for the entire community.
