CODEWITHSUNDEEP
javamysqlsqlspringspring-data-jpa
Matheus
Matheus

Reputation: 3380

Select BETWEEN dates returns wrong results

I have a Table in MySQL which has it's column definitions as below:

CREATE TABLE APPOINTMENT(
    CD_APPOINTMENT BIGINT NOT NULL,
    -- omitted for brevity
    APPOINT_DATE DATE NOT NULL
);

My JPA entity is defined as:

@Entity
@Table(name = "APPOINTMENT")
public class Appointment {

    protected Long id;
    protected Date date = new Date();
    // other atributes omitted for brevity

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "CD_APPOINTMENT")
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Temporal(TemporalType.DATE)
    @Column(name = "APPOINT_DATE", columnDefinition = "DATE")
    public Date getDate() {
        return date;
    }
}

As I'm using Spring, I have benefits of Spring Data JPA. Following that line, I'm using Spring Data JPA Repositories.

I'm testing in 2019-07-12 (at my timezone [UTC-3]).

When I run:

appointmentRepository.save(appointment);

the Appointment is successfully (more or less) saved.

Fine! The column APPOINT_DATE has the value of 2019-07-12, yes? Well, it's seems ok.

When I run:

SELECT * FROM APPOINTMENT;

the retrieved rows looks as expected:

CD_APPOINTMENT|APPOINT_DATE
--------------|------------
             1|  2019-07-12

The strange part appears when I try to filter BETWEEN dates.

If I run my JPQL:

SELECT ap FROM Appointment AS ap WHERE ap.date BETWEEN :startDate AND :endDate

startDate and endDate are parameters received in a @Param annotation in Spring and both of them have the value of 2019-07-12

I get 0 rows, but I was expecting to get one (the above inserted Appointment). Firstly, I thought it was a problem with the JPQL, but it's not. If I execute the same JPQL with a different RDBMS (like H2, for an example), the query works perfectly.

And if I run the same JPQL but in SQL, directly on the MySQL database:

SELECT * FROM APPOINTMENT where APPOINT_DATE BETWEEN '2019-07-12' AND '2019-07-12'

just like the JPQL it returns 0 rows.

If I run the now(); command at MySQL database, it return the CORRECT date time.

How can I fix it? Has anybody seen something like that already? Because I have not.

Upvotes: 0

Views: 709

Answers (3)

Matheus
Matheus

Reputation: 3380

My MySQL instance is from Amazon RDS.

Their default Time Zone is UTC. Switched from UTC to Brazil/East and now it's working as expected.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

It is best not to use between for date/times. One reason is because there might be a time component that throws off the comparison.

I would suggest:

SELECT *
FROM APPOINTMENT 
WHERE APPOINT_DATE >= '2019-07-12' AND
      APPOINT_DATE < '2019-07-13'

This logic works with an without a time component. And it can take advantage of an index on the date column.

Upvotes: 1

alexherm
alexherm

Reputation: 1362

BETWEEN '2019-07-12' AND '2019-07-13'

Upvotes: 1

Related Questions