How to insert am pm in a time datatype filed in mysql?

I need to save a library all 7 days 'open time' and 'close time' , Please suggest how to achieve this?

Currently I have create a database table_lib_hours

-----FIELD-----|--TYPE-------
|day_id 开发者_JAVA百科       | int(10)     |
|day_name      |varchar(100) |
|day_open_time |TIME         |
|day_close_time|TIME         | 
|last_update   |TIMESTAMP    |

Now my question is

Is there any way to store all 7 day's name in day_name field automatically ?

Use a timestamp in UTC timezone for easier relocation of the server.

In your application, simple format the timestamp for output. When reading in data, use gmmktime().

1. strftime() with a format of %a or %A will give you the weekday name for a date.
2. Leave it as a normal time in the database and use strftime() with a format of %I or %l combined with %p or %P when displaying it .

it's usually better to keep non formatted or locale independent data inside your app, and let the front end display/format it...

So I don't know if you have to support multiple language in your app, but if it's the case I'd definitely go with a day of week index instead of the day name.

1) there's usually already functions to handle this in DB engines like DAYOFWEEK() in MySQL. 2) it's easier to order when you query it.

for the time i'd go with what fireeyedboy suggested and use STR_TO_DATE to insert your data in the DB and DATE_FORMAT when you fetch back the data

all the functions you need are here

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html