Timestamp Difference in Seconds

[Robert]

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]