在Php / Mysql中处理时间

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.

我很困惑。例如,我正在添加一条记录,时间也应该填满,然后我填写时间文本框,格式如下午6:30。

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.

那么如何将这些数据存储到mysql数据库中呢?我应该使用什么数据类型?因为有时间,日期时间和时间戳数据类型。我应该使用什么PHP函数。

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.

我想要实现的是用户输入时间格式为H:MM am / pm(例如下午6:30),以正确的方式将其存储在数据库中,并以格式化的方式显示或检索此数据进入了。

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

谁能帮助我了解如何使用Php在mysql中存储和检索时间。请举个例子。

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.

我知道网上有这么多资源,但我想在编程方面为我这样的新手提供直接而明确的答案。

6 个解决方案

#1

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.

如果您将时间作为用户输入的下午6:30,那么您可以使用strtotime()将其转换为时间戳,然后您可以格式化存储在数据库中。

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

使用需要HH:MM:SS(24小时)时间格式的MySQL TIME数据类型。

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.

这已准备好插入数据库。从数据库中检索它时,可以使用DATE_FORMAT()函数将MySQL格式化为原始格式。

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

Produces:

6:30 PM

#2

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

您可以使用时间戳将时间存储在MySQL数据库中。

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: http://nl1.php.net/manual/en/function.date.php and http://php.net/manual/en/function.strtotime.php

有关更多信息,请参阅:http://nl1.php.net/manual/en/function.date.php和http://php.net/manual/en/function.strtotime.php

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

编辑:您可以在MySQL数据库中将时间戳值保存为INT(11)。

#3

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

您应该在mysql中使用类型时间http://dev.mysql.com/doc/refman/5.0/en/time.html

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

时间以这种格式存储:HH:MM:SS

And for php converting,

对于PHP转换,

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

如果你想看到这个过程: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

#4

you can try something like that:

你可以试试这样的东西:

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

Now you should get a valid mysql time.

现在你应该得到一个有效的mysql时间。

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

大多数情况下,最好的方法是使用正确的格式将数据存储到mysql数据库。如果你在这里只有timedata,我推荐该列的时间格式。日期时间和时间戳只有在你有完整日期和时间部分时才能使用,如2012-12-05 09:54:32(或时间戳1354697672)。

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

要加载时间数据并将其格式化回00:00 am / pm格式,您可以尝试:

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

I hope this will help you.

我希望这能帮到您。

#5

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:

使用strftime和strtotime函数:

$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.

$ timestamp是epoch以来的秒数,您可以将其作为INT存储在数据库中。

#6

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.

使用带有格式的MySQL时间戳(YYYY-MM-DD HH:MM:SS)。您需要以24小时格式保存时间。如果你只是将它保存为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.

然后当你检索时间时,只需使用date('h:i A',$ mysql_timestamp)将其格式化为下午6:30。

Hope that helps.

希望有所帮助。