PostgreSQL- Filter a date range

I'm a SQL developer and spend most of my time in MSSQL.
I'm looking for a better way to filter a "Timestamp without timezone" field in a PostgreSQL DB.

I'm using:

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

But given that I'm not an expert at the syntax I have to think there's a better way.

Any Suggestions? Thanks.

For date 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 explicit date format.

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

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

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)

You can keep the query simple by using `BETWEEN` as long as your column name is of type `TIMESTAMP` and your column name isn't "timestamp"...

`SELECT * FROM table WHERE column BETWEEN '2018-12-30 02:19:34' AND '2018-12-30 02:25:34'`

This works for dates '2018-12-30' and date-times '2018-12-30 02:19:34'.

I would agree with leonbloy that using this would make the code more readable and clear.

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

You can also use interval if you want to filter for months or days etc. like this.
datefield < current_date + interval '1 months'