PostgreSQL- Filter a date range

Your solution is fine. If the dates are literals, I’d prefer, though:

WHERE datefield >= '2010-01-01 00:00:00' 
 AND  datefield <  '2012-01-01 00:00:00'

This performs exactly the same, but is more maintenable, because it makes clear the point of each literal “date” being a timestamp, not a date. For example, suppose sometime someone changes your query to the following

    AND  datefield <=  '2012-01-01'

… expecting (and failing) to include the full day “2012-01-01” in the query. With the later syntax, the intention is more clear and this confusion is prevented.

To make it even more clear (perhaps too verbose), you can do the explicit cast:

WHERE datefield >= '2010-01-01 00:00:00'::timestamp 
 AND  datefield <  '2012-01-01 00:00:00'::timestamp

I wouldn’t use to_date() here for similar reasons (potential datatype confusion), nor to_timestamp() (it returns a timestamptz).

BTW, I’ve modified the case to comply with recommended practice (keywords in uppercase, identifiers in lowercase)

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)