mysql-java获取某日或当日0点到24点数据条件书写

mysql-java 获取当日0点到24点数据

1.sql

SELECT * FROM xxx
WHERE create_time BETWEEN
DATE_FORMAT(NOW(),’%Y-%m-%d 00:00:00’)
AND DATE_FORMAT(NOW(),’%Y-%m-%d 23:59:59’)

如下亦可

2.xml

<!-- 平台端查询用户所有记录 注：被删除的也需要被查出来 -->
    <select id="findAllSearchRecords" resultType="com.xianboss.goods.entity.SearchRecordsEntity">
        SELECT * FROM goods_user_search_records
        WHERE
        1=1
        <if test="dto.startDate!=null and dto.startDate!=''">
            AND create_time &gt;= DATE_FORMAT(#{dto.startDate},'%Y-%m-%d 00:00:00')
        </if>
        <if test="dto.endDate!=null and dto.endDate!=''">
            AND create_time &lt;= DATE_FORMAT(#{dto.endDate},'%Y-%m-%d 23:59:59')
        </if>
    </select>

3.java实体类

注：DTO实体类数据类型必须为String类型 否则查询报错

@Data
@ApiModel
public class SearchRecordsQueryDTO implements Serializable {

	private static final long serialVersionUID = 3880025527963904889L;

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd")
    @ApiModelProperty(value = "从这一天开始")
    private String startDate;

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd")
    @ApiModelProperty(value = "到这一天为止")
    private String endDate;

}