1

My SQL is:

SELECT date_trunc('week', '2020-06-01'::timestamp)::date ||'-'|| 
(date_trunc('week', '2020-06-01'::timestamp)+ '6 days'::interval)::date;

However, using this code it shows only one week's output (2020-06-01-2020-06-07). The actual output I want is:

 (2020-06-01-2020-06-07,
 2020-06-08-2020-06-14,
 2020-06-15-2020-06-21,
 2020-06-22-2020-06-28)
1

generate_series() with timestamp arguments will do what you want.

testdb=# select d::date::text||'-'||(d+interval '6 days')::date::text from
generate_series('2020-06-01', '2020-06-01'::date + interval '3 weeks', interval '1 weeks') d;
       ?column?        
-----------------------
 2020-06-01-2020-06-07
 2020-06-08-2020-06-14
 2020-06-15-2020-06-21
 2020-06-22-2020-06-28
(4 rows)

Time: 0.645 ms
1
  • Nice one (+1) - I'll adapt this to my solution to have fewer CTEs! However, I believe that the OP wants the answer as a single string and not separate fields - see my (edited) answer.
    – Vérace
    Jun 5 '20 at 18:55
1

EDIT:

Thanks to the answer by @AdamKG here, it is possible to obtain the exact result desired by the OP in one pass without using CTEs as follows (fiddle available here):

SELECT 
  REPLACE
  (
    '(' ||
    STRING_AGG(d::DATE::TEXT || '-' || (d + INTERVAL '6 days')::DATE::TEXT || ',' , E'\n') 
    || ')', ',)', ')'
  ) AS str
FROM GENERATE_SERIES
     (
       '2020-06-01', '2020-06-01'::DATE + INTERVAL '3 weeks', INTERVAL '1 weeks'
     ) d;

Result:

str
(2020-06-01-2020-06-07,
2020-06-08-2020-06-14,
2020-06-15-2020-06-21,
2020-06-22-2020-06-28)

Et voilà bis.

I'm leaving my original answer intact below for those who wish to follow the logic - it's essentially the same as the query above except it's all done in one step!


Original Answer:

This proved tricky enough to get exactly into the requested format, but I was able to get there eventually! A 9.6 fiddle is available here.

This SQL finally did it:

WITH cte1 AS
(
  SELECT t.d1::DATE
  FROM GENERATE_SERIES
       (
         TIMESTAMP '2020-06-01',
         TIMESTAMP '2020-06-30',
         INTERVAL  '7 DAY'
       ) AS t(d1)
),
cte2 AS
(
  SELECT d1, (d1 + '6 DAY'::INTERVAL)::DATE AS d2
  FROM cte1
  WHERE (d1 + '6 DAY'::INTERVAL)::DATE < '2020-06-30'::DATE
),
cte3 AS
(
  SELECT 
    '(' || 
    STRING_AGG((d1::TEXT) || '-' || (d2::TEXT) || ',', E'\n') 
    || ')' 
    AS date_string1
  FROM cte2
)
SELECT REPLACE(date_string1, ',)', ')') AS date_string FROM cte3;

Result:

date_string
(2020-06-01-2020-06-07,
2020-06-08-2020-06-14,
2020-06-15-2020-06-21,
2020-06-22-2020-06-28)

which is exactly the format requested!

Logic:

I'll go through the logic step by step, since a) I believe that it's helpful to the OP and b) it's helpful for me to reinforce what I've learnt in answering the question (+1 for a thought-provoking quesion BTW). The steps are also outlined in the fiddle!

cte1

Here, I make use of the (incredibly useful) GENERATE_SERIES function. This gets all the Mondays in June - it may be a coincidence that June 2020 starts with a Monday - but anyway, that's what the OP wants - in any case, if it's Mondays from other months that are required, you only have to start from the relevant first Monday. I found this answer very helpful for this section!

SELECT t.d1::DATE
FROM GENERATE_SERIES
     (
       TIMESTAMP '2020-06-01',
       TIMESTAMP '2020-06-30',
       INTERVAL  '7 DAY'
     ) AS t(d1)

Result:

d1
2020-06-01
2020-06-08
2020-06-15
2020-06-22
2020-06-29

cte2

This adds 6 days to the result of cte1 to get the end of the week - i.e. every Sunday in June - in fact, it also picks up the first Sunday in July without the WHERE clause which the OP apparently doesn't want, so I remove it using the WHERE. I take the result of cte1 and simply put the result of the addition of the '6 DAY' beside it:

SELECT d1, (d1 + '6 DAY'::INTERVAL)::DATE AS d2
FROM cte1
WHERE (d1 + '6 DAY'::INTERVAL)::DATE < '2020-06-30'::DATE

SELECT * FROM cte2 gives

d1          d2
2020-06-01  2020-06-07
2020-06-08  2020-06-14
2020-06-15  2020-06-21
2020-06-22  2020-06-28

which are all the dates we need for the OP's requirements.

cte3

In this cte, I use the STRING_AGG function which essentially converts a result set into a .csv list - not normally best practice, but given the number of questions about converting back and forth between .csv, very useful and is the OP's requirement. I also found this post helpful.

There was also the problem of this newline character - I wasn't sure that this was possible, but thanks to my friend Mr. Google, I found this post which explains that one can use either E\n or CHR(10) - I tried both and both work fine - the decision here to use E\n is arbitrary on my part.

I also added the ( & ) brackets at the beginning and end of the STRING_AGG.

SELECT 
  '(' || 
  STRING_AGG((d1::TEXT) || '-' || (d2::TEXT) || ',', E'\n') 
  || ')' 
  AS date_string1
FROM cte2

SELECT * FROM cte3 gives:

date_string1
(2020-06-01-2020-06-07,
2020-06-08-2020-06-14,
2020-06-15-2020-06-21,
2020-06-22-2020-06-28,)

So, we're nearly there! The only "fly in the ointment" is the ',)' at the very end - the OP's requirement is for the string to end 2020-06-28) and not 2020-06-28,), so the very last part of the SQL statement is the use of the REPLACE function to remove this final comma.

SELECT REPLACE(date_string1, ',)', ')') AS date_string FROM cte3;

Et voilà - the result as the OP requested.

p.s. welcome to the forum!

0

Even shorter:

select d::text || '-' || (d + 6)::text
from (
  select date '20200601' + generate_series(0,21,7) d
) x;

Remember

  1. you can add integers to dates to "add days", and the resulting datatype is still a date rather than the timestamp you get when you add an interval to a date
  2. you can specify a step parameter to generate_series

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.