> 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) )
As far as I can see you don't need the TIME() function calls here as TIMEDIFF does also accept a TIMESTAMP. However, that's still not an option since TIMEDIFF() returns a time and not a signed integer or float. And the time seems to be in the range '00:00:00' - '23:59:59' i.e. not signed. That would make more comparisons necessary - plus you would have to calculate the seconds from the result time in this case, too. Sounds more complicated to me. > Anyway for the combination of DATEDIFF/TIMEDIFF, there is no built-in that > does > excactly what you want. Ah, ok. Then I leave the function as it is now. Thanks! robert > > 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] > -- "Sie haben neue Mails!" - Die GMX Toolbar informiert Sie beim Surfen! Jetzt aktivieren unter http://www.gmx.net/info -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
