List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 5 1999 1:40am
Subject:Re: Time difference - maybe slightly OT
View as plain text  
On Sat, 1999-09-04 17:38:38 -0700, Darren Sweeney wrote:
> I have 2 timestamps ... e.g. 19990904210841 & 19990815164033
> 
> I need to return the difference in days, hours, minutes and seconds
> .... any ideas on how to parse this?

That's quite similiar to the recent question on how to calculate the
age given a birthday!

 CREATE TABLE tmp (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
 , a datetime
 , b datetime
 );
 INSERT INTO tmp VALUES (NULL,19990904210841,19990815164033);
 INSERT INTO tmp VALUES (NULL,19990904210841,19990903210841);
 INSERT INTO tmp VALUES (NULL,19990904210841,19990903210840);
 INSERT INTO tmp VALUES (NULL,19990904210841,19990903210842);
 SELECT * FROM tmp;
 +----+---------------------+---------------------+
 | id | a                   | b                   |
 +----+---------------------+---------------------+
 |  1 | 1999-09-04 21:08:41 | 1999-08-15 16:40:33 |
 |  2 | 1999-09-04 21:08:41 | 1999-09-03 21:08:41 |
 |  3 | 1999-09-04 21:08:41 | 1999-09-03 21:08:40 |
 |  4 | 1999-09-04 21:08:41 | 1999-09-03 21:08:42 |
 +----+---------------------+---------------------+

 Difference in days:
   TO_DAYS(a) - TO_DAYS(b) - IF( RIGHT(a,8)<RIGHT(b,8) , 1 , 0 )

 Difference in seconds:
   TIME_TO_SEC(a) - TIME_TO_SEC(b) + IF( RIGHT(a,8)<RIGHT(b,8) , 86400 , 0 )
 or
   ( TIME_TO_SEC(a) - TIME_TO_SEC(b) + 86400 ) % 86400

 Difference in hh:mm:ss with SEC_TO_TIME(...).

 SELECT *
      , TO_DAYS(a) - TO_DAYS(b)
        -IF( RIGHT(a,8)<RIGHT(b,8) , 1 , 0 ) AS days
      , SEC_TO_TIME( TIME_TO_SEC(a) - TIME_TO_SEC(b)
                     +IF( RIGHT(a,8)<RIGHT(b,8) , 86400 , 0 )
                   ) AS sec
 FROM tmp;
 +----+---------------------+---------------------+------+----------+
 | id | a                   | b                   | days | sec      |
 +----+---------------------+---------------------+------+----------+
 |  1 | 1999-09-04 21:08:41 | 1999-08-15 16:40:33 |   20 | 04:28:08 |
 |  2 | 1999-09-04 21:08:41 | 1999-09-03 21:08:41 |    1 | 00:00:00 |
 |  3 | 1999-09-04 21:08:41 | 1999-09-03 21:08:40 |    1 | 00:00:01 |
 |  4 | 1999-09-04 21:08:41 | 1999-09-03 21:08:42 |    0 | 23:59:59 |
 +----+---------------------+---------------------+------+----------+

This should do it ... I hope.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Time difference - maybe slightly OTDarren Sweeney5 Sep
  • Re: Time difference - maybe slightly OTMartin Ramsch5 Sep