php 如何从 mysql 日期中提取年、月、日、小时、分钟、秒？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4616746/
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
How extract years, months, days, hours, minutes, seconds from a mysql date?
提问by xRobot
In my mysql database I have dates like this: 2011-01-06 09:39:114525
在我的 mysql 数据库中，我有这样的日期：2011-01-06 09:39:114525
and I need to extraxt separately years, months, days, hours, minutes, seconds from it.
我需要从中分别提取年、月、日、小时、分钟、秒。
So I need to extract from above example:
所以我需要从上面的例子中提取：
2011
01
06
09
39
11
How can I do it ?
我该怎么做 ？
回答by Andrew
In MySQL, just do MONTH(date), YEAR(date), etc. In PHP, you can do date('g', strtotime($datefromsql))to get, for example, the 12-hour format hour from the date.
在 MySQL 中，只需执行MONTH(date)、YEAR(date)等。在 PHP 中，您可以执行date('g', strtotime($datefromsql))从日期获取 12 小时格式的小时等。
So in your above example, you could either do
所以在你上面的例子中，你可以做
SELECT YEAR(date),
MONTH(date),
DAYOFMONTH(date),
HOUR(date),
MINUTE(date),
SECOND(date)
or in PHP,
或在 PHP 中，
$time = strtotime($datefromsql);
echo date('Y', $time);
echo date('m', $time);
echo date('d', $time);
echo date('h', $time);
echo date('i', $time);
echo date('s', $time);
回答by Wazy
Check this link http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
检查此链接http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
CONVERT_TZ() Convert from one timezone to another
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME() Return the current time
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DATE() Extract the date part of a date or datetime expression
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format UNIX timestamp as a date
GET_FORMAT() Return a date format string
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE() Create a date from the year and day of year
MAKETIME MAKETIME()
MICROSECOND() Return the microseconds from argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
QUARTER() Return the quarter from a date argument
SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
SECOND() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() A synonym for DATE_SUB() when invoked with three arguments
SUBTIME() Subtract times
SYSDATE() Return the time at which the function executes
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIME() Extract the time portion of the expression passed
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP() Return a UNIX timestamp
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (0-53)
YEAR() Return the year
YEARWEEK() Return the year and week
回答by Gvidon Shmavonyan
Best way to use DateTime class
使用 DateTime 类的最佳方法
$my_sql_date = '2011-01-06 09:39:11';
$date_time = new DateTime($my_sql_date);
echo $date_time->format('Y').PHP_EOL;
echo $date_time->format('m').PHP_EOL;
echo $date_time->format('d').PHP_EOL;
echo $date_time->format('h').PHP_EOL;
echo $date_time->format('i').PHP_EOL;
echo $date_time->format('s');
回答by Rubén Ruíz
$dt = Carbon::now();
$dt->timestamp = strtotime($mysqlVarDatetime); //like '2017-01-31 07:19:11' string cad
it can be result in:
它可能导致：
var_dump($dt->year); // int(2017)
var_dump($dt->month); // int(1)
var_dump($dt->day); // int(31)
var_dump($dt->hour); // int(7)
var_dump($dt->minute); // int(19)
var_dump($dt->second); // int(11)
Resource for more information Carbon library: http://carbon.nesbot.com/docs/#api-getters
碳库更多信息资源：http: //carbon.nesbot.com/docs/#api-getters
回答by Stefan
Old thread, but I couldn't resist posting the obvious 'brute-force' solution:
旧线程，但我忍不住发布了明显的“蛮力”解决方案：
If your SQL date-time string format is always given as in your example, and you don't want to use date-time functions/classes, you can do the following:
如果您的 SQL 日期时间字符串格式始终如您的示例所示，并且您不想使用日期时间函数/类，则可以执行以下操作：
$mysql_datetime = "2011-01-06 09:39:114525";
list($date, $time) = explode(' ', $mysql_datetime);
list($year, $month, $day) = explode('-', $date);
list($hr, $min, $sec) = explode(':', $time);
$sec = (int)substr($sec, 0, 2);
The values you want are now available in the following variables: $year, $month, $day, $hr, $min, $sec.
您想要的值现在在以下变量中可用：$year, $month, $day, $hr, $min, $sec.
回答by arjithn
I would like to suggest one more cleaner way. And this can be achieved without on any library or framework.
我想建议一种更清洁的方法。这可以在没有任何库或框架的情况下实现。
First, you should convert the datetime string to a timestamp (strtotimeworks on almost every format). Then using getdateas follows :
首先，您应该将日期时间字符串转换为时间戳（strtotime适用于几乎所有格式）。然后使用getdate如下：
$dateAsTimestamp = strtotime("2011-01-06 09:39:11.45");
$dateTime = getdate($dateAsTimestamp);
The function returns all components of date and time as an associative array.
该函数将日期和时间的所有组件作为关联数组返回。
Array
(
[seconds] => 11
[minutes] => 39
[hours] => 9
[mday] => 6
[wday] => 4
[mon] => 1
[year] => 2011
[yday] => 5
[weekday] => Thursday
[month] => January
[0] => 1294306751
)
Now can be accessed as per your requirement
现在可以根据您的要求访问
echo($dateTime['hours']);
echo($dateTime['minutes']);
echo($dateTime['seconds']);
$MONTH = $dateTime['month'];
