在 PHP/Mysql 中处理时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13719116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dealing with Time in Php/Mysql
提问by Towki
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.
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.
我真正想要实现的是让用户输入的时间格式为 H:MM am/pm（例如 6:30 pm），以正确的方式将其存储在数据库中，并以完全相同的格式显示或检索此数据被输入。
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.
我知道网络上有很多资源，但我只想为像我这样的编程新手提供直接而明确的答案。
回答by MrCode
If you take the time as 6:30 PMas 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 PM用户输入，那么您可以strtotime()将其转换为时间戳，然后您可以将其格式化以存储在数据库中。
Use the MySQL TIMEdata type which requires the time format in HH:MM:SS (24 hour).
使用TIME需要 HH:MM:SS（24 小时）格式的 MySQL数据类型。
Example:
例子：
$dbFormat = date('H:i:s', strtotime('6:30 PM'));
Produces:
产生：
18:30:00
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
下午6:30
回答by Dillen Meijboom
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.phpand 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)。
回答by RainHeart257
You should use the type timein mysql http://dev.mysql.com/doc/refman/5.0/en/time.html
您应该在 mysql http://dev.mysql.com/doc/refman/5.0/en/time.html 中使用类型time
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.strtotime.php
回答by Warin Koslowski
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 数据库是最好的主意。如果您在这里只有时间数据，我建议使用该列的时间格式。日期时间和时间戳你应该只在你有完整的日期和时间部分，如 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:00am/pm 格式，您可以尝试：
$time = '18:30:00';
$newTime = strftime('%I:%M %p',strtotime($time));
I hope this will help you.
我希望这能帮到您。
回答by CE_REAL
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.
使用格式为 (YYYY-MM-DD HH:MM:SS) 的 MySQL 时间戳。您需要以 24 小时格式保存时间。如果您只是将其保存为 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.
然后当您检索时间时，只需使用 date('h:i A', $mysql_timestamp) 将其格式化为下午 6:30。
Hope that helps.
希望有帮助。
回答by Bart Friederichs
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 strftimeand strtotimefunctions:
$timestamp = strtotime("6:30 pm");
$timestring = strftime("%I:%M %P", $timestamp);
The $timestampis seconds since epoch and you can store it as a INT in your database.
这$timestamp是自纪元以来的秒数，您可以将其作为 INT 存储在您的数据库中。
