Reputation: 164
I 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
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