codeheadache
codeheadache

Reputation: 164

query two specific dates in postgresql

enter image description hereI have a table as below. I want to query specific dates from the table. The dates are random and therefore i do not want to use the BETWEEN keyword. Also, the number of dates that i want to query could vary from one to many(for simplicity lets say 2 distinct dates.

create table temptable(id serial primary key not null,myTimestamp timestamp);
insert into temptable(myTimestamp) values ('2020-09-25 02:02:51.99');
insert into temptable(myTimestamp) values ('2020-08-24 12:20:51.111');
insert into temptable(myTimestamp) values ('2020-09-23 13:20:51.286');

The following query is executed hoping to get two distinct dates.

select * 
from temptable 
where myTimestamp::date = date '2020-09-23' 
and myTimestamp::date = date '2020-08-24';

The above query executes on pgadmin but nothing is listed on the table. If i use OR operator, i can see one date returned but that i not what i want. I want both the dates

Please advice

Thanks

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

One method uses in:

select * 
from temptable 
where myTimestamp::date in (date '2020-09-23', date '2020-08-24');

However, this might be more efficient with inequalities for the date comparisons:

select * 
from temptable 
where (myTimestamp >= '2020-09-23'::timestamp and
       myTimestamp < '2020-09-24'::timestamp
      ) or
      (myTimestamp >= '2020-08-24'::timestamp and
       myTimestamp < '2020-08-25'::timestamp
      )

This is more index friendly.

Or if you prefer:

select * 
from temptable tt join
     (values ('2020-09-23'::timestamp), ('2020-08-24'::timestamp)
     ) v(ts)
     on myTimestamp >= v.ts and
        myTimestamp < v.ts + interval '1 day';

Upvotes: 1

VBoka
VBoka

Reputation: 9083

So, as it has been already commented, all you need is to replace and with or

select * 
from temptable 
where myTimestamp::date = date '2020-09-23' 
or myTimestamp::date = date '2020-08-24';

Here is a demo:

DEMO

Upvotes: 1

Related Questions