Dealing with Time in Php/Mysql

Question

Im very confused. For example I'm adding a record which time should be also filled up, then I filled up the the time textbox with format like this 6:30 pm.

So how could I store this data into mysql database? What data type should I use?Because there are time,datetime and timestamp datatypes. What PHP functions should I use.

What exactly I wanted to achieve is to get user inputted time with the format H:MM am/pm (e.g 6:30 pm), store it in a database the proper way, and display or retrieve this data with formatting exactly the way it was entered.

Could anyone please help me understand how to store and retrieve times in mysql using Php. Please give me examples.

I know there are so many resources on the web, but I want direct and clear answers for just a newbie like me in programming.

Normalise the time you want to store to ISO8601 (w3.org/TR/NOTE-datetime). There are useful JavaScript libraries for that (e.g. dojo). If you are storing time in your DB, use the time datatypes. — Bart Friederichs
– Bart Friederichs, Commented Dec 5, 2012 at 8:38
@DipeshParmar that's very bad advise. A database should use the datatypes available. You will run into problems later if you don't thnk it through. — Bart Friederichs
– Bart Friederichs, Commented Dec 5, 2012 at 8:42

MrCode · Accepted Answer · 2012-12-05 08:48:01Z

22

If you take the time as 6:30 PM as user input then you can use strtotime() to convert it to a timestamp, which you can then format for storage in the database.

Use the MySQL TIME data type which requires the time format in HH:MM:SS (24 hour).

Example:

$dbFormat = date('H:i:s', strtotime('6:30 PM'));

Produces:

18:30:00

This is ready to insert into the database. When you retrieve it from the database, you can have MySQL format it back to the original format using the DATE_FORMAT() function.

SELECT DATE_FORMAT(mytime, '%l:%i %p') FROM mytable

Produces:

6:30 PM

answered Dec 5, 2012 at 8:48

MrCode

64.6k10 gold badges95 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Towki Over a year ago

I applied your example $dbFormat = date('H:i:s', strtotime($timeinput)); for inserting the time into the database And i used this for outputting: $time = date('g:i a',strtotime($timeinput)); And all goes well!

Community · Accepted Answer · 2023-11-17 20:07:23Z

4

You could use timestamp to store your time in the MySQL database.

Time to timestamp:

<?php
    $time = strtotime($yourTime); //yourTime is the time from your input box.
?>

Timestamp to time:

<?php
    $time = date('g:i a', $yourTime); //yourTime is the time from the database.
?>

For more information see: https://www.php.net/manual/en/function.date.php and http://php.net/manual/en/function.strtotime.php

Edit: You can save a timestamp value as INT(11) in your MySQL database.

edited Nov 17, 2023 at 20:07

CommunityBot

11 silver badge

answered Dec 5, 2012 at 8:46

Dillen Meijboom

9647 silver badges13 bronze badges

Comments

RainHeart257 · Accepted Answer · 2012-12-05 08:58:17Z

2

You should use the type time in mysql http://dev.mysql.com/doc/refman/5.0/en/time.html

The time is stored in this format: HH:MM:SS

And for php converting,

Insert:

$time=strtotime('6:30 pm');
$data_bd=date("H:i:s",$time);
//And then insert into mysql field

Extract:

//Extract the data from mysql
$time=strtotime('18:30:00');
$data_print=date("g:i a",$time);

If you want to see the process: http://codepad.org/gNdm4YIy

For more information:

http://es2.php.net/manual/en/function.strtotime.php

http://es2.php.net/manual/en/function.date.php

edited Dec 5, 2012 at 8:58

answered Dec 5, 2012 at 8:52

RainHeart257

3051 silver badge9 bronze badges

Comments

Warin Koslowski · Accepted Answer · 2012-12-05 09:01:39Z

1

you can try something like that:

$time = '6:30pm';
$newTime = strftime('%H:%M:%S',strtotime($time));

Now you should get a valid mysql time.

Mostly its the best idea to store data to mysql database with the right format. If you only have timedata here i recommend the time-format for that column. datetime and timestamp you should only use if u have full dates with time-parts like 2012-12-05 09:54:32 (or as timestamp 1354697672).

To load the time data and to format it back into 00:00am/pm format you can try:

$time = '18:30:00';
$newTime = strftime('%I:%M %p',strtotime($time));

I hope this will help you.

answered Dec 5, 2012 at 9:01

Warin Koslowski

753 bronze badges

2 Comments

Towki Over a year ago

Could you explain to me what does the percent sign do?

Joseph Van Riper Over a year ago

The percent sign informs strftime that the following character is to be translated in some way, rather than displayed verbatim.

Bart Friederichs · Accepted Answer · 2012-12-05 08:45:49Z

0

It all depends on your applications (do you want time-in-a-day, or absolute time?, do you want to do arithmetic?, etc).

A simple solution could be this:

Use the strftime and strtotime functions:

$timestamp = strtotime("6:30 pm");
$timestring = strftime("%I:%M %P", $timestamp);

The $timestamp is seconds since epoch and you can store it as a INT in your database.

answered Dec 5, 2012 at 8:45

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

Comments

CE_REAL · Accepted Answer · 2012-12-05 13:31:14Z

0

Use the MySQL timestamp with format (YYYY-MM-DD HH:MM:SS). You need to save the time in a 24-Hour format. If you just save it as 0000-00-00 18:30:00.

And then when you're retrieving the time just use date('h:i A', $mysql_timestamp) to format it to 6:30 PM.

Hope that helps.

edited Dec 5, 2012 at 13:31

answered Dec 5, 2012 at 9:11

CE_REAL

3845 silver badges13 bronze badges

Collectives™ on Stack Overflow

Dealing with Time in Php/Mysql

6 Answers 6

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related