mysql time format, and formatting text entered??

[PAkerly]

Hello,
I've got a PHP web form that users enter time into. Usually the user
enters time as:
1215
1205
215
etc...
Is there a way I can convert these times to:
12:15
12:05
2:15
etc..
or another time format when they are being entered into the DB? Here
is how I am getting them into the DB now. I want to do this so that
later I can query the DB for times.

what time format do they need to be in in mysql?

$sql="INSERT INTO mydb (time1,time2)
VALUES
('$_POST[txttime1]','$_POST[txttime2]')";

thanks.

[www.1-script.com]

responding to
http://www.1-script.com/forums/mysql-time-format-and-formatting-text-entered-article7891--22.htm

Post by PAkerly
Hello,
I've got a PHP web form that users enter time into. Usually the user
1215
1205
215
etc...
12:15
12:05
2:15
etc..
or another time format when they are being entered into the DB? Here
is how I am getting them into the DB now. I want to do this so that
later I can query the DB for times.
what time format do they need to be in in mysql?
$sql="INSERT INTO mydb (time1,time2)
VALUES
('$_POST[txttime1]','$_POST[txttime2]')";
thanks.

Well, question is: what is your application doing with this value
afterwards? 1215 is obviously not a proper time stamp to do anything
useful with 'cause year and date are missing. Are they also entering day
and year with it, too? If that's the case, my personal preference would be
to concatenate day/year/time properly and convert to a proper epoch
timestamp before inserting into the DB.
But, again, without knowing what you're doing with the value afterwards,
it's hards to say what's preferred.

It looks like at this point the colon is in there just for pretty
formatting and in such case I would insert and keep it in the DB as 1215
and then would pretty it up outside mysql just before showing it to a
user. Basically, you're using varchar(4) instead of varchar(5). Saving one
character is usually not worth arguing about but just on principal: format
the data for pretty output
after the data has been extracted from DB.

-------------------------------------

[Gordon Burditt]

Post by PAkerly
I've got a PHP web form that users enter time into. Usually the user
1215
1205
215
etc...
12:15
12:05
2:15
etc..
or another time format when they are being entered into the DB? Here
is how I am getting them into the DB now. I want to do this so that
later I can query the DB for times.

Are you using the MySQL DATETIME or TIME type? If so,
I'd recommend putting the time *IN* in the format that MySQL wants,
(probably using PHP to reformat it: PHP is better at string handling
for this) (e.g. '2010-02-05 13:27'), and getting the time *OUT* in
whatever format you want (e.g. using SELECT DATE_FORMAT(time1,
'%H:%i) FROM ... which might produce '13:27'). For the TIME
type, use TIME_FORMAT().