Ugh. My perl isn't quite right there. Here's a bit better (e.g.
working) example:
If you create the table, then put this in "populate_hours.pl":
#### BEGIN
#!/usr/bin/perl
$counter = 0;
while ($counter < 1000000) {
print "INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD('2000-01-01 00:00:00', INTERVAL $counter HOUR) );\n";
$counter++;
}
#### END
then run
./populate_hours.pl | mysql -h host -u user -ppassword -D database
you'll have a table full of hours.
Dan
On 1/10/07, Dan Buettner <[EMAIL PROTECTED]> wrote:
One of the most puzzling and challenging things to do with SQL can be
to show what's NOT there, as you're trying to do.
Many people opt to do such a report in their favorite scripting
language for this reason, as one can easily increment timestamps by a
given amount and re-do the query. Can be resource intensive to re-do
the queries for each hour or whatever, but it's often pretty easy.
Another option is to create a table used specifically for joining to
get units of time with no corresponding entries in the other table.
You could create a table like so:
CREATE TABLE all_hours (
date_hour DATETIME,
KEY (date_hour) );
then populate it like so, with perl:
$counter = 0;
while $counter < 1000000 {
print "INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD("2000-01-01 00:00:00", INTERVAL $counter HOUR) );"
$counter++;
}
Then you can join on that table.
A million hour entries would be good for 114 years or so. Fewer would
likely give somewhat better performance.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
