CODEWITHSUNDEEP
phpsqlmysqldatetimetime
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40633

Convert time to MySQL Time

I'm trying to store a week schedule (e.g. Monday, 9am-5pm, etc.). I do not have the need to store the dates; I just need to save the following: day, from time, to time.

So, say I have the following time values:

Assuming that the values are actual valid times, how do I convert these strings into MySQL Time type? And how do I do the reverse? (I'm using PHP.)

Also, how do I query for something like this: find every store that is open on Mondays between 2pm and 3pm? Do I just do something like: WHERE day = 1 AND from_time >= 2pm AND to_time <= 3pm (changing '2pm' and '3pm' to whatever their converted values are, of course)? Or is there some MySQL function better suited for such queries?

Upvotes: 0

Views: 1664

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332571

MySQL has a TIME data type - it will store values in the hh:mi:ss format, and you can use the TIME_FORMAT function to change the presentation to however you'd like.

Try not to rely on functions applied to the column for comparison - IE:

WHERE TIME_TO_SEC(column) = 123

...because it will render an index, if one exists on the column, useless -- ensuring a table scan (worst performing option).

Upvotes: 0

Jens M&#252;hlenhoff
Jens M&#252;hlenhoff

Reputation: 14873

MySQL understands the ISO 8601 date format, so you have to give time in the form "08:00:00" for 8:00 AM.

You can just use a string with a valid ISO 8601 time in your WHERE clause.

http://en.wikipedia.org/wiki/ISO_8601

http://dev.mysql.com/doc/refman/5.1/en/time.html

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

Upvotes: 0

coreyward
coreyward

Reputation: 80041

MySQL has built in conversion for unix timestamps to a MySQL date:

INSERT INTO table (the_date) VALUES (FROM_UNIXTIME(your_timestamp));

…and the other way around…

SELECT UNIX_TIMESTAMP(the_date) FROM table;

You can use the DAY() and DAYOFWEEK() functions in your WHERE conditionals to convert your MySQL timestamps into the relevant units for you to do your query.

You might need to play around a bit with your schema to determine the best structure to allow you to get the functionality you need here. E.g. it might not make sense to store the day-of-week in a datetime field at all.

Upvotes: 1

Related Questions