[thelist] tip: generating equidistant dates in an interval
Matt Warden
mwarden at gmail.com
Fri Apr 14 14:05:49 CDT 2006
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.
More information about the thelist
mailing list