From: Robert (bob.newsgmx.net)
Date: Wed Jun 23 2004 - 07:09:13 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]