Home arrow MySQL arrow Page 5 - Date Arithmetic With MySQL

Artificial Intelligence - 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

It should be noted that MySQL includes intelligence to automatically format the result of the calculation as either a date-only or date-and-time value. For example, if your arguments to DATE_ADD() or DATE_SUB() contains only year, month and day components, MySQL will output a date-only value as result.


mysql> SELECT DATE_SUB(20041130, INTERVAL 3 MONTH);
+--------------------------------------+
| DATE_SUB(20041130, INTERVAL 3 MONTH) |
+--------------------------------------+
| 2004-08-30 |
+--------------------------------------+
1 row in set (0.00 sec)

However, if your arguments include a time component - hours, minutes or seconds - then MySQL produces a result containing both date and time components.


mysql> SELECT DATE_ADD('2010-02-14', INTERVAL "1 1" DAY_HOUR);
+-------------------------------------------------+
| DATE_ADD('2010-02-14', INTERVAL "1 1" DAY_HOUR) |
+-------------------------------------------------+
| 2010-02-15 01:00:00 |
+-------------------------------------------------+
1 row in set (0.01 sec)

If you use illegal date or time values, MySQL will still attempt to return a valid result by performing adjustments on the various values.


mysql> SELECT DATE_ADD('2010-02-30', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD('2010-02-30', INTERVAL 1 DAY) |
+----------------------------------------+
| 2010-03-03 |
+----------------------------------------+
1 row in set (0.00 sec)

MySQL also provides an alternative syntax to DATE_ADD() and DATE_SUB(), which is sometimes more readable - this involves using + and - signs to indicate the type of calculation to be performed. Consider the
following examples, which illustrate:


mysql> SELECT 20000615 + INTERVAL 5 DAY;
+---------------------------+
| 20000615 + INTERVAL 5 DAY |
+---------------------------+
| 2000-06-20 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT 20000615000000 - INTERVAL 10 SECOND ;
+-------------------------------------+
| 20000615000000 - INTERVAL 10 SECOND |
+-------------------------------------+
| 2000-06-14 23:59:50 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '2000-06-15 13:00' - INTERVAL "2-1" YEAR_MONTH;
+------------------------------------------------+
| '2000-06-15 13:00' - INTERVAL "2-1" YEAR_MONTH |
+------------------------------------------------+
| 1998-05-15 13: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: