CODEWITHSUNDEEP
sqlpostgresql
lapots
lapots

Reputation: 13395

two queries return the same result

I want to return date from last week except yesterday and I want to return only yesterday's data.

My last week query looks like this

SELECT b.id,
    b.publisher_id,
    b.name,
    b.size,
    b.present,
    b.display,
    d.book_id AS
    download,
    b.download_date
    FROM books b LEFT
    JOIN
    downloads d ON
    d.book_id=b.id
    WHERE DATE(b.download_date) >= CURRENT_DATE - INTERVAL '1 week'
        AND NOT DATE(b.download_date) > CURRENT_DATE - INTERVAL '1 day'

The query for yesterday data looks like this

SELECT b.id,
    b.publisher_id,
    b.name,
    b.size,
    b.present,
    b.display,
    d.book_id AS
    download,
    b.download_date
    FROM books b LEFT
    JOIN
    downloads d ON
    d.book_id=b.id
    WHERE DATE(b.download_date) = CURRENT_DATE - INTERVAL '1 day'

But they return the same result. Why? My data looks like this where "2014-06-16 07:56:27.672+00" is the download_date

2655;85;"Amsco - Jeff Beck Anthology.rar";12893115;f;f;;"2014-06-16 07:56:27.672+00"
3035;108;"Apress - Big Data Analytics.rar";8254213;f;f;;"2014-06-16 07:56:27.672+00"
7076;291;"Century - Wall and Piece.rar";66864919;f;f;;"2014-06-16 07:56:27.672+00"
266;9;"A K Peters - Video-Based Rendering.rar";2096583;f;f;;"2014-06-16 07:56:27.672+00"

My current date - 2014-06-17 10:40....

What's the problem?

Upvotes: 0

Views: 56

Answers (1)

the_tiger
the_tiger

Reputation: 346

Based on your data, all downloads are from yesterday. (Current: 06-17, downloads 06-16). The query for yesterday works fine because the current date minus 1 day is exactly the download date. The mistake is in the first query:

AND NOT DATE(b.download_date) > CURRENT_DATE - INTERVAL '1 day'

has to be changed to

AND NOT DATE(b.download_date) >= CURRENT_DATE - INTERVAL '1 day'

because at the moment you are only excluding downloads from today (download date GREATER THAN yesterday)

Upvotes: 1

Related Questions