Trying to select all records that match a datetime range.

swares has asked for the wisdom of the Perl Monks concerning the following question:

I have a sql_query plugin for a irc bot. It will perform preconfigured sql queries, I want to extend it so users can specify a time range and query. I need to match time range provided by a user to a series of change records. It needs to match any changes start_time through end_time to the user provided range. The solution may be more of a sql issue. What I have now (not completely tested) will match any change that has a start_time or end_time w/in the user provided range but it will miss any records that both start and end out side the range provided by the user. Input is expected to be in this format () = optional: yr-mo-dd (hr:min:sec) - yr-mo-dd (hr:min:sec) search term (Updated) Using MySql and DBI. Great responses, thanks.

$start_time_field='start_time';
$end_time_field='end_time';
$search = "yr-mo-dd (hr:min:sec) - yr-mo-dd (hr:min:sec) search term";
trim($search); # removes white space and non printables
my $date_field_start = $public_handlers{$cmd}->{'args'}->{'date_field_
+start'};
my $date_field_end = $public_handlers{$cmd}->{'args'}->{'date_field_en
+d'}; 
my $description_field = $public_handlers{$cmd}->{'args'}->{'descriptio
+n_field'}; 
($date1,$date2)=split / - /, $search;
($datetime_start) = $date1 =~ /(\d\d-\d\d-\d\d \(\d\d;\d\d:\d\d\))|(\d
+\d-\d\d-\d\d)/;
($datetime_end) = $date2 =~ /(\d\d-\d\d-\d\d \(\d\d;\d\d:\d\d\))|(\d\d
+-\d\d-\d\d)/;
($search_term) = $date2 =~ /\d\d-\d\d-\d\d \(\d\d;\d\d:\d\d\)(.+)|\d\d
+-\d\d-\d\d (.+)/;
if ($datetime_start !~ /\d\d:\d\d:\d\d/) {$datetime_start = $datetime_
+start . " 00:00:00"};
if ($datetime_end !~ /\d\d:\d\d:\d\d/) {$datetime_end = $datetime_end 
+. " 23:59:59"};
$query = "SELECT * FROM $table WHERE $description_field LIKE '%$search
+_term%' and $start_time_field or $end_time_field between '$datetime_s
+tart' and '$datetime_end' ";
[download]

Comment on Trying to select all records that match a datetime range. Download Code

Replies are listed 'Best First'.
Re: Trying to select all records that match a datetime range. by Errto (Vicar) on Apr 20, 2007 at 21:30 UTC
I would agree that this is an SQL issue. A colleage showed me this trick the other day: `... AND START_TIME < $datetime_end AND END_TIME > $datetime_start` [download] This includes all records that start or end within the period or that span it entirely without the need for OR conditions. Of course, I should make the obligatory comment that you should consider using placeholders - see The fine art of database programming - rather than including the values directly in your SQL string.	[reply] [d/l]
Re: Trying to select all records that match a datetime range. by Zaxo (Archbishop) on Apr 20, 2007 at 22:10 UTC
You appear to have some errors in $query. I think "$start_time" should be "$start_time_field" and "$end_time" "$end_time_field". Perhaps the logic is ok in your dbms, but I'd write that (using placeholders) as, `my $query = <<"ESQL"; SELECT * FROM $table WHERE $description_field LIKE '%$search_term%' and $start_time_field between ? and ? or $end_time_field between ? and ? ESQL` [download] In what follows you may need to apply an epoch time conversion function to $start_time_field and $end_time_field. If your dbms is forgiving and helpful with datetime formats, the following may not be necessary. The surest way is to convert the dates and times to unix epoch time and do numerical comparisons. In Perl, `use Time::Local; # . . .` [download] I'll put your start and end times into an array of (yr,mo,day,hr,min,sec) using a simpler regex, `my @datetime_start = $date1 =~ /\d+/g; $#datetime_start = 5; # expand to six elements, # even if there are only three my @datetime_end = $date2 =~ /\d+/g; $#datetime_end = 5; # expand to six elements, # even if there are only three` [download] Now we can call timelocal() to get the range limits as epoch time, `my $datetime_start = timelocal reverse @datetime_start; my $datetime_end = timelocal reverse @datetime_end;` [download] All that remains is to compose the SQL query. You don't say what dbms you use, but most all will provide an epoch time conversion function for their native datetime type. MySQL has a rich enough set of date and time functions that you don't need to jump through most of those hoops. Its comparison functions work fine with correctly formatted date strings. I think all you would need to do is `$date1 =~ tr/()//d;` and so on. After Compline, Zaxo	[reply] [d/l] [select]
Re: Trying to select all records that match a datetime range. by roboticus (Chancellor) on Apr 21, 2007 at 03:21 UTC
swares: If I understand you correctly, you have several cases you need to cover, like so: `Desired range: +---------------+ Case 1,2: +-1-+ +---2--+ Case 3,4: +----3-----+ +--------4-----------+ Case 5: +---5----+ Case 6: +------------------6----------------+` [download] and you want to return records for cases 3, 4, 5, and 6, but you're not getting case 6. I normally cover these cases by: Look for a cases start time and/or stop time inside the desired range. Look for the desired ranges start and/or end time inside the cases range. That should catch all the cases for you. The SQL code I normally use resembles: `select * from table where (start_time between desired_start and desired_stop) or (end_time between desired_start and desired_end) or (desired_start between start_time and end_time) or (desired_stop between start_time and end_time)` [download] ...roboticus	[reply] [d/l] [select]