Re: MySQL query question...
by CountZero (Bishop) on Aug 09, 2005 at 20:00 UTC
|
Why would you want to solve that problem in SQL?If you are already using Perl, modules such as Date::Calc and more in particular Date::Calc::Iterator seems so much more appropriate to solve this problem.
CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law
| [reply] |
|
Perhaps because doing it in SQL, you can take advantage of the indexes and groupings the database provides, rather than having to fetch the whole set and then discard data in your script ? For instance, there's 1000 matches for any day within the range, however you want to display them 25 items at a time - do you fetch 1000 items each time, throwing away an unknown amount of records to find the first 25, then do the same on the second page request for the next 25 ? Or would it be more elegant to just ask the database 'records 26-50 matching these criteria, please ?' ?
| [reply] [d/l] |
|
| [reply] |
Re: MySQL query question...
by bpphillips (Friar) on Aug 09, 2005 at 20:10 UTC
|
You could use some of the DateTime modules for this kind of thing (you'd need to do something a bit different to make the recurrence logic jump from one Wednesday to the next):
use strict;
use DateTime::Set;
use DateTime::Format::MySQL;
my ($d1,$d2) = (DateTime->now(),DateTime->now()->add(days=>7));
my $set = DateTime::Set->from_recurrence(
after => $d1,
before => $d2,
recurrence => sub {
$_[0]->truncate(to=>'day')->add(days=>1)
},
);
my @dates = map {
DateTime::Format::MySQL->format_date($_)
} $set->as_list;
# @dates now contains a list of MySQL looking date strings
I've also done the same thing in the past by using MySQL's from_days() function. This "works" but is probably not recommended
# non-production-ready code follows
my ($start_day,$end_day) = $dbh->selectrow_array("select to_days(now()
+), to_days(now() + interval 7 day)");
my @dates = map {
$dbh->selectrow_array("select from_days($_)")
} $start_day .. $end_day;
| [reply] [d/l] [select] |
Re: MySQL query question...
by scmason (Monk) on Aug 09, 2005 at 19:57 UTC
|
I have been working on the same problem ( in QT-C++ though). I decided that the best method would be to make an iterator that would generate each date between start date and end date . This is very close to what you are proposing, except that I would dynamically generate the dates because I do not see the feasbility of generating a table that contains all the dates from some_past_date to infinity (which is how long I hope people use my application:)
I am glad you ask the question though, because I am interested in hearing peoples opinions.
"Never take yourself too seriously, because everyone knows that fat birds dont fly" -FLC
| [reply] |
|
| [reply] |
Re: MySQL query question...
by shiza (Hermit) on Aug 09, 2005 at 21:53 UTC
|
MySQL has a ton of Date and Time Functions. I do not understand why you need a separate table to store dates in though. | [reply] |
|
I agree. To use SQL to select every Wednesday between date X and date Y ought to be trivial. Something like
SELECT * FROM your_table
WHERE
your_date_column BETWEEN X and Y
AND
DAYOFWEEK(your_date_column) = 4
(untested) should do it.
($_='kkvvttuu bbooppuuiiffss qqffssmm iibbddllffss')
=~y~b-v~a-z~s; print
| [reply] [d/l] |
|
Well, that does do it, but you still need a table (your_table) containing a list of dates. What I would like to do is SELECT dates from a table that doesn't really exist. That way I don't have to maintain a table of dates. Is there a way to select dates from a sort of phantom table in MySQL?
| [reply] |
|
|
Re: MySQL query question...
by McDarren (Abbot) on Aug 16, 2005 at 09:09 UTC
|
Yes, this is a trivial task in mySQL..
And no, you dont need any temporary table...
Here is a simple example (using my own data)
mysql> SELECT send_time, DAYNAME(send_time) AS Day
-> FROM BillArchive
-> WHERE send_time BETWEEN '2005-08-02' AND '2005-08-05'
-> HAVING Day = 'Wednesday'
-> LIMIT 10;
+---------------------+-----------+
| send_time | Day |
+---------------------+-----------+
| 2005-08-03 15:41:40 | Wednesday |
| 2005-08-03 23:08:52 | Wednesday |
| 2005-08-03 16:12:23 | Wednesday |
| 2005-08-03 14:25:40 | Wednesday |
| 2005-08-03 19:50:22 | Wednesday |
| 2005-08-03 20:02:46 | Wednesday |
| 2005-08-03 14:25:40 | Wednesday |
| 2005-08-03 20:02:46 | Wednesday |
| 2005-08-03 22:37:50 | Wednesday |
| 2005-08-03 20:02:46 | Wednesday |
+---------------------+-----------+
10 rows in set (0.09 sec)
- Darren
| [reply] [d/l] |
|
You don't need a temporary table, but you do need a table that actually contains dates to select from. That's easy enough, but what I would really like to do is to select a list of dates from a table that doesn't exist. Similar to how you can SELECT 5+5 and get 10 without selecting from a table. I'd like to be able to say
SELECT date WHERE date BETWEEN start_date AND end_date AND DAY(date)='Wednesday';
| [reply] [d/l] |