Here is some code from a very old version of a CGI/Mysql timesheet system I wrote. I used both
Date::Manip and SQL date calculation commands. Maybe it will help.
The program basically lets you record how many hours a day you spend on any of your clients, in a view which covers all clients for a 1 week period. Due to user requests, a function was later added which let them log in to past weeks (to make up for unrecorded hours) by selecting a pulldown menu at login time which had items such as "2 weeks ago". So I think your idea was completely valid.
I can tell you that Date::Manip is great, but is also one of the two or three biggest modules on CPAN, if memory matters. As for quoting, maybe DATE_ADD will solve it for you?
Some Date::Manip (mainly used for day of the week type stuff):
$companystartdate = &ParseDate("today"); # returns 2000082415:40:44
+format
$companystartdate =~ s/^(....)(..).+/$1-$2-01/; # build 2000-08-01
Some SQL:
my $statement = "select * from charges where
(UserId='$userhash{'UserId'}' AND
(Date BETWEEN '$start' AND
DATE_ADD('$start', interval 6 day)
)
) order by ClientGroupId,ClientId,Date";
and
my $monthstart = substr($sqlstartdate,0,8) . "01"; # 2000-08-01
my $monthend = "DATE_SUB(
( DATE_ADD('$monthstart',interval 1 month) ),
Interval 1 day)"; # 1mo less 1dy. too many parens?
my $statement="select sum(Hours) from charges where
(UserId='$userhash{'UserId'}'
and ClientId='$client'
and FeeType='$fee'
and Date between '$monthstart' and $monthend
) group by UserId";
By the way regarding list values, why the "world of pain"? Are you creating columns all over the place? You probably know it, but CGI.pm can freeze into a string joined by nulls. Not that DBI might like it any.
Hope this helps.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.