MySQL - INTERVAL 运算符

要通过 JavaScript 程序在 MySQL 表中执行 Interval 运算符，我们需要使用 mysql2 连接器的 query() 函数执行 INTERVAL 语句。

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

要通过 Java 程序在 MySQL 表中执行 Interval 运算符，我们需要使用 JDBC type 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"
});
 
  //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
 
  //创建数据库
  sql = "create database TUTORIALS"
  con.query(sql);
 
  //选择数据库
  sql = "USE TUTORIALS"
  con.query(sql);
 
  //创建OTT表
  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);
 
  //插入记录
  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);
 
  //使用 INTERSECT 运算符
  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://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();
    }
  }
}                 

输出

获得的输出如下所示 -

Date '2023-04-14' after 10 days: 2023-04-24 

import mysql.connector
#建立连接
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)
# 获取所有符合条件的行
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                 

输出

以下是上述代码的输出 -

('2023-06-07',)