I use the following procedures, DateDiff is the last one and relies on
the others.

HIH

Si

-------------- SQL START -------------------------
--DECODEDATE. Retrieves the individual elements of a date (year,
month and days) from a TIMESTAMP.

CREATE EXCEPTION EXC_INVALID_YEAR 'Invalid Year Value';
CREATE EXCEPTION EXC_INVALID_MONTH 'Invalid Month Value';
CREATE EXCEPTION EXC_INVALID_DAY 'Invalid Day Value';
CREATE EXCEPTION EXC_INVALID_TIMESTAMP 'TimeStamp value can not be null';
CREATE EXCEPTION EXC_INVALID_PERIOD 'Invalid Date Difference period';

COMMIT;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE DECODEDATE(ipDATE TIMESTAMP)
RETURNS
(
opYEAR INTEGER,
opMONTH INTEGER,
opDAY INTEGER
)
AS
BEGIN
opYEAR = EXTRACT(YEAR FROM ipDATE);
opMONTH = EXTRACT(MONTH FROM ipDATE);
opDAY = EXTRACT(DAY FROM ipDATE);
SUSPEND;
END ^

SET TERM ; ^

COMMIT;




--ENCODEDATE. Creates a TIMESTAMP from a year, month and day.

SET TERM ^ ;
CREATE OR ALTER PROCEDURE ENCODEDATE(ipYEAR INTEGER,
ipMONTH INTEGER, ipDAY INTEGER)
RETURNS
(
opDATE TIMESTAMP
)
AS
BEGIN
IF (ipYEAR IS NULL) THEN
EXCEPTION EXC_INVALID_YEAR;

IF ((ipMONTH IS NULL) OR (ipMONTH < 1) OR (ipMONTH > 12)) THEN
EXCEPTION EXC_INVALID_MONTH;

IF ((ipDAY IS NULL) OR (ipDAY < 1) OR (ipDAY > 31)) THEN
EXCEPTION EXC_INVALID_DAY;

opDate = CAST(CAST(ipYEAR AS CHAR(4)) || '.' || CAST(ipMONTH AS VARCHAR(2))
|| '.' || CAST(ipDAY AS VARCHAR(2)) AS TIMESTAMP);

SUSPEND;
END ^

SET TERM ; ^

COMMIT;




--ENCODEDATETIME. Creates a TIMESTAMP from individual day, month,
year, hours, minutes and second values.

SET TERM ^ ;
CREATE OR ALTER PROCEDURE ENCODEDATETIME(ipYEAR INTEGER,
ipMONTH INTEGER, ipDAY INTEGER, ipHOURS INTEGER,
ipMINUTES INTEGER, ipSECONDS INTEGER,
ipMILLISECONDS INTEGER)
RETURNS
(
opDATE TIMESTAMP
)
AS
BEGIN
IF (ipYEAR IS NULL) THEN
EXCEPTION EXC_INVALID_YEAR;

IF ((ipMONTH IS NULL) OR (ipMONTH < 1) OR (ipMONTH > 12)) THEN
EXCEPTION EXC_INVALID_MONTH;

IF ((ipDAY IS NULL) OR (ipDAY < 1) OR (ipDAY > 31)) THEN
EXCEPTION EXC_INVALID_DAY;

opDate = CAST(CAST(ipYEAR AS CHAR(4)) || '.' || CAST(ipMONTH AS VARCHAR(2))
|| '.' || CAST(ipDAY AS VARCHAR(2)) || ' ' || CAST(ipHOURS AS VARCHAR(2))
|| ':' || CAST(ipMINUTES AS VARCHAR(2)) || ':' || CAST(ipSECONDS
AS VARCHAR(2))
|| ':' || CAST(ipMILLISECONDS AS VARCHAR(4)) AS TIMESTAMP);

SUSPEND;
END ^

SET TERM ; ^

COMMIT;




--DECODEDATETIME. Given a TIMESTAMP returns the individual elements
(years, months etc).

