MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Interval Operator
MySQL INTERVAL Operator
The INTERVAL operator in MySQL is used to create an interval between two different events or times. This interval can be in seconds, minutes, hours, days, etc. Thus, MySQL mainly uses this operator to perform date and time calculations, such as adding or subtracting a specified time interval from date and time values.
INTERVAL operator is used with various date and time functions, and helps in real-time scenarios for calculating the deadlines, scheduling events, etc.
Syntax
Following is the syntax of INTERVAL operator in MySQL 
INTERVAL expr unit
Where,
expr: is a keyword that specifies the interval value.
unit: keyword determines the interval unit (such as DAY, HOUR, MINUTE, etc.).
Note: The INTERVAL and UNIT are case-insensitive.
Standard Formats For Interval Expressions and Units
Following is the table of MySQL standard formats for the interval expressions and its corresponding unit 
unit
expr
DAY
DAYS
DAY_HOUR
'DAYS HOURS'
DAY_MICROSECOND
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_MINUTE
'DAYS HOURS:MINUTES'
DAY_SECOND
'DAYS HOURS:MINUTES:SECONDS'
HOUR
HOURS
HOUR_MICROSECOND
'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_MINUTE
'HOURS:MINUTES'
HOUR_SECOND
'HOURS:MINUTES:SECONDS'
MICROSECOND
MICROSECONDS
MINUTE
MINUTES
MINUTE_MICROSECOND
'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND
'MINUTES:SECONDS'
MONTH
MONTHS
QUARTER
QUARTERS
SECOND
SECONDS
SECOND_MICROSECOND
'SECONDS.MICROSECONDS'
WEEK
WEEKS
YEAR
YEARS
YEAR_MONTH
'YEAR_MONTHS'
Example
The following query adds 10 days to the date “2023-04-14” 
SELECT '2023-04-14' + INTERVAL 10 DAY;
Output
The output for the query above is produced as given below 
'2023-04-14' + INTERVAL 10 DAY
2023-04-24
Example
The following query subtracts 5 days from the date "2023-04-14" 
SELECT '2023-04-14' - INTERVAL 5 DAY;
Output
The output for the query above is produced as given below 
'2023-04-14' - INTERVAL 5 DAY
2023-04-09
Example
Here, we are adding two hours to the datetime value "2023-04-14 09:45:30.000" 
SELECT '2023-04-14 09:45:30.000' + INTERVAL 2 HOUR;
Output
Following is the output 
'2023-04-14 09:45:30.000' + INTERVAL 2 HOUR
2023-04-14 11:45:30
Example
The following query is subtracting sixty minutes from the datetime value "2023-04-14 09:45:30.000" 
SELECT '2023-04-14 09:45:30.000' - INTERVAL 60 MINUTE;
Output
Following is the output 
'2023-04-14 09:45:30.000' - INTERVAL 60 MINUTE
2023-04-14 08:45:30
Example
Here, we are adding and deleting one from the date '2023-04-14' 
SELECT DATE_ADD('2023-04-14', INTERVAL 1 MONTH) ADD_ONE_MONTH,
DATE_SUB('2023-04-14',INTERVAL 1 MONTH) SUB_ONE_MONTH;
Output
On executing the given query, the output is displayed as follows 
ADD_ONE_MONTH
SUB_ONE_MONTH
2023-05-14
2023-03-14
Example
In the following query, we are using the TIMESTAMPADD() function to add two hours to the timestamp value 
SELECT TIMESTAMPADD (HOUR, 2, '2020-01-01 03:30:43.000') 2_HOURS_LATER;
Output
Let us compile and run the query, to produce the following result 
2_HOURS_LATER
2020-01-01 05:30:43
Example
Now, let us create a table with a name OTT using the following query 
CREATE TABLE OTT (
   ID INT NOT NULL,
   SUBSCRIBER_NAME VARCHAR (200) NOT NULL,
   MEMBERSHIP VARCHAR (200),
   EXPIRED_DATE DATE NOT NULL
);
Using the following query, we are inserting some records into the above-created table using the INSERT INTO statement as shown below 
INSERT INTO OTT VALUES
(1, 'Dhruv', 'Silver', '2023-04-30'),
(2, 'Arjun','Platinum', '2023-04-01'),
(3, 'Dev','Silver', '2023-04-23'),
(4, 'Riya','Gold', '2023-04-05'),
(5, 'Aarohi','Platinum', '2023-04-02'),
(6, 'Lisa','Platinum', '2023-04-25'),
(7, 'Roy','Gold', '2023-04-26');
The table is created as 
ID
SUBSCRIBER_NAME
MEMBERSHIP
EXPIRED_DATE
1
Dhruv
Silver
2023-04-30
2
Arjun
Platinum
2023-04-01
3
Dev
Silver
2023-04-23
4
Riya
Gold
2023-04-05
5
Aarohi
Platinum
2023-04-02
6
Lisa
Platinum
2023-04-25
7
Roy
Gold
2023-04-26
Now, we are selecting data from the OTT table for the subscribers whose membership is about to expire within the next 7 days from the specific date of '2023-04-01'.
SELECT ID, SUBSCRIBER_NAME, MEMBERSHIP, EXPIRED_DATE, 
DATEDIFF(expired_date, '2023-04-01') EXPIRING_IN
FROM OTT
WHERE '2023-04-01' BETWEEN DATE_SUB(EXPIRED_DATE, INTERVAL 7 DAY) 
AND EXPIRED_DATE;
On executing the given query, the output is displayed as follows 
ID
SUBSCRIBER_NAME
MEMBERSHIP
EXPIRED_DATE
EXPIRED_IN
1
Arjun
Platinum
2023-04-01
0
2
Riya
Gold
2023-04-05
4
3
Aarohi
Platinum
2023-04-02
1
Interval Operator Using Client Program
In addition to executing the Interval Operator in MySQL table using an SQL query, we can also apply the INTERVAL operator on a table using a client program.
Syntax
Following are the syntaxes of the Interval Operator in MySQL table in various programming languages 
PHPNodeJSJavaPython
To execute the Interval operator in MySQL table through a PHP program, we need to execute INTERVAL statement using the query() function of mysqli connector.
$sql = "INTERVAL expr unit";   
$mysqli->query($sql);
Example
Following are the implementations of this operation in various programming languages 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "SELECT '2023-04-14' + INTERVAL 10 DAY AS DATE";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Date '2023-04-14' after  10 days: \n");
   while($row = $result->fetch_assoc()) {
      printf("DATE: %s",
      $row["DATE"],);
      printf("\n");         
   }
} else {
   printf('Error.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Date '2023-04-14' after  10 days:
DATE: 2023-04-24     
﻿
﻿
﻿
﻿
﻿
﻿
﻿

unit	expr
DAY	DAYS
DAY_HOUR	'DAYS HOURS'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
HOUR	HOURS
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
MICROSECOND	MICROSECONDS
MINUTE	MINUTES
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
MONTH	MONTHS
QUARTER	QUARTERS
SECOND	SECONDS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
WEEK	WEEKS
YEAR	YEARS
YEAR_MONTH	'YEAR_MONTHS'

ID	SUBSCRIBER_NAME	MEMBERSHIP	EXPIRED_DATE
1	Dhruv	Silver	2023-04-30
2	Arjun	Platinum	2023-04-01
3	Dev	Silver	2023-04-23
4	Riya	Gold	2023-04-05
5	Aarohi	Platinum	2023-04-02
6	Lisa	Platinum	2023-04-25
7	Roy	Gold	2023-04-26

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Interval Operator

MySQL INTERVAL Operator

Syntax

Standard Formats For Interval Expressions and Units

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output

Example

Interval Operator Using Client Program

Syntax

Example

Output