Select BETWEEN dates returns wrong results

Question

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.

Your columns is of DATE type, and so I would guess that the BETWEEN call isn't working like you expect - I have found the behavior of BETWEEN is often not as expected. To fix, use specific "APPOINT_DATE >= '2019-07-12' AND APPOINT_DATE < '2019-07-12' to make it very clear which side of the logic is includes the endpoint, and which doesn't. — mgrollins, Jul 13 '19 at 00:34
Same result. 0 rows returned by this query. Both on SQL and JPQL — Matheus, Jul 13 '19 at 00:36
Hmmm, maybe check out this other answer then: https://stackoverflow.com/a/50838253/3911355 — mgrollins, Jul 13 '19 at 00:53

score 1 · Answer 1 · answered Jul 13 '19 at 01:09

1

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

answered Jul 13 '19 at 01:09

alexherm

1,362
2
18
31

Ok, it works, but why `APPOINT_DATE = '2019-07-12'` returns 0 rows? – Matheus Jul 13 '19 at 01:36

score 1 · Answer 2 · answered Jul 13 '19 at 01:24

1

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.

answered Jul 13 '19 at 01:24

Gordon Linoff

1,242,037
58
646
786

Ok, it works, but why `APPOINT_DATE = '2019-07-12'` returns 0 rows? – Matheus Jul 13 '19 at 01:36
1

@MatheusCirillo . . . Two possibilities. First, perhaps it is a `datetime` with a time component. Second, perhaps you have an arcane format set, so that is interpreted as 7 Dec 2019. – Gordon Linoff Jul 13 '19 at 01:46
That's really weird. If I run `select sysdate();` or `select now();` or `select curdate()` or `select current_date();`. All of these commands returns the right format 2019-07-12 (yyyy-MM-dd). Still haven't found a fix for this.. – Matheus Jul 13 '19 at 01:56
@MatheusCirillo . . . The first two have a time component. – Gordon Linoff Jul 13 '19 at 02:02
Wow, another weird behaviour. If I run `WHERE APPOINT_DATE = '2019-07-13'` (13 day), it works....how did that happen? The column `APPOINT_DATE` has the value of **2019-07-12**. What is going on right here? That's one of the strangest things I've ever seen. – Matheus Jul 13 '19 at 02:03
1

Fixed. Sad to know that the problem was the time zone on mysql instance. As its from Amazon RDS, it's default is UTC. Switched from UTC to Brazil/East and now it's working. Yes, Im mad at this. – Matheus Jul 13 '19 at 02:30

score 0 · Accepted Answer · answered Jul 13 '19 at 02:31

0

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.

answered Jul 13 '19 at 02:31

Matheus

3,058
7
16
37

Select BETWEEN dates returns wrong results

3 Answers3