You may want to use a TIMEDIFF for time differences (no microseconds). I.e. instead
> SET timediff = > (HOUR(ts1) - HOUR(ts2)) * 3600 + > (MINUTE(ts1) - MINUTE(ts2)) * 60 + > (SECOND(ts1) - SECOND(ts2)); > use SET timediff = TIMEDIFF( TIME(ts1), TIME(ts2) ) Anyway for the combination of DATEDIFF/TIMEDIFF, there is no built-in that does excactly what you want. Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Robert [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 23, 2004 2:09 PM > To: MaxDB > Subject: Timestamp Difference in Seconds > > > > Hi all, > > I couldn't find a function that calculates the difference between two > timestamps in seconds. Below you'll find what I came up so > far. Is there > any better way to do that with MaxDB? > > Note: DATEDIFF() seems to always return the absolute amount of days > regardless of argument ordering(i.e. always >= 0). That's > why you see the > if statement there. > > Thanks for any feedback! > > Kind regards > > robert > > > > CREATE FUNCTION TIMESTAMPDIFF( ts1 TIMESTAMP, ts2 TIMESTAMP ) > RETURNS INTEGER > AS > VAR > timediff INTEGER; > BEGIN > /* calculate in seconds */ > SET timediff = > (HOUR(ts1) - HOUR(ts2)) * 3600 + > (MINUTE(ts1) - MINUTE(ts2)) * 60 + > (SECOND(ts1) - SECOND(ts2)); > > IF ts1 >= ts2 THEN > return timediff + DATEDIFF( DATE(ts1), DATE(ts2) ) * 86400 > ELSE > return timediff - DATEDIFF( DATE(ts2), DATE(ts1) ) * 86400; > END; > > > with fractional seconds: > > CREATE FUNCTION TIMESTAMPDIFFMS( ts1 TIMESTAMP, ts2 TIMESTAMP ) > RETURNS FLOAT > AS > VAR > timediff FLOAT; > BEGIN > /* calculate in seconds with fractions */ > SET timediff = > (HOUR(ts1) - HOUR(ts2)) * 3600 + > (MINUTE(ts1) - MINUTE(ts2)) * 60 + > (SECOND(ts1) - SECOND(ts2)) + > (MICROSECOND(ts1) - MICROSECOND(ts2)) / 1000000.0; > > IF ts1 >= ts2 THEN > return timediff + DATEDIFF( DATE(ts1), DATE(ts2) ) * 86400 > ELSE > return timediff - DATEDIFF( DATE(ts2), DATE(ts1) ) * 86400; > END; > > > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