SET TERM ^ ;
CREATE OR ALTER PROCEDURE DECODEDATETIME(ipDATETIME TIMESTAMP)
RETURNS
(
opYEAR INTEGER, opMONTH INTEGER, opDAY INTEGER, opHOURS INTEGER,
opMINUTES INTEGER, opSECONDS INTEGER, opMILLISECONDS INTEGER
)
AS
BEGIN
IF (ipDATETIME IS NULL) THEN
EXCEPTION EXC_INVALID_TIMESTAMP;

opYEAR = EXTRACT(YEAR FROM ipDATETIME);
opMONTH = EXTRACT(MONTH FROM ipDATETIME);
opDAY = EXTRACT(DAY FROM ipDATETIME);
opHOURS = EXTRACT(HOUR FROM ipDATETIME);
opMINUTES = EXTRACT(MINUTE FROM ipDATETIME);
opSECONDS = EXTRACT(SECOND FROM ipDATETIME);
opMILLISECONDS = 0;--EXTRACT(MILLISECOND from ipDATETIME);

SUSPEND;
END ^

SET TERM ; ^

COMMIT;



--ENDODETIME. Encodes hours, minutes and seconds into a TIMESTAMP.
SET TERM ^ ;
CREATE OR ALTER PROCEDURE ENCODETIME(ipDATE TIMESTAMP, ipHOURS INTEGER,
ipMINUTES INTEGER, ipSECONDS INTEGER, ipMILLISECONDS INTEGER)
RETURNS
(
opDATE TIMESTAMP
)
AS
DECLARE VARIABLE vYear INTEGER;
DECLARE VARIABLE vMonth INTEGER;
DECLARE VARIABLE vDay INTEGER;
BEGIN
EXECUTE PROCEDURE DECODEDATE(ipDATE) RETURNING_VALUES :vYear, :vMonth, :vDay;
EXECUTE PROCEDURE ENCODEDATETIME(vYear, vMonth, vDay, ipHOURS, ipMINUTES,
ipSECONDS, ipMILLISECONDS) RETURNING_VALUES :opDATE;

SUSPEND;
END ^

SET TERM ; ^

COMMIT;




--DECODETIME. Retrieves the individual time elements from a TIMESTAMP.
SET TERM ^ ;

CREATE OR ALTER PROCEDURE DECODETIME(ipTIME TIMESTAMP)
RETURNS
(
opHOURS INTEGER,
opMINUTES INTEGER,
opSECONDS INTEGER,
opMILLISECONDS INTEGER
)
AS
BEGIN
IF (ipTIME IS NULL) THEN
EXCEPTION EXC_INVALID_TIMESTAMP;

opHOURS = EXTRACT(HOUR FROM ipTIME);
opMINUTES = EXTRACT(MINUTE FROM ipTIME);
opSECONDS = EXTRACT(SECOND FROM ipTIME);
opMILLISECONDS = 0;--EXTRACT(MILLISECOND from ipTIME);
SUSPEND;
END ^

SET TERM ; ^

COMMIT;




--DATEDIFF. Returns the difference between two dates. The difference
can be in Years, Months, Weeks, Days, Hours, Minutes or Seconds.
SET TERM ^ ;
CREATE OR ALTER PROCEDURE DATEDIFF(ipPERIOD SMALLINT, ipDATE1 TIMESTAMP,
ipDATE2 TIMESTAMP)
RETURNS
(
opDIFFERENCE BIGINT
)
AS
DECLARE VARIABLE Y1 INTEGER;
DECLARE VARIABLE M1 INTEGER;
DECLARE VARIABLE D1 INTEGER;
DECLARE VARIABLE H1 INTEGER;
DECLARE VARIABLE N1 INTEGER;
DECLARE VARIABLE S1 INTEGER;
DECLARE VARIABLE MS1 INTEGER;
DECLARE VARIABLE Y2 INTEGER;
DECLARE VARIABLE M2 INTEGER;
DECLARE VARIABLE D2 INTEGER;
DECLARE VARIABLE H2 INTEGER;
DECLARE VARIABLE N2 INTEGER;
DECLARE VARIABLE S2 INTEGER;
DECLARE VARIABLE MS2 INTEGER;
DECLARE VARIABLE DAYS1 INTEGER;
DECLARE VARIABLE DAYS2 INTEGER;
BEGIN
EXECUTE PROCEDURE DECODEDATETIME(ipDATE1) RETURNING_VALUES Y1, M1,
D1, H1, N1, S1, MS1;
EXECUTE PROCEDURE DECODEDATETIME(ipDATE2) RETURNING_VALUES Y2, M2,
D2, H2, N2, S2, MS2;

