How to compare dates in datetime fields in Postgresql

Question

I have been facing a strange scenario when comparing dates in postgresql version 9 2 4 in windows    I have a column in my table say update date with type  timestamp without timezone    Client can search over this field with only date  i e  2013-05-03  or date with time  i e  2013-05-03 12 20 00   This column has the value as timestamp for all rows currently and have the same date part 2013-05-03  but difference in time part  When I m comparing over this column  I m getting different results  Like the followings  select   from table where update date  gt    2013-05-03  AND update date  lt    2013-05-03  - gt  No results  select   from table where update date  gt    2013-05-03  AND update date  lt   2013-05-03  - gt  No results  select   from table where update date  gt    2013-05-03  AND update date  lt    2013-05-04  - gt  results found  select   from table where update date  gt    2013-05-03  - gt  results found  My question is how can I make the first query possible to get results  I mean why the 3rd query is working but not the first one

User · Answer

Use Date convert to compare with date  Try This   select   from table  where TO DATE to char timespanColumn  YYYY-MM-DD    YYYY-MM-DD     to timestamp  2018-03-26    YYYY-MM-DD

User · Answer

Use the range type  If the user enter a date   select   from table where     update date      lt       tsrange  2013-05-03    2013-05-03   date   1           If the user enters timestamps then you don t need the   date   1 part  http   www postgresql org docs 9 2 static rangetypes html  http   www postgresql org docs 9 2 static functions-range html

User · Answer

When you compare update date  gt    2013-05-03  postgres casts values to the same type to compare values  So your  2013-05-03  was casted to  2013-05-03 00 00 00     So for update date    2013-05-03 14 45 00  your expression will be that    2013-05-03 14 45 00   gt    2013-05-03 00 00 00  AND  2013-05-03 14 45 00   lt    2013-05-03 00 00 00    This is always false  To solve this problem cast update date to date   select   from table where update date  date  gt    2013-05-03  AND update date  date  lt    2013-05-03  - gt  Will return result

User · Answer

Nicolai is correct about casting and why the condition is false for any data   i guess you prefer the first form because you want to avoid date manipulation on the input string  correct   you don t need to be afraid   SELECT   FROM table WHERE update date  gt    2013-05-03   date AND update date  lt    2013-05-03   date    1 day   interval

[sql] How to compare dates in datetime fields in Postgresql?

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to date