Selecting rows with date between start and end date

Question

I have a timestampz column in postgresql table and I wish to retrieve entries lying within a specific time frame

I tried using

SELECT * FROM table where date BETWEEN '2012-01-01' AND '2012-01-05'

But the above query fails to retrieve some rows that fall within the specified date, so I tried something like this

SELECT * FROM table WHERE date BETWEEN '2012-01-01 00:00:00' AND '2012-01-05 23:59:59'

This works fine, but Is there a better way to active this?

Answer 1

Not much better but cleaner i think.

select *
from table
where 
    date >= '2012-01-01' 
    and 
    date < '2012-01-05'::date + 1

It will cover up to '2012-01-05 23:59:59.999999'

Answer 2

You can try:

SELECT * 
FROM table 
where date::DATE BETWEEN '2012-01-01'::DATE AND '2012-01-05'::DATE

SQL Fiddle