blocks|key|1045884|text|在MySQL中，您可以执行MONTH(date)、YEAR(date)等操作，例如，您可以执行date('g',+strtotime($datefromsql))来获取日期中的12小时格式。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1045885|因此，在上面的示例中，您可以这样做|1045886|SELECT+YEAR(date),+
+++++++MONTH(date),+
+++++++DAYOFMONTH(date),+
+++++++HOUR(date),+
+++++++MINUTE(date),+
+++++++SECOND(date)|code-block|syntax|javascript|1045887|或者在PHP中|1045888|$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);|1045889|entityMap^0|D|B|P|A|1B|Y|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|10|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|11|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|R|$]]

In MySQL, just do <code>MONTH(date)</code>, <code>YEAR(date)</code>, etc. In PHP, you can do <code>date('g', strtotime($datefromsql))</code> to get, for example, the 12-hour format hour from the date.

So in your above example, you could either do 

<pre><code>SELECT YEAR(date), 
 MONTH(date), 
 DAYOFMONTH(date), 
 HOUR(date), 
 MINUTE(date), 
 SECOND(date)
</code></pre>

or in PHP, 

<pre><code>$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);
</code></pre>

blocks|key|1045900|text|检查此链接http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1045901|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|code-block|syntax|javascript|1045902|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html^0|5|1V|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Check this link <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html</a>

<pre><code>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
</code></pre>

blocks|key|1045936|text|使用DateTime类的最佳方式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1045937|$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');|code-block|syntax|javascript|1045938|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Best way to use DateTime class

<pre><code>$my_sql_date = '2011-01-06 09:39:11';
$date_time = new DateTime($my_sql_date);
echo $date_time-&gt;format('Y').PHP_EOL;
echo $date_time-&gt;format('m').PHP_EOL;
echo $date_time-&gt;format('d').PHP_EOL;
echo $date_time-&gt;format('h').PHP_EOL;
echo $date_time-&gt;format('i').PHP_EOL;
echo $date_time-&gt;format('s');
</code></pre>

blocks|key|831042|text|老帖子，但我忍不住贴出了显而易见的“蛮力”解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|831043|如果您的SQL日期-时间字符串格式始终与示例中的格式相同，并且您不想使用日期-时间函数/类，则可以执行以下操作：|831044|$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);|code-block|syntax|javascript|831045|现在，您需要的值在以下变量中可用：$year,+$month,+$day,+$hr,+$min,+$sec。|offset|length|style|CODE|831046|entityMap^0|0|0|0|H|10|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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:

<pre><code>$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);
</code></pre>

The values you want are now available in the following variables: <code>$year, $month, $day, $hr, $min, $sec</code>.

In my mysql database I have dates like this: 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:

<pre><code>2011
01
06
09
39
11
</code></pre>

How can I do it ?

How extract years, months, days, hours, minutes, seconds from a mysql date?

数据库

MySQL

在我的mysql数据库中，日期如下: 2011-01-06 09:39:114525我需要分别提取年，月，日，小时，分钟，秒。所以我需要从上面的例子中提取：20110106093911我该怎么做呢？

问如何从mysql日期中提取年、月、日、小时、分钟、秒？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从mysql日期中提取年、月、日、小时、分钟、秒？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从mysql日期中提取年、月、日、小时、分钟、秒？
EN