opDIFFERENCE = 0;

IF (ipPERIOD < 4) THEN
BEGIN
DAYS1 = CAST((Y1 * 365.25) + (M1 * 12) + D1 AS INTEGER);
DAYS2 = CAST((Y2 * 365.25) + (M2 * 12) + D2 AS INTEGER);
END


IF (ipPERIOD = 0) THEN -- Seconds
opDIFFERENCE = (((((DAYS1 - DAYS2) * 86400) -
((H2 - H1) * 3600))) - ((M2 - M1) * 60)) - (S2 - S1);
ELSE IF (ipPERIOD = 1) THEN -- Minutes
opDIFFERENCE = (((DAYS1 - DAYS2) * 1440) -
((H2 - H1) * 60)) - (M2 - M1);
ELSE IF (ipPERIOD = 2) THEN -- Hours
opDIFFERENCE = ((DAYS1 - DAYS2) * 24) - (H2 - H1);
ELSE IF (ipPERIOD = 3) THEN -- Days
opDIFFERENCE = (DAYS1 - DAYS2);
ELSE IF (ipPERIOD = 4) THEN -- Weeks
opDIFFERENCE = (ipDATE1 - ipDATE2) / 7;
ELSE IF (ipPERIOD = 5) THEN -- Months
opDIFFERENCE = ((Y1 - Y2) * 12) + (M1 - M2);
ELSE IF (ipPERIOD = 6) THEN -- Years
opDIFFERENCE = Y1 - Y2;
ELSE
EXCEPTION EXC_INVALID_PERIOD;

SUSPEND;
END ^

SET TERM ; ^

COMMIT;
------------------------- SQL END -------------------------------------

On Thu, Oct 2, 2008 at 10:40 AM, PenWin <penwin@...> wrote:
> Hi!
>
> What is the proper way to calculate a time difference in minutes
> (seconds, hours, years...) between two timestamps? I am using Firebird
> 1.5 and I would very much like to manage this task just by using PL/SQL,
> without resorting to UDFs.
>
> Originally I wrote this procedure:
>
> CREATE PROCEDURE diff (time1 TIMESTAMP, time2 TIMESTAMP)
> RETURNS (min_diff INTEGER)
> AS BEGIN
> time1 = time1-time2;
> min_diff = time1*60*24;
> SUSPEND;
> END
>
> It does not work, complains when I try to compile it:
> "SQL error code = -607
> Array/BLOB/DATE data types not allowed in arithmetic"
>
> So I modified it with CAST to DOUBLE PRECISION:
>
> ...
> min_diff = CAST(time1 AS DOUBLE PRECISION)*60*24;
> ...
>
> This compiles OK, but when I try to execute the procedure:
> SELECT * FROM diff(field1, field2)
> I get an error message:
> "conversion error from string "0.013194444""
>
> Now that value 0.013194444 corresponds to the expected difference
> between the two fields I used. What I don't understand is why I am
> getting this error and why it is a conversion from string. I suspect it
> might be related to the fact that my Windows locale sets a different
> decimal separator than ".", but everywhere else SQL works just fine with
> the dot (as expected). Any ideas?
>
> Thanks,
>
> Pepak
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>