mysql time format, and formatting text entered??

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I've got a PHP web form that users enter time into. Usually the user
enters time as:
Is there a way I can convert these times to:
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)


Re: mysql time format, and formatting text entered??

responding to wrote:

PAkerly wrote:

Quoted text here. Click to load it

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.


Re: mysql time format, and formatting text entered??

Quoted text here. Click to load it

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().

Site Timeline