Time

Anon

Hi,

I have a field in my MYSQL dbase set as TIME.
I had to enter all the times in hh:mm:ss format.

I have a HTML form and I dont want the user to have to type in hh:mm:ss, hh:mm would be better. How can I do this (or is there a better way to let the user enter the time on the form)?

Thank-you.

shivers

I usually find it best to store dates/times as INTs in the database and use the time_t format (# of seconds past midnight 1st Jan 1970) for storing/processing them.

Then u can use mktime() to convert back to human readable form for display. There are also other functions for converting from human form to time_t form.

I also tend to use a set of generic functions for date/time entry - providing drop-down boxes ensuring data is always valid and in the format you like.

(this also makes life easier for internationalisation / time zone handling in the future)

its also very good for working out the difference between two dates/times, and lots of other groovy things.

Anon

Hi Shane,

thanks for the advice but I cant easily change my data type from TIME to INT.
Do you know of any other way,
at the moment my form has got a text box saying enter time (in hh:mm:ss format).

Thanks.

shivers

I'm not too sure on other ways - I only ever use the time_t method.

I'm guessing the TIME data type takes a string of the form hh:mm:ss or some such thing. You could still write a generic function that draws 3 drop-down boxes - one each for hours, minutes and seconds.

This would give you 3 variables, say $hour, $minute and $second whilch you could join like this:

$thetime = $hour . ":" . $minute . ":" . $second

That should be ok to put in your SQL statements.

As for pulling data out, your drop-down drawing function could take the string as a 'default value' parameter, split it into its component parts (say explode() using ':' as the separator).

On the usability side, it could be annoying for user to have to scroll down 60 entries in the minute/second dropdowns - you could just include some of the entries - say only 0, 15, 30, and 45 seconds for example. (also a good idea to ensure the default options are close to what users will most likely be entering)

Otherwise, if you want to keep the input as a text box, you could just parse the inputted text (perhaps explode()ing it and putting it back together) to ensure you just have hh:mm - and then adding on a dummy :ss portion to keep the database happy.

Hope that helps, let me know if you'd like any clarification on anything 🙂

Anon

Hi Shane,

thanks, I get your idea, I tried the following...
Instead of having one text box on the form I made two (hour and minute).

In my PHP I added
$second=00;
$query="SELECT * xxxxxx FROM YGGI WHERE time='thetime = $hour . ":" . $minute . ":" . $second '";

However when I try the form it seems not too work. I think it is something simple but I cant see it, any ideas?

I also tried
$second=00;
$thetime=$hour . ":" . $minute . ":" . $second;

then doing the query with just 'thetime' but still no luck.

Thanks,
Laura.

shivers

hmm.

could you post the SQL query after the variable substitution has taken place so I can check the syntax?

Could you also post the error message exactly as reported? THis shoudl help us get to the bottom of it 🙂

(feel free to chop out any table/field names if there are any confidentiality issues, or you could email it to me at swright@proactive-computing.co.uk if you'd rather not post)

Anon

Thanks for the help Shane, Ive got it!
I dont think it liked $second=00;
so I concatenated hour and minute and multiplied them by 100.
Seems to work fine.

Thanks for the help, much appreciated!

shivers

noprobs, glad to help & happy it worked 🙂

[deleted]

If you store the time as an int, you loose a lot of the cute date/time calculation functions in MySQL. Not a very good idea.

[deleted]

Just put the string that the user entered into the database :-)

If you look at the manual it says:

-- quote --

One can also use one of the
following ``relaxed'' syntax: HH:MM:SS.fraction, HH:MM:SS, HH:MM, D HH:MM:SS, D HH:MM, D HH or SS. Here D is days between 0-33.

-- end quote --

so entering the time like this:

INSERT INTO table (time_field) values ("10:20");

is perfectly acceptable.

Don't forget that this is a string, so you'll need to add quotes around it in your sql statement.

shivers

This is true - but really its not much of a loss and in many cases it's not a good idea to use them anyway...

a) Portability. Databases differ a lot in what they provide in terms of date/time manipulation. Should you ever need to change the database for whatever reason, there'll be zero work if you used INTs from the start.

b) Speed. All manipulation becomes simple integer adds/subtracts = very fast! No string parsing or anything else yukky (except when you need to talk to users but thats unavoidable)

c) Consistency. Dates and times and datetimes are all held/manipulated in the same way internally. All operations are simple. All conversions from int to human readable and vice versa should be handled by the same functions - global control of formatting, but easily changeable if need be.

d) Future Expansion. Internationalising your project with support for different time zones is a doddle - just let the input/output functions access the user profile for a time offset, add/subtract the relevant amount and display the time zone suffix (eg EST) with the output. Trivially easy!

The only problem is if you need to store/manipulate dates before 1970. This is painful. But aside from dates of birth there is never anything that needs it.

Granted there is an extra overhead in writing the conversion functions in the first place - but this only has to be done once - and there's no point reinventing the wheel; if anyone wants them they can have mine.

neville

I did an insert like this
INSERT INTO table (time_field) values ("10:20");

and when I queried it I got

00:10:20

Which is incorrect ..... I want the 10 to be inserted as hour not as minute

[deleted]

indeed, the manual seems to contradict itself,
mysql assumes the rightmost values to be seconds,
so hh:mm is in fact mm:ss.

It should be ok if you append ':00' to the end:
INSERT INTO table (time_field) values ("10:20:00");

with a string it would be:
$time="10:20";
$sql_query="INSERT INTO table (time_field) values (".$time.":00);";

You could add a simple regexp to check if the user typed HH:MM or HH:MM:SS so you can remove the extra ":00" in your sql statement.

neville

Thats how I am doing it right now ....

I actually thought there was another(undocumented) way mysql handled the time field at design time so that it would automatically take in hh:mm as hh:mm only

The timestamp(14) field type for instance takes in date and time in YYYYMMDDHHMMSS format only. The time field type does not have any such thing.