mysql determines whether the current time is between the start and end time and the start and end time are allowed to be empty

mysql determines whether the current time is between the start and end time and the start and end time are allowed to be empty

Requirement: Query ongoing activity data

There are several situations in progress:
1. If the start time is empty and the end time is empty, the result data will always be in progress.
2. If the start time is empty and the end time is not empty, the current time is before the end time and is in progress data
3. If the start time is not empty and the end time is empty, the current time is after the start time and is in progress data
4. If the start time is not empty and the end time is not empty, the data within the start and end time period is the ongoing data.

The following SQL query is the result set that meets the above four requirements, meeting the title requirements

SELECT * FROM 
Table name WHERE 1=1 
and(start_time is null or start_time<now()) 
and(end_time is null or end_time>now())

Mybatis writing method, the start time and end time input parameters are allowed to be empty as shown in the figure:

insert image description here

<if test="record.startDate != null and record.startDate != '' or record.endDate != null and record.endDate != '' ">
      AND id in
      (select id from rht_product_price where 1=1
      <if test="record.startDate != null and record.startDate != ''">
        and start_date &lt;= #{record.startDate,jdbcType=VARCHAR}
      </if>
      <if test="record.endDate!= null and record.endDate != ''">
        and end_date &gt;= #{record.endDate,jdbcType=VARCHAR}
      </if>
      )
    </if>

This is the end of this article about MySQL judging whether the current time is between the start and end time and the start and end time are allowed to be empty. For more relevant information about MySQL judging whether the current time is between the start and end time, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Analyze the method of setting the current time as the default value in MySQL
  • Get the current system time and date in MySQL to facilitate query and judgment code
  • Insert current time in php MYSQL

<<:  Example of using CSS filter to write mouse over effect

>>:  Ideas and codes for implementing iframe to intercept part of the website content

Recommend

Vue uses rules to implement form field validation

There are many ways to write and validate form fi...

Detailed explanation of using pt-heartbeat to monitor MySQL replication delay

pt-heartbeat When the database is replicated betw...

Detailed installation and use of SSH in Ubuntu environment

SSH stands for Secure Shell, which is a secure tr...

MySQL 5.7.18 Archive compressed version installation tutorial

This article shares the specific method of instal...

Learn MySQL in a simple way

Preface The database has always been my weak poin...

HTML form and the use of form internal tags

Copy code The code is as follows: <html> &l...

How to solve the mysql insert garbled problem

Problem description: When inserting Chinese chara...

Docker Stack deployment method steps for web cluster

Docker is becoming more and more mature and its f...

Reasons and solutions for prompting to save action after uploading files in form

The json data must be returned in html format That...

Summary of Linux commands commonly used in work

Use more open source tools such as docker and kub...

Introduction to the use and advantages and disadvantages of MySQL triggers

Table of contents Preface 1. Trigger Overview 2. ...

Getting Started Guide to MySQL Sharding

Preface Relational databases are more likely to b...

CSS3 realizes the graphic falling animation effect

See the effect first Implementation Code <div ...