Welcome to WebmasterWorld Guest from 34.231.247.139

Forum Moderators: open

Message Too Old, No Replies

Entering a time frame in MySQL

     
4:43 pm on May 13, 2008 (gmt 0)

New User

10+ Year Member

joined:May 12, 2006
posts:21
votes: 0


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?

4:47 pm on May 13, 2008 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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).
5:58 pm on May 13, 2008 (gmt 0)

New User

10+ Year Member

joined:May 12, 2006
posts:21
votes: 0


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?
6:01 pm on May 13, 2008 (gmt 0)

New User

10+ Year Member

joined:May 12, 2006
posts:21
votes: 0


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?
6:34 pm on May 13, 2008 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7577
votes: 4


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 "-".

4:30 pm on May 14, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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.

4:38 pm on May 14, 2008 (gmt 0)

New User

10+ Year Member

joined:May 12, 2006
posts:21
votes: 0


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. ?

5:34 pm on May 15, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


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;
}
10:29 pm on May 18, 2008 (gmt 0)

New User

10+ Year Member

joined:May 12, 2006
posts:21
votes: 0


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members