CODEWITHSUNDEEP
phpmysql
Yogus
Yogus

Reputation: 2272

How to split time left in days,hours,minutes,seconds in query

Below is my table structure

enter image description here

Now I am in need of a query in whicn I can display the days,hours,minutes,second left for that event.

I have use this query 

select title,`date`,subject ,TIMESTAMPDIFF(DAY, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'),'2013-05-25 12:00:00')  as dayleft ,TIMESTAMPDIFF(HOUR, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'),'2013-05-25 12:00:00')  as hourleft from jos_holidays where `date` > CONVERT_TZ(NOW(),@@session.time_zone, '+05:30')

and the above results me

enter image description here

But my need to get exact time left includes days,hours,minutes and seconds and display it in seperate columns. Can I achive this in query? Though this can be achievable in PHP.

Also the date "2013-05-25 12:00:00" value in query is hard coded . Can be make it dynamic too.

Thanks

Upvotes: 2

Views: 1983

Answers (4)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT title,
       `date`,
       subject ,
       TIMESTAMPDIFF(DAY, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'), `date`) AS dayleft,
       TIMESTAMPDIFF(HOUR, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'), `date`)
       - TIMESTAMPDIFF(DAY, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'), `date`)*24 AS hourleft,
       TIMESTAMPDIFF(MINUTE, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'),  `date`) -
       TIMESTAMPDIFF(HOUR, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'), `date`)* 60 AS minuteleft,
       TIMESTAMPDIFF(SECOND, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'), `date`)- 
       TIMESTAMPDIFF(MINUTE, CONVERT_TZ(NOW(),@@session.time_zone, '+05:30'),  `date`)*60 AS secondleft
FROM jos_holidays
WHERE `date` > CONVERT_TZ(NOW(),@@session.time_zone, '+05:30')

Upvotes: 1

webGautam
webGautam

Reputation: 565

its work i have used this query

 SELECT 
    TIMESTAMPDIFF(DAY,NOW(),'2013-06-23') AS dafLeft, 
    TIMESTAMPDIFF(HOUR,NOW(),'2013-06-23')-TIMESTAMPDIFF(DAY,NOW(),'2013-06-23')*24 AS hourLeft,
    TIMESTAMPDIFF(MINUTE,NOW(),'2013-06-23')-TIMESTAMPDIFF(HOUR,NOW(),'2013-06-23')*60 AS minLeft,
    TIMESTAMPDIFF(SECOND,NOW(),'2013-06-23')-TIMESTAMPDIFF(MINUTE,NOW(),'2013-06-23')*60 AS secLeft;

For dynamic just '2013-06-23' to your field name (date field name) and fetch like you do in other table.

Happy coding!! :)

Upvotes: 0

Arjan
Arjan

Reputation: 9884

In PHP you can use

$now = new DateTime(); // or use any start date you want
$date = new DateTime($row['date']); // your sql query result
$diff = $now->diff($date);

This makes $diff a DateInterval object that contains all the information you are looking for. It does require PHP 5.3+.

I'm not sure if it would be possible to get the same information directly from MySQL. But I would assume that it's mainly for display purposes, so calculating this in PHP from available information is not too much overhead.

Upvotes: 0

GautamD31
GautamD31

Reputation: 28753

Try like

$diff = strtotime($date) - time();
echo "tile left is ".date('Y-m-d H-m-i',$diff);

Upvotes: 0

Related Questions