How to convert second to formatted date time in mysql? I have a query to get date difference in second. i.e
SELECT TIMESTAMPDIFF(SECOND, '2012-06-03 13:13:55', '2012-06-06 15:20:18') sec
Now I need to convert this seconds in date time in SQL. Is it possible? Or Is there are any function in mysql to get DATETIME fields difference with formatted option like
SELECT DATEDIFF('%m %D, %Y %H...', '2012-06-03 13:13:55', '2012-06-06 15:20:18') sec
So my expected result is 1 mon 5 day 13 hour 3 sec
Check out this function date_format(date, format) date_format spec
I think you want this. here default date specify '2008-01-02'.
SELECT DATE_ADD('2008-01-02', INTERVAL TIMESTAMPDIFF(SECOND, '2012-06-03 13:13:55', '2012-06-06 15:20:18') SECOND);
This seems to work
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
Giving the result row
secs mins hours days months years
266783 4446 74 3 0 0
You can now do whatever you like with those results
You could now use just the seconds result column to create your output using php like so :-
<?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 );
This would output
4 Days 2 Hours 12 Minutes 23 Seconds