
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
What are the different unit values that can be used with MySQL INTERVAL keyword?
Different unit values which can be used with MySQL INTERVAL keyword are as follows −
MICROSECOND
This unit will be used for adding or subtracting the number of specified microseconds from the current time or as provided by the user.
mysql> Select NOW()+INTERVAL 100 MICROSECOND +--------------------------------+ | NOW()+INTERVAL 100 MICROSECOND | +--------------------------------+ | 2017-10-28 18:47:25.000100 | +--------------------------------+ 1 row in set (0.00 sec)
Above query will add 100 microseconds to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 100 Microsecond; +--------------------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 100 Microsecond | +--------------------------------------------------+ | 2017-02-25 05:04:30.000100 | +--------------------------------------------------+ 1 row in set (0.00 sec)
Above query will add 100 microseconds to the provided date & time with the help of MySQL INTERVAL keyword.
SECOND
This unit will be used for adding or subtracting the number of specified seconds from the current time or as provided by the user.
mysql> Select NOW()+INTERVAL 59 SECOND; +--------------------------+ | NOW()+INTERVAL 59 SECOND | +--------------------------+ | 2017-10-28 18:49:30 | +--------------------------+ 1 row in set (0.00 sec)
Above query will add 59 seconds to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 59 Second; +--------------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 59 Second | +--------------------------------------------+ | 2017-02-25 05:05:29 | +--------------------------------------------+ 1 row in set (0.00 sec)
Above query will add 59 seconds to the specified date & time with the help of MySQL INTERVAL keyword.
MINUTE
This unit will be used for adding or subtracting the number of specified minutes from the current time or as provided by the user.
mysql> Select NOW()+INTERVAL 10 MINUTE; +--------------------------+ | NOW()+INTERVAL 10 MINUTE | +--------------------------+ | 2017-10-28 18:58:44 | +--------------------------+ 1 row in set (0.00 sec)
Above query will add 10 minutes to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 10 Minute; +--------------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 10 Minute | +--------------------------------------------+ | 2017-02-25 05:14:30 | +--------------------------------------------+ 1 row in set (0.00 sec)
Above query will add 10 minutes to the specified date & time with the help of MySQL INTERVAL keyword.
HOUR
This unit will be used for adding or subtracting the number of specified hours from the current time or as provided by the user.
mysql> Select NOW()+INTERVAL 2 HOUR; +-----------------------+ | NOW()+INTERVAL 2 HOUR | +-----------------------+ | 2017-10-28 20:55:44 | +-----------------------+ 1 row in set (0.00 sec)
Above query will add 2 hours to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 1 Hour; +-----------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 1 Hour | +-----------------------------------------+ | 2017-02-25 06:04:30 | +-----------------------------------------+ 1 row in set (0.00 sec)
Above query will add 1 hour to the specified date & time with the help of MySQL INTERVAL keyword.
DAY
This unit will be used for adding or subtracting the number of specified days from the current date or as provided by the user.
mysql> Select NOW()+INTERVAL 10 DAY; +-----------------------+ | NOW()+INTERVAL 10 DAY | +-----------------------+ | 2017-11-07 18:48:53 | +-----------------------+ 1 row in set (0.00 sec)
Above query will add 10 days to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 10 Day; +-----------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 10 Day | +-----------------------------------------+ | 2017-03-07 05:04:30 | +-----------------------------------------+ 1 row in set (0.00 sec)
Above query will add 10 days to the specified date & time with the help of MySQL INTERVAL keyword.
WEEK
This unit will be used for adding or subtracting the number of specified weeks from the current date or as provided by the user.
mysql> Select NOW()+INTERVAL 1 WEEK; +-----------------------+ | NOW()+INTERVAL 1 WEEK | +-----------------------+ | 2017-11-04 18:49:10 | +-----------------------+ 1 row in set (0.00 sec)
Above query will add 1 week i.e. 7 days to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 1 Week; +-----------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 1 Week | +-----------------------------------------+ | 2017-03-04 05:04:30 | +-----------------------------------------+ 1 row in set (0.00 sec)
Above query will add 1 week i.e. 7 days to the specified date & time with the help of MySQL INTERVAL keyword.
MONTH
This unit will be used for adding or subtracting the number of specified months from the current date or as provided by the user.
mysql> Select NOW()+INTERVAL 1 MONTH; +------------------------+ | NOW()+INTERVAL 1 MONTH | +------------------------+ | 2017-11-28 18:49:31 | +------------------------+ 1 row in set (0.00 sec)
Above query will add 1 month to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 1 Month; +------------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 1 Month | +------------------------------------------+ | 2017-03-25 05:04:30 | +------------------------------------------+ 1 row in set (0.00 sec)
Above query will add 1 month to the specified date & time with the help of MySQL INTERVAL keyword.
QUARTER
This unit will be used for adding or subtracting the number of specified quarters (1 quarter = 3 months) from the current date or as provided by the user.
mysql> Select NOW()+INTERVAL 1 QUARTER; +--------------------------+ | NOW()+INTERVAL 1 QUARTER | +--------------------------+ | 2018-01-28 18:49:41 | +--------------------------+ 1 row in set (0.00 sec)
Above query will add 1 quarter i.e. 3 months to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 2 Quarter; +--------------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 2 Quarter | +--------------------------------------------+ | 2017-08-25 05:04:30 | +--------------------------------------------+ 1 row in set (0.00 sec)
Above query will add 2 quarters i.e. 6 months to the specified date & time with the help of MySQL INTERVAL keyword.
YEAR
This unit will be used for adding or subtracting the number of specified years from the current date or as provided by the user.
mysql> Select NOW()+INTERVAL 1 YEAR; +-----------------------+ | NOW()+INTERVAL 1 YEAR | +-----------------------+ | 2018-10-28 18:49:48 | +-----------------------+ 1 row in set (0.00 sec)
Above query will add 1 year to the current date & time with the help of MySQL INTERVAL keyword.
mysql> Select '2017-02-25 05:04:30' + INTERVAL 2 Year; +-----------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 2 Year | +-----------------------------------------+ | 2019-02-25 05:04:30 | +-----------------------------------------+ 1 row in set (0.00 sec)
Above query will add 2 years to the specified date & time with the help of MySQL INTERVAL keyword.
- Related Articles
- How can I use the arithmetic operators (+,-,*,/) with unit values of INTERVAL keyword in MySQL?
- What MySQL returns if I use enclosed set of unit values with INTERVAL keyword?
- What are the different wildcard characters that can be used with MySQL LIKE operator?
- What are the different wildcard characters that can be used with MySQL RLIKE operator?
- What are all the ways keyword ‘this’ can be used in Java?
- What are the different wildcard characters which can be used with NOT LIKE operator?
- How can I use INTERVAL keyword with MySQL NOW() and CURDATE() functions?
- What are the different commands used in MySQL?
- What can another keyword be used instead of MODIFY to modify the column/s of MySQL table?
- What MySQL returns if I write only one value in the enclosed set of unit values for compound INTERVAL unit?
- How can I use MySQL INTERVAL keyword while extracting the part of the date?
- Which MySQL function can be used to append values of a column with single quotes?
- Why BINARY keyword used with MySQL REGEXP operator?
- Can we use INTERVAL keyword while inserting date records in a MySQL table?
- What are the different ways in MySQL to add ‘half year interval’ in date?
