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

win10 docker-toolsbox tutorial on building a php development environment

Download image docker pull mysql:5.7 docker pull ...

How to deploy Vue project under nginx

Today I will use the server nginx, and I also nee...

Solution to the horizontal scroll bar in iframe under IE6

The situation is as follows: (PS: The red box repr...

centos 7 modify sshd | prohibit root login and sshd port script definition

1. Create a new user wwweee000 [root@localhost ~]...

MySQL foreign key (FOREIGN KEY) usage case detailed explanation

Introduction: The disadvantages of storing all da...

Implementation of MySQL5.7 mysqldump backup and recovery

MySQL backup Cold backup:停止服務進行備份,即停止數據庫的寫入Hot ba...

HTML Basics_General Tags, Common Tags and Tables

Part 1 HTML <html> -- start tag <head>...

Native JS to achieve blinds special effects

This article shares a blinds special effect imple...

Mysql example of splitting into multiple rows and columns by specific symbols

Some fault code tables use the following design p...

A brief discussion on the solution of Tomcat garbled code and port occupation

Tomcat server is a free and open source Web appli...

How to quickly install tensorflow environment in Docker

Quickly install the tensorflow environment in Doc...

Solution to the garbled code problem in MySQL 5.x

MySQL is a commonly used open source database sof...

MySQL sql_mode analysis and setting explanation

When inserting a set of data into the MySQL datab...