Entering a time frame in MySQL

[Kavkan]

Hi all,

I've been trying to enter an appointment time frame in a MySQL DB. So far I've been designating the data as TEXT, but I realize now that when sorting the information it does it alphabetically.. and not time wise.

So is there a built in way to sort such data, for example: 8:00am - 9:00am, 8:30am-10:00, 11:00-12:00pm ?

Or, do I have to write a separate script to convert the data entered by the user into a format acceptable by MySQL as a TIME format?

Also, should I break up the time frame into two parts.. instead of entering 9:00-10:00 in one, make it so 9:00 goes in one and then 10:00 in another?

Any suggestions?

[lorax]

MySQL allows for several date/time functions [dev.mysql.com] in the query strings. Normally, I track a date/time stamp and then do my comparison or filter functions on the query rather than during input. So the way I'd handle your setup is to enter a starttime (could be any valid date/time) and endtime (again - any valid date/time).

[Kavkan]

So, basicually what you are saying is have two separate columns. One for the start time and one for the end time, instead of a single column for the start and end time (ex. 8:00 - 9:00).. This is the best way to do it then?

[Kavkan]

Additionally, I was thinking of possibly writing a function to take the first hour from the string and then convert that into a TIME data type.. possibly inputing it into an invisible column by which I'd sort the records. This would also be approriate I think, but given the fact that putting them into two separate columns would be much easier.. Unless, maybe you or anyone else knows a similar open source script that I could use?

[lorax]

The only reason I'd separate the times into 2 columns is to provide me the maximum flexibility for future calculations. You could put them into the same field and run calculations on them from there but you'd need to translate the data into a usable format if you do.

For example, you could use the 24 hour format and format the text like so: 08:00-14:00. Then when you want to do calculations you could pull the data and split it on the "-".

[rocknbil]

Perhaps I'm not understanding the problem, but you can do sorting if you use the datetime() format:

sort by starttime, endtime asc

Barring that, check out the link provided above for date and time functions, you can also extract portions of a datetime or date string using date_format on the fly, no need to create a new or temporary table.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'

For user input issues, I always use dynamically generated select lists, don't give them the option of entering a typo.

[Kavkan]

To clarify, the issue is that a user may enter something along the lines of: 8.00 - 9:00, or 8:00am - 9:00, or 8.00 - 9.00am or any other similar variation into a single DB column.

So, would date_format work with this? Wouldn't I still need something to break the single text string of "8:00 - 9:00" into just taking the firt hour "8:00", and then date formating on this?

I am not exactly sure, but I guess date_format would do just fine if the user inputs the data into two separate columns like lorax mentioned.. ie. having a coulumn for start time and a different one for end time. ?

[rocknbil]

Well, that would be the way **I** would do it, mostly to make use of existing functions in mySQL and to avoid a series of frustrating and probably complex regexps, splits, and programming math. :-)

When you output anything from this it's pretty easy:

select starttime, endtime from records where rec_id='1234';

print "Your requested appointment is " . $start . '-' $end . '.';

Generating a "proper" date-time format for the initial for as select lists is also pretty easy, a series of nested loops. Here's a perl example, easily ported to anything else. I keep them in two routines for portability, sometimes you don't need a time.

