Forum Moderators: coopster
I am having issues reconciling PHP and MySQL time formats. I have a form that asks for event start times and event end times (9:00am, 9:30am, 10:00am,... 7:00pm, 7:30pm, etc.). These hours are selected via a drop-down list. The (partial) code for the list is:
<form id="event_start" name="event_start" method="post" action="">
<label for="event_start">Event Start:</label>
<select name="event_start" id="event_start">
<option><?php $hour = mktime(9, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(9, 30, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(10, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(10, 30, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(11, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(11, 30, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(12, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(12, 30, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(13, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(13, 30, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(14, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(14, 30, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(15, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(15, 30, 0); echo date("g:i a", $hour) ?></option></select>
On the form, the values show up as 9:00am, 9:30am,...3:00pm, 3:00pm - which is exactly what i want. These values are then submitted into a TIME field in my database.
1. However, when I looked at the values in the db, there didn't seem to be any difference between 3:00 and 15:00 (3 AM and 3 PM). And when I queried the db, the output was "3:00:00" in both cases.
2. My next step was to hard code time fields into the db (without the form): 3:00, 15:00, 23:30, etc. However, I could not figure out the PHP code to format these values back into the format of "3:00am", "3:00pm", "11:30pm", etc. The closest I got was to just plain echo the MySQL values from the TIME field, which brought up "3:00:00", "15:00:00", etc.
3. So my question is (A) how do i insert time values which MySQL will recognize while (B) keeping the "3:00am" "3:00pm" format in my drop-down list and also in the results that website visitors will see? ...Also, I don't think storing the values as strings will help because I only want to display events that have not concluded, and I'm not sure I can do that with strings.
I assumed there would be a simple solution to this issue, and there probably is - it's just eluded me so far...
Any and all help appreciated.
Thanks!
I have always found using 24-hour format time to be a heck of a lot easier all round.
Additionally, I use UTC for all storage and convert to local time for display only.
$test1 = mktime(15, 0, 0);
echo "Test 1 time is " . date("g:i a", $test1);
which works fine with just PHP, but doesn't do much for the MySQL values.
Thanks
Using your example, something like:
<?php $hour = mktime(9, 0, 0) ?><option value="<?php echo gmdate('H:i',$hour) ?>"><?php echo date("g:i a", $hour) ?></option>
<?php $hour = mktime(9, 30, 0) ?><option value="<?php echo gmdate('H:i',$hour) ?>"><?php echo date("g:i a", $hour) ?></option>
// setting the values for INSERT into database
$event_start = date("H:i:s", strtotime($_POST['event_start']));
$event_end = date("H:i:s", strtotime($_POST['event_end']));
// displaying the drop-down list to choose a time (this is only partial)
<option><?php $hour = mktime(11, 0, 0); echo date("g:i a", $hour) ?></option>
<option><?php $hour = mktime(15, 0, 0); echo date("g:i a", $hour) ?></option>
// showing the event times with values from the database
echo "The event begins at " . date("g:i a", strtotime($row1['event_start'])) . " and ends at " . date("g:i a", strtotime($row1['event_end'])) . ".";
OUTPUT: The event begins at 11:00 am and ends at 3:00 pm.
Again, I'm only posting the relevant code to this topic. Other pieces are the INSERT and SELECT queries, and a WHILE LOOP. Nothing too crazy with these, but I can post as well, if needed...
Also, I am in in the Central Standard Time Zone, so I set my timezone from GMT to CST (at the beginning of my code). I'm not sure if that was overkill, but here's the code for that:
// setting timezone to CST
date_default_timezone_set('US/Central');
***Thank you again for all your help with this. This is truly the board of webmasters!
If you are using Local Time in your database, you'll hit a snag when you repeat the hour from 1am to 2am when the clocks 'go back' in the autumn.
You also need to find out how your server handles the jump in Spring and Autumn, and adjust your UTC offset accordingly.
I convert all times to UTC for storage in the database all year round, and apply the conversion for display purposes only, using UTC+0000 in Winter and UTC+0100 in Summer for the UK.
You would likely use UTC-0800 in Winter and UTC-0700 in Summer.
Be aware that Southern Hemisphere is on their Daylight Savings from October to March or somesuch, that most equatorial countries don't use DST, and that a few countries use an hh:30 offset to UTC.
