How to insert time with am/pm format into the database

Question

So im trying to insert a time using an input text field into a database table with data type TIME.

The format of time that I want to insert should be like this:

H:MM pm// example: 6:30 pm

The problem is the am/pm doesnt insert in my database table. Only the hour and minute.

Please give me idea how to solve this. Better with sample codes. Thanks.

Why you need to store time in that format? You can store time in a DATETIME field and show it the way you want... — m4t1t0, Dec 04 '12 at 16:20
see this: http://stackoverflow.com/questions/2086313/insert-am-pm-from-front-side-and-store-in-time-field-of-mysql — GBD, Dec 04 '12 at 16:21

score 7 · Answer 1 · answered Dec 04 '12 at 16:27

7

Data Type TIME is for storing time data type - that means no AM/PM. Store the data in Your database in 24 hour format and format it to 12 hour format with am/pm in PHP or MySQL using one of these:

PHP:

$date = new DateTime($mysql_column['time']);
$date->format('h:i:s a');

or:

$date = date('h:i:s a', strtotime($mysql_column['time']));

or MySQL:

SELECT DATE_FORMAT('%h:%i:%s %p', time) FROM table;

answered Dec 04 '12 at 16:27

shadyyx

15,825
6
60
95

How do I format the time at first?I mean the user input of the time before saving into the database. – Towki Dec 04 '12 at 17:01
@Towki If the user is free to input whatever he wants, You would have to make a right data input validation. You could also create real-time JavaScript validation allowing user to input only numbers and a colon and only in this format: `00:00`. Then of course perform a sanitization and if You have a string like this: `18:59` or `08:12` etc You can just easily store this `string` into Your `TIME` column. – shadyyx Dec 05 '12 at 09:12

score 4 · Answer 2 · answered Dec 04 '12 at 16:25

4

Store the TIME as a standard format (18:30:00), and the format it however you want when you display it (Using DateTime objects or the date functions).

MySQL doesn't support extra formats when storing time data.

answered Dec 04 '12 at 16:25

Madara's Ghost

172,118
50
264
308

Debugger · Answer 3 · 2014-12-18T13:41:30.003

I think you want to add the jquery time picker value in your database with actual format in the database.

Here I have written some function

function update_time($time){
    $ap = $time[5].$time[6];
    $ttt = explode(":", $time);
    $th = $ttt['0'];
    $tm = $ttt['1'];
    if($ap=='pm' || $ap=='PM'){
        $th+=12;
        if($th==24){
            $th = 12;
        }
    }
    if($ap=='am' || $ap=='AM'){
        if($th==12){
            $th = '00';
        }
    }
    $newtime = $th.":".$tm[0].$tm[1];
    return $newtime;
}
$time = update_time($_POST['time']); //here I am calling the function now you can insert the value in db

you just have to call the function and insert the returned value in database. And while printing that you can do something like that echo date("h:i A",strtotime($time));

score 0 · Answer 4 · answered Dec 04 '12 at 16:21

0

Change the type of the field to a varchar. TIME cannot store it like that. However, keep in mind that storing it like you want to will make it more difficult to provide localized results if that is something you will eventually need. That is, timezone support becomes difficult if you are not storing the timestamp itself, but rather a user-friendly representation.

EDIT: Or, DATETIME works as well, as was pointed out in the comments above.

answered Dec 04 '12 at 16:21

imkingdavid

1,411
13
26

Dates and times belong in date or time (or DATETIME) fields. They should be stored as the proper type and converted for presentation. – Ken White Dec 04 '12 at 16:34
Storing a timestamp as a string and converting that in PHP is, in my opinion, perfectly acceptable. – imkingdavid Dec 04 '12 at 16:37
Until you need to do some sort of math operation, at which point it's not. If you didn't need a DATE, TIME, or TIMESTAMP type they wouldn't exist. (Didn't downvote, BTW; I just made a comment.) – Ken White Dec 04 '12 at 16:55

score 0 · Answer 5 · answered Dec 04 '12 at 16:24

You can use the DateTime Object in PHP which has functions to create a time object from any format and also has a function to output a time in any format like so

<?php
     $date = DateTime::createFromFormat('j-M-Y', '15-Feb-2009');
     echo $date->format('Y-m-d');
?>

http://php.net/manual/en/datetime.createfromformat.php

Darius · Answer 6 · 2012-12-04T16:44:57.177

0

You would be best changing the field type to 'VARCHAR (32)', and then writing the time with PHP.

Example: date('m/d/y g:i:sa');

Why do you want to store the am or pm anyhow? If you store the date/time as a unix epoch timestamp, you can format the date however you want in the program - not the database.

Example: time(); - Store this in an INT(8) field.
         date('m/d/y g:i:sa, $time()); - Output from DB like this.

edited Dec 04 '12 at 16:44

answered Dec 04 '12 at 16:25

Darius

612
2
11
23

Dates and times belong in date and time (or DATETIME) fields, not character fields. Convert them for presentation, not for storage. – Ken White Dec 04 '12 at 16:33
This is true - I would use an int(8) data field instead. – Darius Dec 04 '12 at 16:44

score 0 · Answer 7 · answered Dec 04 '12 at 16:53

0

try .ToShortTimeString() after your date variable.

answered Dec 04 '12 at 16:53

Diane

119
3
9

How to insert time with am/pm format into the database

7 Answers7