[thelist] tip: generating equidistant dates in an interval

[Matt Warden]

This came from some ingenious guidance from the famous Rudy Limeback
(http://r937.com/).

<tip type="sql" title="generating equidistant dates in an interval"
author="rudy mwarden">

Rudy gave this gem:

create a table of integers [0, 9]:

create table INTEGERS (I integer not null primary key);
insert into INTEGERS (I) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

generate the Cartesian product of the integers table and itself:

select *
  from INTEGERS as units
cross
  join INTEGERS as tens
 where tens.I*10 + units.I between 0 and 30

This will give you something like this:
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
0	1
1	1
2	1
...

Add in a little arithmetic:

select tens.I*10 + units.I as dd
  from INTEGERS as units
cross
  join INTEGERS as tens
 where tens.I*10 + units.I between 0 and 30

0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
...

So, now let's say we want to generate dates which are every 15 minutes
from now until 3 hours from now. Use this idea and modify it slightly:

We can get multiples of 15 like so:

select (tens.I*10 + units.I)*15 as dd
  from INTEGERS as units
cross
  join INTEGERS as tens
 where (tens.I*10 + units.I)*15 between 0 and (3 * 60)

0
15
30
45
60
75
90
105
120
135
150
165
180

So, use this as the argument to date_add() and we have ourselves a winner:

select date_add(now(), interval ((tens.I*10 + units.I)*15) minute) as dd
  from INTEGERS as units
cross
  join INTEGERS as tens
 where ((tens.I*10 + units.I)*15) between 0 and (3 * 60)

2006-04-14 14:07:42.0
2006-04-14 14:22:42.0
2006-04-14 14:37:42.0
2006-04-14 14:52:42.0
2006-04-14 15:07:42.0
2006-04-14 15:22:42.0
2006-04-14 15:37:42.0
2006-04-14 15:52:42.0
2006-04-14 16:07:42.0
2006-04-14 16:22:42.0
2006-04-14 16:37:42.0
2006-04-14 16:52:42.0
2006-04-14 17:07:42.0

And obviously you could modify this technique for any type of
interval, simply by changing the date_add() and between clauses
accordingly.

</tip>



--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.