This creates named lists start_date_day, start_date_month,start_date_year,start_time_hour, etc. This message board really hoses up my careful formatting though. :-(

## Get a start and end time drop-down list.
## Pass form object names/ids to routine.

$startDay = &getDateDD('start_date');
$startTime = &getTimeDD('start_time');
$endDay = &getDateDD('end_date');
$endTime = &getTimeDD('end_time');

print qq/
Select begin: $startDay $startTime
Select end: $endDay $endTime
/;

## the subs. $date_start and $date_end are predefined variables to set the length of the year dropdown.

sub getDateDD { 
 my ($moName,$daName,$yrName,$dtString,$ddName); 
$moName= $daName= $yrName=''; 
 ($ddName) = shift(@_); 
 $dtString = qq¦ 
  <select name="$moName" id="$moName"> 
  <option value="">--</option> 
 ¦; 
 for $i (1..12) { 
  if (length($i)<2) { $moTxt = '0' . $i; } 
  else { $moTxt = $i; } 
  $dtString .= qq¦<option value="$moTxt"¦; 
  if ($mm eq $moTxt) { $dtString .= ' selected'; } 
  $dtString .= qq¦>$moTxt</option>\n¦; 
 } 
 $dtString .= qq¦ 
  </select> 
  <select name="$daName" id="$daName"> 
  <option value="">--</option> 
 ¦; 
 for $i (1..31) { 
  if (length($i)<2) { $daTxt = '0' . $i; } 
  else { $daTxt = $i; } 
  $dtString .= qq¦<option value="$daTxt"¦; 
  if ($dd eq $daTxt) { $dtString .= ' selected'; } 
  $dtString .= qq¦>$daTxt</option>\n¦; 
 } 
 $dtString .= qq¦ 
  </select> 
  <select name="$yrName" id="$yrName"> 
  <option value="">--</option> 
 ¦; 
 for $i ($date_start..$date_end) { 
  $dtString .= qq¦<option value="$i"¦; 
  if ($yyyy eq $i) { $dtString .= ' selected'; } 
  $dtString .= qq¦>$i</option>\n¦; 
 } 
 $dtString .= qq¦ 
  </select>\n¦; 
 return $dtString; 
} 
################################### 
## As above, but for time. 
sub getTimeDD { 
 my ($dtString,$i,$ddName,$label,$twodigit); 
$hName=$minName=$secName=''; 
 ($ddName) = shift(@_); 
 $dtString .= qq¦ <span class="small">HH:MM:SS</span> 
  <select name="$hName" id="$hName"> 
  <option value="00">00</option> 
 ¦; 
 for $i (1..24) { 
 if (length($i) < 2) { $twodigit = '0' . $i; } 
  else { $twodigit = $i; } 
 if ($i < 12) { $label = "$i AM"; } 
 elsif ($i == 24) { $label = "12 MN"; $twodigit = '00'; } 
 elsif ($i == 12) { $label = "$i N"; } 
 else { $label = ($i-12) . ' PM'; } 
 $dtString .= qq¦<option value="$twodigit"¦; 
 if ($i == $hr) { $dtString .= ' selected'; } 
 $dtString .= qq¦>$label</option>\n¦; 
 } 
 $dtString .= qq¦ </select> 
  <select name="$minName" id="$minName"> 
  <option value="00">00</option> 
 ¦; 
 for $i (1..59) { 
 if (length($i) < 2) { $twodigit = '0' . $i; } 
  else { $twodigit = $i; } 
 $dtString .= qq¦<option value="$twodigit"¦; 
  if ($i == $min) { $dtString .= ' selected'; } 
  $dtString .= qq¦>$twodigit</option>\n¦; 
 } 
 $dtString .= qq¦ </select> 
  <select name="$secName" id="$secName"> 
  <option value="00">00</option> 
 ¦; 
 for $i (1..59) { 
 if (length($i) < 2) { $twodigit = '0' . $i; } 
  else { $twodigit = $i; } 
 $dtString .= qq¦<option value="$twodigit"¦; 
  if ($i == $sec) { $dtString .= ' selected'; } 
  $dtString .= qq¦>$twodigit</option>\n¦; 
 } 
 $dtString .= qq¦</select>\n¦; 
 return $dtString; 
} 

[Kavkan]

Thanks for the detailed feedback rocknbil! :)

However, I already wrote it by using two columns, one where the user enters the time frame ie. 8:00-9:00. Then I used a JavaScript function to extract the first hour, the start time. After which, I place the start time in a separate column by which I sort the appointments. So far I've ran into a few minor issues with this approach, but will contue testing it.. if I run into more problems, I might have to re do it.