how to insert am pm in a time datatype filed in mysql

Question

i want to save a library all 7 days open time and close time

please suggest how to do it...

right now i m doing like this

on database i crate a db table_lib_hours

-----FIELD-----x--TYPE----------
|day_id        |INT          |
|day_name      |varchar(100) |
|day_open_time |TIME         |
|day_close_time|TIME         | 
|last_update   |TIMESTAMP    |

Now i have 2 Questions

1>is there any way to store all 7 day's name in day_name field automatically

Answer 1

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 .

Answer 2

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

Answer 3

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