PostgreSQL - filter date range

Question

I am a SQL developer and spend most of my time in MSSQL. I am looking for the best way to filter the "Timestamp without timezone" field in PostGreSQL DB

I use:

Where DateField >= '2010-01-01' and DateField < '2012-01-01' 

But, given that I'm not an expert on syntax, I should think that the best way.

Suggestions? Thanks.

Answer 1

Your decision is in order. If dates are literals, I would prefer:

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

This is done in exactly the same way, but is more amenable to support since it clearly states that each literal date is a timestamp, not a date. For example, suppose someone changes your request to the next

  AND datefield <= '2012-01-01' 

... waiting (and could not) include in the request the full day "2012-01-01". With later syntax, intent becomes clearer, and this confusion is prevented.

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

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

I would not use to_date() here for similar reasons (potential data type confusion) rather than to_timestamp() (it returns timestamptz ).

By the way, I changed the case to fit the recommended practice (keywords in upper case, identifiers in lower case)

Answer 2

For time intervals, you can use something like:

 WHERE DateField BETWEEN to_date('2010-01-01','YYYY-MM-DD') AND to_date('2010-01-02','YYYY-MM-DD') 

It is shorter (you do not need to repeat DateField ) and has an explicit date format.

Within 1 hour / day / month / year you can use:

 WHERE date_trunc('day',DateField) = to_date('2010-01-01','YYYY-MM-DD')