3

Hi I want to make dynamic times series using generate_series in Postgresql.

For example, I've got a set of random date list:

cleaning date
2015-03-01 00:00
2015-05-31 00:00
2015-06-13 00:00

and I want to dynamically split up the date period by these cleaning dates.

The result will be like this:

start date         end date
0000-00-00 00:00   2015-03-01 00:00
2015-03-01 00:00   2015-05-31 00:00
2015-05-31 00:00   2015-06-13 00:00
2015-06-13 00:00   (current_timestamp)

I've got no idea how I set dynamic interval in generate_series... Is that possible by generate_series?

1
  • 2
    0000-00-00 00:00 is an invalid date Commented Mar 30, 2016 at 11:03

1 Answer 1

1

I really do not see a point of using generate_series.

To get result that you proposed you could use ROW_NUMBER and self-join:

CREATE TABLE tab(cleaning timestamp);

INSERT INTO tab(cleaning)
VALUES ('2015-03-01 00:00'), ('2015-05-31 00:00'), ('2015-06-13 00:00');

Main query:

WITH cte AS
(
   SELECT cleaning, ROW_NUMBER() OVER(ORDER BY cleaning) AS rn
   FROM tab
)
SELECT COALESCE(c1.cleaning,'0001-01-01'::timestamp) AS start_date,
       COALESCE(c2.cleaning,CURRENT_TIMESTAMP) AS end_date
FROM cte c1
FULL JOIN cte c2
  ON c1.rn = c2.rn-1
ORDER BY start_date;

SqlFiddleDemo

Output:

╔═══════════════════════════╦═════════════════════════╗
║        start_date         ║        end_date         ║
╠═══════════════════════════╬═════════════════════════╣
║ January, 01 0001 00:00:00 ║ March, 01 2015 00:00:00 ║
║ March,   01 2015 00:00:00 ║ May,   31 2015 00:00:00 ║
║ May,     31 2015 00:00:00 ║ June,  13 2015 00:00:00 ║
║ June,    13 2015 00:00:00 ║ March, 30 2016 11:03:38 ║
╚═══════════════════════════╩═════════════════════════╝

EDIT:

Another possibility is to use LAG/LEAD windowed functions:

SELECT COALESCE(LAG(cleaning) OVER(ORDER BY cleaning), '0001-01-01'::timestamp) 
       AS start_date
      ,cleaning AS end_date
FROM tab
UNION ALL
SELECT MAX(cleaning), CURRENT_TIMESTAMP
FROM tab
ORDER BY start_date;

SqlFiddleDemo2

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.