'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.

postgresqldate-range


Solution 1:[1]

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')

Solution 2:[2]

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'.

Solution 3:[3]

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

Solution 4:[4]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ihor Romanchenko
Solution 2 Phil Andrews
Solution 3 Anish Jain
Solution 4 shyam yadav

Related Questions