Khóa học miễn phí MySQL – INTERVAL Operator nhận dự án làm có lương

MySQL – Interval Operator

Table of content


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 −

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);

To execute the Interval operator in MySQL table through a JavaScript program, we need to execute INTERVAL statement using the query() function of mysql2 connector.

sql = "INTERVAL expr unit"; 
con.query(sql);

To execute the Interval operator in MySQL table through a Java program, we need to execute INTERVAL statement using the executeQuery() function of JDBC type 4 driver.

String sql = "INTERVAL expr unit";
statement.executeQuery(sql);

To execute the Interval operator in MySQL table through a Python program, we need to execute INTERVAL statement using the execute() function provided by MySQL Connector/Python.

interval_query = "INTERVAL expr unit"
cursorObj.execute(interval_query);

Example

Following are the implementations of this operation in various programming languages −

$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     
var mysql = require(''mysql2'');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "Nr5a0204@123"
});
 
  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
 
  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);
 
  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);
 
  //Creating OTT table
  sql = "CREATE TABLE OTT (ID INT NOT NULL,SUBSCRIBER_NAME VARCHAR (200) NOT NULL,MEMBERSHIP VARCHAR (200),EXPIRED_DATE DATE NOT NULL);"
  con.query(sql);
 
  //Inserting Records
  sql = "INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, EXPIRED_DATE) 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'');"
  con.query(sql);
 
  //Using INTERSECT Operator
  sql = "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;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});    

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 2,
    SUBSCRIBER_NAME: ''Arjun'',
    MEMBERSHIP: ''Platinum'',
    EXPIRED_DATE: 2023-03-31T18:30:00.000Z,
    Expiring_in: 0
  },
  {
    ID: 4,
    SUBSCRIBER_NAME: ''Riya'',
    MEMBERSHIP: ''Gold'',
    EXPIRED_DATE: 2023-04-04T18:30:00.000Z,
    Expiring_in: 4
  },
  {
    ID: 5,
    SUBSCRIBER_NAME: ''Aarohi'',
    MEMBERSHIP: ''Platinum'',
    EXPIRED_DATE: 2023-04-01T18:30:00.000Z,
    Expiring_in: 1
  }
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IntervalClause {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT ''2023-04-14'' + INTERVAL 10 DAY";
            rs = st.executeQuery(sql);
            System.out.print("Date ''2023-04-14'' after 10 days: ");
            while(rs.next()){
              String date = rs.getNString(1);
              System.out.println(date);
              System.out.println();
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                 

Output

The output obtained is as shown below −

Date ''2023-04-14'' after 10 days: 2023-04-24 
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
  host=''localhost'',
  user=''root'',
  password=''password'',
  database=''tut''
)
cursorObj = connection.cursor()
interval_query = f"""
SELECT ''2023-05-28'' + INTERVAL 10 DAY;
"""
cursorObj.execute(interval_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                 

Output

Following is the output of the above code −

(''2023-06-07'',)  

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc