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:

<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!