Postgres date issue

Question

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.

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.

You should really avoid those dreaded quoted identifiers. They cause much more trouble than they are worth it. — user330315
– user330315, 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. — user330315
– user330315, Commented Feb 6, 2019 at 11:31

score 4 · Accepted Answer · 2019-02-06 11:33:05Z

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

edited Feb 6, 2019 at 11:33

answered Feb 6, 2019 at 11:29

user330315

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

4 Comments

SamuraiJack Over a year ago

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 <=

user330315 Over a year ago

Because in both cases the time part would be 00:00:00 - see the condition below "this is essentially the same as" paragraph

SamuraiJack Over a year ago

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 ?

user330315 Over a year ago

@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.

Collectives™ on Stack Overflow

Postgres date issue

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related