Postgresql selects a range of months

Question

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

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

Since each month starts 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 problem is with enddate. I need to manually calculate the last date for this month, taking into account all factors, such as the length of the month, leap year, etc., because the request fails if the specified date is invalid. So currently I'm 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 a way to avoid this correct calculation of the end date?

Explanation

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

Answer 1

This is a very common need for reporting environments. I created several functions to accommodate these date manipulations.

 CREATE OR REPLACE FUNCTION public.fn_getlastofmonth ( date ) RETURNS date AS $body$ begin return (to_char(($1 + interval '1 month'),'YYYY-MM') || '-01')::date - 1; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100; 

Then you can use ...

 WHERE date >= '2012-01-01' AND date < fn_getlastofmonth('2013-04-01') 

Answer 2

It is good to avoid BETWEEN for comparing date ranges. It is better to use >= and < , since it works the same with columns / values ​​of date and date / time.

One way (if you can build dates from outside):

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

You can also use date arithmetic:

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

or OVERLAPS :

 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 here explains why OVERLAPS works as follows:

Each time period is considered a half-open period start <= time <end, unless the beginning and end are equal, in which case it represents a one-time moment. This means, for example, that two time periods with a single common point do not overlap.

Answer 3

I have not tried this, but it is worth doing

 SELECT * FROM some_table WHERE some_date BETWEEN '2012-01-01' AND date('2013-04-01') - integer '1' 

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

Answer 4

All of the above answers provide a working solution, but not in whole or in part. Since I was only looking for an SQL solution (no function), I am combining the best tips from the solutions above.

Perfect solution for my question:

 SELECT * FROM table WHERE date >= '2012-01-01' AND date < date('2013-04-01') + interval '1 month' 

EDIT

I do not use the overlap function here because I pass the default date values ​​for the start and end as "era" and "now." If the user has not specified any time range, the request will look like this:

 SELECT * FROM table WHERE date >= 'epoch' AND date < 'now' 

The overlap function cannot handle "epoch" and "now" and gives an SQL error, while the above code works fine for both cases.

PS: I supported all the answers that were correct in some way and led me to this decision.

Answer 5

 SET search_path=tmp; DROP TABLE zdates; CREATE TABLE zdates ( zdate timestamp NOT NULL PRIMARY KEY , val INTEGER NOT NULL ); -- some data INSERT INTO zdates(zdate,val) SELECT s, 0 FROM generate_series('2012-01-01', '2012-12-31', '1 day'::interval ) s ; UPDATE zdates SET val = 1000 * random(); DELETE FROM zdates WHERE random() < 0.1; -- CTE to round the intervals down/up to the begin/end of the month WITH zope AS ( SELECT date_trunc('month', zdate)::date AS zbegin , date_trunc('month', zdate+interval '1 month')::date AS zend , val AS val FROM zdates ) SELECT z.zbegin , z.zend , COUNT(*) AS zcount , SUM(val) AS zval FROM zope z GROUP BY z.zbegin, z.zend ORDER BY z.zbegin, z.zend ; 

RESULT:

 CREATE TABLE INSERT 0 366 UPDATE 366 DELETE 52 zbegin | zend | zcount | zval ------------+------------+--------+------- 2012-01-01 | 2012-02-01 | 28 | 13740 2012-02-01 | 2012-03-01 | 28 | 14923 2012-03-01 | 2012-04-01 | 26 | 13775 2012-04-01 | 2012-05-01 | 25 | 11880 2012-05-01 | 2012-06-01 | 25 | 12693 2012-06-01 | 2012-07-01 | 25 | 11082 2012-07-01 | 2012-08-01 | 26 | 13254 2012-08-01 | 2012-09-01 | 28 | 13632 2012-09-01 | 2012-10-01 | 28 | 16461 2012-10-01 | 2012-11-01 | 23 | 12622 2012-11-01 | 2012-12-01 | 24 | 12554 2012-12-01 | 2013-01-01 | 28 | 14563 (12 rows)