Home arrow MySQL arrow Page 4 - Date Arithmetic With MySQL

The Number Game - MySQL

When dealing with date and time values, one of the more common (and complex) tasks involves performing addition and subtraction operations on these values. However, with MySQL's powerful date and time API taking care of all the minor adjustments for you, manipulating date and time data is no longer the tedious and time-consuming process it used to be. Find out why, inside.

TABLE OF CONTENTS:
  1. Date Arithmetic With MySQL
  2. When Two And Two Don't Make Four
  3. Counting Down
  4. The Number Game
  5. Artificial Intelligence
  6. A Short Interval
  7. Lather, Rinse, Repeat
  8. Code Poet
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 35
July 03, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In addition to futzing with days and dates, MySQL also provides you with the ability to perform date arithmetic on specific date and time values, with its DATE_ADD() and DATE_SUB() functions. The syntax of these functions is somewhat more complex than the ones you've seen thus far - here's what it looks like:


DATE_ADD(startDate, INTERVAL period periodType)

DATE_SUB(startDate, INTERVAL period periodType)

In order to better understand this, consider the following example, which adds 1 year to the specified start date and returns the new value:


mysql> SELECT DATE_ADD('2003-04-15', INTERVAL 1 YEAR);
+-----------------------------------------+
| DATE_ADD('2003-04-15', INTERVAL 1 YEAR) |
+-----------------------------------------+
| 2004-04-15 |
+-----------------------------------------+
1 row in set (0.18 sec)

Here's another one, this one adding 3 hours and 45 minutes to the starting timestamp,


mysql> SELECT DATE_ADD('2003-04-15 02:02', INTERVAL "03:45"
mysql> HOUR_MINUTE);
+------------------------------------------------------------+
| DATE_ADD('2003-04-15 02:02', INTERVAL "03:45" HOUR_MINUTE) |
+------------------------------------------------------------+
| 2003-04-15 05:47:00 |
+------------------------------------------------------------+
1 row in set (0.01 sec)

and yet another, this one adding 7 days, 1 hour, 55 minutes and 10 seconds to December 25 2000:


mysql> SELECT DATE_ADD('2000-12-25 12:00:00', INTERVAL "7 01:55:10"
DAY_SECOND);
+-------------------------------------------------------------------+
| DATE_ADD('2000-12-25 12:00:00', INTERVAL "7 01:55:10" DAY_SECOND) |
+-------------------------------------------------------------------+
| 2001-01-01 13:55:10 |
+-------------------------------------------------------------------+
1 row in set (0.01 sec)

In order to better understand this, it's necessary to delve a little deeper into the syntax of the DATE_ADD() and DATE_SUB() functions:

1. The first parameter, startDate, is a date or time value in either string ("YYYY-MM-DD HH:MM:SS") or number (YYYYMMDDHHMMSS) format. This parameter specifies the date to use as a base for the calculation.

2. The second parameter consists of three separate components, which together specify the interval which is to be added to (or subtracted from) the first parameter. The components consist of the keyword INTERVAL, followed by the interval period and a keyword providing information on the interval calculation to be performed.

The relationship between the formatting of the interval period and the keyword following it is fixed, and illustrated in the following table:


period (formatted as) periodType
---------------------------------------------
SECONDS SECOND
MINUTES MINUTE
HOURS HOUR
DAYS DAY
MONTHS MONTH
YEARS YEAR
"MINUTES:SECONDS" MINUTE_SECOND
"HOURS:MINUTES" HOUR_MINUTE
"DAYS HOURS" DAY_HOUR
"YEARS-MONTHS" YEAR_MONTH
"HOURS:MINUTES:SECONDS" HOUR_SECOND
"DAYS HOURS:MINUTES" DAY_MINUTE
"DAYS HOURS:MINUTES:SECONDS" DAY_SECOND

With this in mind, the examples above should become much clearer. If, for example, you wanted to perform a calculation involving interval units of 1 year, you could format your arguments using the YEAR type, whereas if you wanted to perform date arithmetic with hours and minutes, you could format your arguments using the HOUR_MINUTE or HOUR_SECOND types.

Let's look at a few more examples of this in action.


mysql> SELECT DATE_ADD(20041130, INTERVAL 1 MONTH);
+--------------------------------------+
| DATE_ADD(20041130, INTERVAL 1 MONTH) |
+--------------------------------------+
| 2004-12-30 |
+--------------------------------------+
1 row in set (0.88 sec)

mysql> SELECT DATE_ADD(20041130, INTERVAL 31 DAY);
+-------------------------------------+
| DATE_ADD(20041130, INTERVAL 31 DAY) |
+-------------------------------------+
| 2004-12-31 |
+-------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT DATE_ADD('2003-09-17 03:30:00', INTERVAL "02:30"
HOUR_MINUTE);
+---------------------------------------------------------------+
| DATE_ADD('2003-09-17 03:30:00', INTERVAL "02:30" HOUR_MINUTE) |
+---------------------------------------------------------------+
| 2003-09-17 06:00:00 |
+---------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_SUB('2003-01-01', INTERVAL 24 HOUR);
+------------------------------------------+
| DATE_SUB('2003-01-01', INTERVAL 24 HOUR) |
+------------------------------------------+
| 2002-12-31 00:00:00 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2003-09-17 12:00:00', INTERVAL "1 12" DAY_HOUR);

+-----------------------------------------------------------+
| DATE_SUB('2003-09-17 12:00:00', INTERVAL "1 12" DAY_HOUR) |
+-----------------------------------------------------------+
| 2003-09-16 00:00:00 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2003-01-01 18:45:10', INTERVAL "17:45:10"
HOUR_SECOND);
+------------------------------------------------------------------+
| DATE_SUB('2003-01-01 18:45:10', INTERVAL "17:45:10" HOUR_SECOND) |
+------------------------------------------------------------------+
| 2003-01-01 01:00:00 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)



 
 
>>> More MySQL Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: