postgres select query on time column generate output wrong

Question

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,

Ezequiel Tolnay · Accepted Answer · 2016-04-27 06:48:10Z

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

answered Apr 27, 2016 at 6:48

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

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

2 Comments

Arnold Cristobal Over a year ago

this fixed my problem. Thank you.

Gabriel's Messanger Over a year ago

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.

Collectives™ on Stack Overflow

postgres select query on time column generate output wrong

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related