Time Data Field not quite working.

justride · Jan 3, 2005

I have a mysql database with field type "TIME" for the days of the week, both open and close for all 7 days.
my table looks like HOURSSUNSTART (TIME) HOURSSUNSTOP (TIME) ...

I have a form thats generated in PHP with the hours in regular time format such as 12:00 AM and 12 PM, before inserting a record to the db I call a function which converts 12:00AM to 00:00:00 and so on.

However my values are off in the db, they are all off by + or - 12 hours. I know my function in PHP works fine because I tested it and the right values were being generated. Is there something that has to be done before enter a time into mysql, do we have to specify its 24 hour format.

It seems liek my function is wrong but it works fine.

Oh yea, if I sub in a hard coded time like 23:00:00 instead of a dynamic value then it goes into the db just fine.

this is really weird.

Here is the function:

Code:

function convert_time($time){
							
							switch (trim($time)) {
							
								case "12:00 AM":
									return "00:00:00";
									break;
									
								case "12:30 AM":
									return "00:30:00";
									break;
									
								case "1:00 AM":
									return "01:00:00";
									break;
									
								case "1:30 AM":
									return "01:30:00";
									break;
								
								case "2:00 AM":
									return "02:00:00";
									break;
								
								case "2:30 AM":
									return "02:30:00";
									break;
								
								case "3:00 AM":
									return "03:00:00";
									break;
									
								case "3:30 AM":
									return "03:30:00";
									break;
									
								case "4:00 AM":
									return "04:00:00";
									break;
								
								case "4:30 AM":
									return "04:30:00";
									break;
								
								case "5:00 AM":
									return "05:00:00";
									break;
								
								case "5:30 AM":
									return "05:30:00";
									break;
									
								case "6:00 AM":
									return "06:00:00";
									break;
									
								case "6:30 AM":
									return "06:30:00";
									break;
								
								case "7:00 AM":
									return "07:00:00";
									break;
									
								case "7:30 AM":
									return "07:30:00";
									break;
								
								case "8:00 AM":
									return "08:00:00";
									break;
								
								case "8:30 AM":
									return "08:30:00";
									break;
								
								case "9:00 AM":
									return "09:00:00";
									break;
									
								case "9:30 AM":
									return "09:30:00";
									break;
									
								case "10:00 AM":
									return "10:00:00";
									break;
									
								case "10:30 AM":
									return "10:30:00";
									break;
									
								case "11:00 AM":
									return "11:00:00";
									break;
								
								case "11:30 AM":
									return "11:30:00";
									break;
									
								case "12:00 PM":
									return "12:00:00";
									break;
									
								case "12:30 PM":
									return "12:30:00";
									break;
									
								case "1:00 PM":
									return "13:00:00";
									break;
									
								case "1:30 PM":
									return "13:30:00";
									break;
									
								case "2:00 PM":
									return "14:00:00";
									break;
									
								case "2:30 PM":
									return "14:30:00";
									break;
									
								case "3:00 PM":
									return "15:00:00";
									break;
									
								case "3:30 PM":
									return "15:30:00";
									break;
								
								case "4:00 PM":
									return "16:00:00";
									break;
								
								case "4:30 PM":
									return "16:30:00";
									break;
									
								case "5:00 PM":
									return "17:00:00";
									break;
								
								case "5:30 PM":
									return "17:30:00";
									break;
								
								case "6:00 PM":
									return "18:00:00";
									break;
								
								case "6:30 PM":
									return "18:30:00";
									break;
								
								case "7:00 PM":
									return "19:00:00";
									break;
								
								case "7:30 PM":
									return "19:30:00";
									break;
								
								case "8:00 PM":
									return "20:00:00";
									break;
								
								case "8:30 PM":
									return "20:30:00";
									break;
								
								case "9:00 PM":
									return "21:00:00";
									break;
								
								case "9:30 PM":
									return "21:30:00";
									break;
								
								case "10:00 PM":
									return "22:00:00";
									break;
								
								case "10:30 PM":
									return "22:30:00";
									break;
								
								case "11:00 PM":
									return "23:00:00";
									break;
								
								case "11:30 PM":
									return "23:30:00";
									break;
                                             
							}
						}

and here is where I write the query

Code:

"INSERT INTO `HOURS` (`HOURSSUNSTART` , `HOURSSUNSTOP` , 
		      `HOURSMONSTART` , `HOURSMONSTOP` ,   

`HOURSTUESSTART` ,`HOURSTUESSTOP` ,         `HOURSWEDSTART` , `HOURSWEDSTOP` , 
`HOURSTHURSTART` , `HOURSTHURSTOP` , `HOURSFRISTART` ,
`HOURSFRISTOP` , `HOURSSATSTART` , `HOURSSATSTOP` ,) 
							   VALUES ('convert_time($restaurantfields[20]', 
        'convert_time($restaurantfields[21])',
	'convert_time($restaurantfields[22])',
        'convert_time($restaurantfields[23])',
        'convert_time($restaurantfields[24])',
	'convert_time($restaurantfields[25])', 
        'convert_time($restaurantfields[26])',
        'convert_time($restaurantfields[27])',
	'convert_time($restaurantfields[28])',
        'convert_time($restaurantfields[29])',
        'convert_time($restaurantfields[30])',
        'convert_time($restaurantfields[31])',
        'convert_time($restaurantfields[32])', 
        'convert_time($restaurantfields[33])')";

restaurantfields is an array containing strings of "12:00 AM" or "12:00PM" etc...

kenrbnsn · Jan 3, 2005

Why are you doing it this way??? Just use the date() and strtotime() functions?

Code:

$q = "INSERT INTO `HOURS` (`HOURSSUNSTART` , `HOURSSUNSTOP` ,
              `HOURSMONSTART` , `HOURSMONSTOP` ,   

`HOURSTUESSTART` ,`HOURSTUESSTOP` ,         `HOURSWEDSTART` , `HOURSWEDSTOP` ,
`HOURSTHURSTART` , `HOURSTHURSTOP` , `HOURSFRISTART` ,
`HOURSFRISTOP` , `HOURSSATSTART` , `HOURSSATSTOP` ,)
                               VALUES (";
$tmp = array();
for ($i=20;$i<34;$i++)
   $tmp[] ="'" . date('H:i:00',strtotime($restaurantfields[$i])) . "'";
$q .= implode(',',$tmp) . "')";

Read the PHP manual <

http://www.php.net/>

for more information on the date() and strtotime() functions.

Ken

sleipnir214 · Jan 3, 2005

Or use the MySQL builtin date_format() function.

Want the best answers? Ask the best questions!

TANSTAAFL!!

justride · Jan 3, 2005

This was the first thing that came to mind, i have a tendency of doing things the long way. will your way work? it will read my am and pm signs?

kenrbnsn · Jan 3, 2005

The strtotime() function takes an (almost) english date and/or time and converts it into a number that the date() function can use.

Here's a small test program that proves it:

Code:

<?php
if (isset($_GET['in']))
    $str = $_GET['in'];
else
    $str = 'now';
echo date('H:i:00',strtotime($str))."<br>\n";

Upload this to your server and type

http://your.server.here/pgm.php?in=1:30 PM

Ken

justride · Jan 4, 2005

Thanks for the tip, the values get entered into the db just fine. I even retrieve this way on my update page as well.
Thanks a bunch guys!!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Time Data Field not quite working.

justride

Programmer

kenrbnsn

Technical User

sleipnir214

Programmer

justride

Programmer

kenrbnsn

Technical User

justride

Programmer

Similar threads

Part and Inventory Search

Sponsor