Postgresql select between month range

Question

I have table with one of the columns as date in 'YYYY-MM-DD' format. Can I use select to get all data in a monthly range? Say I want all the data from 2012-01-xx to 2013-04-xx. So I am basically looking for a SQL query like the one given below:

SELECT * FROM table WHERE date IN BETWEEN '2012-01' AND '2013-04' (INVALID QUERY)

Since every month begin with '01' I can modify the above query to adjust the start condition.

SELECT * FROM table WHERE date IN BETWEEN '2012-01-01' AND '2013-04' (INVALID QUERY)

Now the issue comes with the enddate. I have to manually calculate the last date for the given month, taking all factors in account like length of the month, leap year etc., as the query fails if the given date is invalid. So currently I am doing something like this:

SELECT * FROM table WHERE date IN BETWEEN '2012-01-01' AND 'VALID_MONTH_END_DATE' (VALID Query)

I want to know if there is any way to avoid this valid end date calculation?

Clarification

I have thought above the first day of the next month, but even then I'll have to apply some logic say, if its December, the next month would be January of next year. I wanted to know if a SQL only solution is possible?

Answer 1

It's good to avoid BETWEEN for date range comparisons. Better use >= and < as it works equally with date and datetime columns/values.

One way (if you can build the dates externally):

WHERE date >= DATE '2012-01-01' 
  AND date < DATE '2013-05-01'      --- first date of the next month

You could also use date arithmetic:

WHERE date >= DATE '2012-01-01' 
  AND date < DATE ('2013-04-01' + INTERVAL '1 MONTH')

or the OVERLAPS operator:

WHERE (date, date) OVERLAPS
      (DATE '2012-01-01', DATE '2013-05-01')

You should also read the Postgres documentation: Date/Time Functions and Operators

The manual explains here why OVERLAPS works this way:

Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.