1

I'm using postgres and I have this record that has the date_time column:

"2015-09-01 09:41:15"

When I do a psql query from pgadmin:

select * from table where date_time >= '2015-08-31' and date_time <= '2015-09-01'

This specific record does not show up even though I used = sign. If i use <= '2015-09-02' it shows up. Is there a way for the record to show up in a query using <= '2015-09-01' since its = to the time queried?

My time column:

date_time timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone,
Arnold Cristobal
  • 843
  • 2
  • 16
  • 36

1 Answers1

1

You're forgetting the time. You could either cast your date_time field to date for the comparison (there's a performance toll, it can be very slow in large datasets):

select * from table 
where date_time >= '2015-08-31' and date_time::date <= '2015-09-01'

or you can cast your starting and ending dates to timestamp:

select * from table 
where date_time >= '2015-08-31' and date_time < ('2015-09-01'::date+1)::timestamp
Ezequiel Tolnay
  • 4,302
  • 1
  • 19
  • 28
  • 1
    this fixed my problem. Thank you. – Arnold Cristobal Apr 27 '16 at 07:32
  • You may add straight explanation why this happens. And it's so because column is `timestamp without time zone` so string `'2015-09-01'` used for comparision is by default cast to `timestamp without time zone` too. In that case `PostgreSQL` set time do midnight so `'2015-09-01'` becomes `'2015-09-01 00:00:00':timestamp without time zone`. – Gabriel's Messanger Apr 27 '16 at 09:47