php - MySQL秒到格式化日期时间

Question

如何在mysql中将秒转换为格式化日期时间？我有一个查询以秒为单位获取日期差异。即

 SELECT TIMESTAMPDIFF(SECOND, '2012-06-03 13:13:55', '2012-06-06 15:20:18') sec

现在我需要在 SQL 中将这一秒转换为日期时间。是否可以？或者 mysql 中是否有任何函数可以获取 DATETIME 字段与格式化选项(如

)的差异

SELECT DATEDIFF('%m %D, %Y %H...', '2012-06-03 13:13:55', '2012-06-06 15:20:18') sec

所以我的预期结果是1 mon 5 day 13 hour 3 sec

Answer 1

这似乎有效

SELECT 
TIMESTAMPDIFF(SECOND, '2012-06-03 13:13:55', '2012-06-06 15:20:18') secs,
TIMESTAMPDIFF(MINUTE, '2012-06-03 13:13:55', '2012-06-06 15:20:18')  mins,
TIMESTAMPDIFF(HOUR, '2012-06-03 13:13:55', '2012-06-06 15:20:18')  hours,
TIMESTAMPDIFF(DAY, '2012-06-03 13:13:55', '2012-06-06 15:20:18')  days,
TIMESTAMPDIFF(MONTH, '2012-06-03 13:13:55', '2012-06-06 15:20:18')  months,
TIMESTAMPDIFF(YEAR, '2012-06-03 13:13:55', '2012-06-06 15:20:18')  years

给出结果行

secs    mins    hours   days    months  years
266783  4446    74      3       0       0

您现在可以对这些结果做任何您喜欢的事情

您现在可以仅使用秒结果列来使用 php 创建输出，如下所示:-

<?php
// of course get this from the result set not a hard coded value
$secs = 266783;
$days = date('d', $secs);

$secs -= $days*24*60*60;
$hours = date('H', $secs);

$secs -= $hours*60*60;
$mins = date('m', $secs);

$secs -= $mins*60;
$seconds = date('s', $secs);

echo sprintf('%d Days %d Hours %d Minutes %d Seconds', $days, $hours, $mins, $seconds );

这将输出

4 Days 2 Hours 12 Minutes 23 Seconds