CODEWITHSUNDEEP
phpmysql
Rejoanul Alam
Rejoanul Alam

Reputation: 5398

MySQL second to formatted date time

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

Upvotes: 0

Views: 336

Answers (3)

RiggsFolly
RiggsFolly

Reputation: 94672

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

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

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

Upvotes: 0

user4962466
user4962466

Reputation:

Check out this function date_format(date, format) date_format spec

Upvotes: 0

Related Questions