Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Generate series of week intervals for given month

In a Postgres 9.1 database, I am trying to generate a series of weeks for a given month but with some constraints. I need all weeks to start on Monday and get cut when they start or end in another month.

Example:

For February, 2013 I want to generate a series like this:

         start
------------------------
2013-02-01 00:00:00+00
2013-02-04 00:00:00+00
2013-02-11 00:00:00+00
2013-02-18 00:00:00+00
2013-02-25 00:00:00+00

The query that I have now looks like this:

SELECT GREATEST(date_trunc('week', dates.d),
                date_trunc('month',dates.d)) as start
FROM generate_series(to_timestamp(1359676800),to_timestamp(1362095999), '1 week') as dates(d)

This query gets me the first 4 weeks but it's missing the week from the 25th. Is it possible to get the last week?

like image 855
Eddie Avatar asked Mar 15 '13 16:03

Eddie


2 Answers

select
    greatest(date_trunc('week', dates.d), date_trunc('month',dates.d)) as start
from generate_series('2013-02-01'::date, '2013-02-28', '1 day') as dates(d)
group by 1
order by 1
like image 84
Clodoaldo Neto Avatar answered Nov 18 '22 19:11

Clodoaldo Neto


SELECT generate_series(date_trunc('week', date '2013-02-01' + interval '6 days')
                     , date_trunc('week', date '2013-02-01' + interval '1 month - 1 day')
                     , interval '1 week')::date AS day
UNION  SELECT date '2013-02-01'
ORDER  BY 1;

This variant does not need a subselect, GREATEST or GROUP BY and only generates the required rows. Simpler, faster. It's cheaper to UNION one row.

  • Add 6 days to the first day of the month before date_trunc('week', ...) to compute the first Monday of the month.

  • Add 1 month and subtract 1 day before date_trunc('week', ...) to get the last Monday of the month.
    This can conveniently be stuffed into a single interval expression: '1 month - 1 day'

  • UNION (not UNION ALL) the first day of the month to add it unless it's already included as Monday.

  • Note that date + interval results in timestamp, which is the optimum here. Detailed explanation:

    • Generating time series between two dates in PostgreSQL

Automation

You can provide the start of the date series in a CTE:

WITH t(d) AS (SELECT date '2013-02-01')  -- enter 1st of month once
SELECT generate_series(date_trunc('week', d + interval '6 days')
                     , date_trunc('week', d + interval '1 month - 1 day')
                     , interval '1 week')::date AS day
FROM   t
UNION  SELECT d FROM t
ORDER  BY 1;

Or wrap it into a simple SQL function for convenience with repeated calls:

CREATE OR REPLACE FUNCTION f_week_starts_this_month(date)
  RETURNS SETOF date AS
$func$
SELECT generate_series(date_trunc('week', $1 + interval '6 days')
                     , date_trunc('week', $1 + interval '1 month - 1 day')
                     , interval '1 week')::date AS day
UNION
SELECT $1
ORDER  BY 1
$func$  LANGUAGE sql IMMUTABLE;

Call:

SELECT * FROM f_week_starts_this_month('2013-02-01');

You would pass the date for the first day of the month, but it works for any date. You the first day and all Mondays for the following month.

like image 15
Erwin Brandstetter Avatar answered Nov 18 '22 19:11

Erwin Brandstetter