MySql database excludes A.M, P.M, from datetime, t

Question

I have three data types in my database. Datetime, timestamp, and time. I get the time using the date function and tried to insert it into the database under all three columns, but all three columns rejected the A.M, P.M part of the date function. I don't understand why. I need the A.M, P.M part of the date function to be also inserted, so I can sort the data in my database more efficiently by time. Is there another column that can store the A.M, P.M part of the date, or is there a workaround this? Thanks.

$time = date('h:i:s A');
//insert $time into all three columns into the database

Answer 1

Change the column data type as Varchar(20) and insert $time = date('h:i:s A'); into database.

Get from DB:-

while fetching this column from database u can use date('h:i:s A',strtotime($fetched_datetime_from_db)).

Enjoy!!

Answer 2

The workaround is to use the hours value as 24 hour clock. To represent '3:45:56 PM', insert to the database column a string value '15:45:56'.

Otherwise, you can use the STR_TO_DATE function to convert the string into a valid TIME value.

 INSERT INTO mytable (mycol) VALUES (STR_TO_DATE('3:45:56 PM','%h:%i:%s %p'))

---

To retrieve a TIME value from a database column, formatted as 12 hour clock with AM/PM.

SELECT DATE_FORMAT(mytimecol,'%h:%i:%s %p') AS mytimestr FROM ... 

References:

STR_TO_DATE https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

DATE_FORMAT https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format