query two specific dates in postgresql

Question

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

Think about your query: you are requesting all rows where the column contains two different values at the same time - this will never be true. You want OR, not AND. When you use OR you only see one row, because there is no row for 2020-08-24 (only for 2020-09-24) — user330315
– user330315, Commented Nov 12, 2020 at 14:25
Hi @codeheadache In your data from your question there is only '2020-09-23' there is no '2020-08-24'. So when you add or you see only one row... — VBoka
– VBoka, Commented Nov 12, 2020 at 14:26
I have updated the insert statements. I want to list out the two dates that i have queried — codeheadache
– codeheadache, Commented Nov 12, 2020 at 14:30

Gordon Linoff · Accepted Answer · 2020-11-12 14:35:56Z

1

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';

answered Nov 12, 2020 at 14:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codeheadache Over a year ago

The query with the in KEYWORD helped me. Thank you

VBoka · Accepted Answer · 2020-11-12 14:30:50Z

1

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

answered Nov 12, 2020 at 14:30

VBoka

9,0933 gold badges19 silver badges27 bronze badges

4 Comments

codeheadache Over a year ago

Thank you for the response. I tried exactly the same as your query. But i am getting different output on pgadmin. There is nothing listed. Is this a bug?

VBoka Over a year ago

What do you @codeheadache get with this: select myTimestamp::date from temptable Do you get same results as me : dbfiddle.uk/…

codeheadache Over a year ago

Yes. For the query select myTimestamp::date from temptable i get the same output as yours.However, for the query in my question, the output is blank, as shown in the picture. For the time being however, i think Gordon solution helped. Thank you for your response

VBoka Over a year ago

OK, @codeheadache, but you told in your question when you add OR operator you can see only one date. Then you have corrected the data in your table... Then you said that with OR operator you can not see anything ? Is that true ?

Collectives™ on Stack Overflow

query two specific dates in postgresql

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related