MySQL - 區間運算子

要在 MySQL 表中透過 JavaScript 程式執行 Interval 運算子，我們需要使用mysql2聯結器的query()函式執行INTERVAL語句。

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

要在Java程式中執行MySQL表的INTERVAL運算子，需要使用JDBC 4型別驅動程式的executeQuery()函式執行INTERVAL語句。

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

要在Python程式中執行MySQL表的INTERVAL運算子，需要使用MySQL Connector/Python提供的execute()函式執行INTERVAL語句。

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

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

輸出

生成的輸出如下：

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://: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();
    }
  }
}                 

輸出

獲得的輸出如下所示：

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

輸出

以上程式碼的輸出如下：

('2023-06-07',)