creating a series of time periods as rows

Question

I want to write a query that, for any given start date in the past, has as each row a week-long date interval up to the present.

For instance, given the start date of Nov 13th 2010, and the present date of 12-16-2010, I want a result set like

+------------+------------+
| Start      | End        |
+------------+------------+
| 2010-11-15 | 2010-11-21 |
+------------+------------+
| 2010-11-22 | 2010-11-28 |
+------------+------------+
| 2010-11-29 | 2010-12-05 |
+------------+------------+
| 2010-12-06 | 2010-12-12 |
+------------+------------+

It doesn't go past 12 because the week-long period that the present date occurs in isn't complete.

I can't get a foothold on how I would even start to write this query. Can I do this in a single query? Or should I use code for looping, and do multiple queries?

score 2 · Accepted Answer · edited May 23 '17 at 11:53

It's quite difficult (but not impossible) to create such a result set dynamically in MySQL as it doesn't yet support any of recursive CTEs, CONNECT BY, or generate_series that I would use to do this in other databases.

Here's an alternative approach you can use.

Create and prepopulate a table containing all the possible rows from some date far in the past to some date far in the future. Then you can easily generate the result you need by querying this table with a WHERE clause, using an index to make the query efficient.

The drawbacks of this approach are quite obvious:

It takes up storage space unnecessarily.
If you query outside of the range that you populated your table with you won't get any results, which means that you will either have to populate the table with enough dates to last the lifetime of your application or else you need a script to add more dates every so often.

See also this related question:

How do I make a row generator in MySQL

score 0 · Answer 2 · answered Dec 16 '10 at 22:03

Beware this is just a concept idea: I do not have a mysql installation right here, so that I cannot test it.

However I would base myself on a table containing the integers, in order to emulate a series.

Something like :

CREATE TABLE integers_table
(
  id integer primary key
);

Followed by (warning, this is pseudo code)

  INSERT INTO integers_table(0…32767);

(that should be enough weeks for the rest of our lives :-)

Then

FirstMondayInUnixTimeStamp_Utc= 3600 * 24 * 4
SecondPerday=3600 * 24

(since 1 jan 1970 was a thursday. Beware I did not cross check! I might be off a few hours!)

And then

CREATE VIEW weeks
AS 
  SELECT integers_table.id AS week_id,
  FROM_UNIXTIME(FirstMondayInUnixTimeStamp_Utc  + week_id * SecondPerDay * 7) as week_start
  FROM_UNIXTIME(FirstMondayInUnixTimeStamp_Utc + week_id * SecondPerDay * 7 + SecondPerDay * 6) as week_end;

creating a series of time periods as rows

2 Answers2