如何从mysql日期提取年、月、日、小时、分钟、秒?

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:

所以我需要从上面的例子中提取:

How can I do it ?

我怎么做呢?

5 个解决方案

#1

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中，只需要执行月(日期)、年(日期)等操作。在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);

#2

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

#3

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');

#4

$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

#5

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。

#1