0

I am trying to select records dated 1st of December 2018 from a table. Even though there are several records postgres is not returning any.

enter image description here

Query:

select * 
from dbo."Transactions" 
where "DateOfTransaction"::timestamp >=to_date('01-12-2018', 'dd-mm-yyyy') 
  and "DateOfTransaction"::timestamp <=to_date('01-12-2018', 'dd-mm-yyyy')

I also tried:

 select * 
 from dbo."Transactions" 
where "DateOfTransaction"::timestamp >=to_date('01-12-2018 00:00:00', 'dd-mm-yyyy HH24:MI:SS') 
  and "DateOfTransaction"::timestamp <=to_date('01-12-2018 23:59:59', 'dd-mm-yyyy HH24:MI:SS')

What is the reason for this strange behavior? I have to give date in dd-mm-yyyy format in where condition.

Improve this question
2
  • 1
    You should really avoid those dreaded quoted identifiers. They cause much more trouble than they are worth it. Commented Feb 6, 2019 at 11:30
  • Are you really still using Postgres 9.1? That is no longer supported - you should plan your upgrade to a supported version (e.g. Postgres 11) now. Commented Feb 6, 2019 at 11:31

1 Answer 1

Reset to default
4

As an explanation on why your query isn't working:

to_date('01-12-2018', 'dd-mm-yyyy') creates a date value (even if you specify a time part), so when you compare that with a timestamp it is as if you were comparing that with a timestamp value where the time part is set to 00:00:00.

So your first query is essentially the same as:

where "DateOfTransaction" >= timestamp '2018-12-01 00:00:00'
  and "DateOfTransaction" <= timestamp '2018-12-01 00:00:00'

Which would only return rows that contain exactly 2018-12-01 00:00:00

How to do it correctly?

You could simply cast the timestamp to a date and compare it with =

where "DateOfTransaction"::date = date '2018-12-01'

which would not be able to use an index on "DateOfTransaction"

If you need to make sure that query uses an index, you can use:

where "DateOfTransaction" >= DATE '2018-12-01'
  and "DateOfTransaction" < DATE '2018-12-01' + 1

Note that the cast "DateOfTransaction"::timestamp is useless as the column is already a timestamp

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

4 Comments

Why won't where "DateOfTransaction" >= DATE '2018-12-01' and "DateOfTransaction" < DATE '2018-12-01' work? Why do I need to add 1? Even though I am using >= and <=
Because in both cases the time part would be 00:00:00 - see the condition below "this is essentially the same as" paragraph
Oh I get it now. Thanks a lot. Postgres is weird. I mean if I include time part it should consider it. Shouldn't it ?
@Arbaaz not if you request to create a date. Date values have no time part. If you want to create a timestamp you have to use to_timestamp() or use proper ISO literals as I have done.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.