I need to multiply by 2 an HOUR TO MINUTE interval.
Having this columns:

"MananaDesde" TIME NOT NULL,
"MananaHasta" TIME NOT NULL,
"MananaIntervalo" INTERVAL HOUR TO MINUTE COMPUTED ALWAYS AS CAST("MananaHasta" - "MananaDesde" AS INTERVAL HOUR TO MINUTE),

need in some circunstances something similar to

CAST(CAST(CAST(MananaIntervalo AS INTERVAL MSECOND) AS BIGINT) * 2 AS INTERVAL HOUR TO MINUTE)

This complex CAST powered to three does not work, but is the more similar to success I got.

Any idea?

Tiago,

<< I need to multiply by 2 an HOUR TO MINUTE interval. >>

You cannot do so.  You must multiple the value by an integer-based INTERVAL
value, not an INTERVAL value that is comprised of more than one type of
INTERVAL.  For example, you can multiple these INTERVAL types:

INTERVAL HOUR
INTERVAL MINUTE
INTERVAL SECOND
INTERVAL MSECOND

<< need in some circunstances something similar to

CAST(CAST(CAST(MananaIntervalo AS INTERVAL MSECOND) AS BIGINT) * 2 AS
INTERVAL HOUR TO MINUTE)

This complex CAST powered to three does not work, but is the more similar
to success I got. >>

I'm not quite sure that the above sentence means, but I just tried this:

SELECT CAST(CAST(21660000 * 2 AS INTERVAL MSECOND) AS INTERVAL HOUR TO
MINUTE)
FROM customer

and it works just fine.  You'll notice that once the INTEGER value is CAST
into a DAY-TIME INTERVAL, in this case MSECOND, that it can then be CAST
into any compatible DAY-TIME INTERVAL.  However, you cannot directly cast
the INTEGER value into an HOUR TO MINUTE DAY-TIME INTERVAL because there
isn't any context as to what the INTEGER represents.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,


<<You cannot do so.  You must multiple the value by an integer-based INTERVAL
value, not an INTERVAL value that is comprised of more than one type of
INTERVAL.  For example, you can multiple these INTERVAL types:

INTERVAL HOUR
INTERVAL MINUTE
INTERVAL SECOND
INTERVAL MSECOND >>

<<SELECT CAST(CAST(21660000 * 2 AS INTERVAL MSECOND) AS INTERVAL HOUR TO
MINUTE)
FROM customer

and it works just fine. >>

Ok. This run as spected. I saw the difference between simple and complex intervals (DAY vs HOUR TO ...). But changing de const 21660000 by the
value runs but generates an astronomic result:

SELECT
CAST(MananaIntervalo AS INTERVAL MSECOND),
CAST(CAST(MananaHasta - MananaDesde AS INTERVAL MSECOND) * 2 AS INTERVAL HOUR TO MINUTE)
FROM ...

returns  21840000 (right), 3773952000000000 (wich is not 21840000 * 2)

Tiago,

<< Ok. This run as spected. I saw the difference between simple and complex
intervals (DAY vs HOUR TO ...). But changing de const 21660000 by the value
runs but generates an astronomic result: >>

CAST() the result to a CHAR/VARCHAR if you want to see the actual value in
Delphi.  Remember, Delphi doesn't have native support for INTERVAL types, so
they are represented in their raw form without any interpretation.  In the
case of DAY-TIME INTERVALs, that means an Int64 representing milliseconds.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<CAST() the result to a CHAR/VARCHAR if you want to see the actual value in
Delphi.  Remember, Delphi doesn't have native support for INTERVAL types, so
they are represented in their raw form without any interpretation.  In the
case of DAY-TIME INTERVALs, that means an Int64 representing milliseconds.>>

Ok. This is not related to Delphi. It's pure EDB. My question is about if it'll be possible to make basic arithmetic operations on intervals inside
SQL/PSM.

Tiago,

> SELECT
> CAST(MananaIntervalo AS INTERVAL MSECOND),
> CAST(CAST(MananaHasta - MananaDesde AS INTERVAL MSECOND) * 2 AS INTERVAL
HOUR TO MINUTE)
> FROM ...
>
> returns  21840000 (right), 3773952000000000 (wich is not 21840000 * 2)
>

I see the same as you.  I don't know if this is a bug but I think you can
get the correct result by casting to a bigint before multiplying and back
again like

SELECT
CAST(MananaIntervalo AS INTERVAL MSECOND),
CAST(CAST(CAST(CAST(MananaHasta - MananaDesde AS INTERVAL MSECOND) AS
BIGINT) * 2 AS INTERVAL MSECOND) AS INTERVAL HOUR TO MINUTE)
FROM ...

John

Tiago,

<< Ok. This is not related to Delphi. It's pure EDB. My question is about if
it'll be possible to make basic arithmetic operations on intervals inside
SQL/PSM. >>

Sure, that should not be a problem.  If you have a specific example in
SQL/PSM that isn't working, just let me know and I'll take a look.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Sure, that should not be a problem.  If you have a specific example in
SQL/PSM that isn't working, just let me know and I'll take a look.>>

This is a fragment of a database. Please see gpHistor_BeforeUpdate comments:

CREATE TABLE "gpHistor"
(
"Fecha" DATE NOT NULL,
"Empleado" SMALLINT NOT NULL,
"MananaDesde" TIME NOT NULL,
"MananaHasta" TIME NOT NULL,
"MananaEstado" SMALLINT DEFAULT 0         NOT NULL DESCRIPTION '0 = Trabajo, 1 = Baja, 2 = Vacaciones',
"MananaIntervalo" INTERVAL HOUR TO MINUTE,
"TardeDesde" TIME NOT NULL,
"TardeHasta" TIME NOT NULL,
"TardeEstado" SMALLINT DEFAULT 0         NOT NULL DESCRIPTION '0 = Trabajo, 1 = Baja, 2 = Vacaciones',
"TardeIntervalo" INTERVAL HOUR TO MINUTE,
"ExtraDesde" TIME DEFAULT TIME '00:00'     NOT NULL,
"ExtraHasta" TIME DEFAULT TIME '00:00'     NOT NULL,
"ExtraIntervalo" INTERVAL HOUR TO MINUTE COMPUTED ALWAYS AS CAST("ExtraHasta" - "ExtraDesde" AS INTERVAL HOUR TO MINUTE),
"TotalDia" INTERVAL HOUR TO MINUTE COMPUTED ALWAYS AS MananaIntervalo + TardeIntervalo + ExtraIntervalo,
"TotalDiaStr" VARCHAR(5) COLLATE "ANSI" COMPUTED ALWAYS AS CAST("TotalDia" AS VARCHAR)

);

CREATE FUNCTION "EsFestivo" (IN "aDate" DATE, IN "bManana" BOOLEAN)
RETURNS BOOLEAN
BEGIN
 DECLARE wkAno SMALLINT DEFAULT EXTRACT(YEAR FROM aDate);
 DECLARE wkMes SMALLINT DEFAULT EXTRACT(MONTH FROM aDate);
 DECLARE wkDia SMALLINT DEFAULT EXTRACT(DAY FROM aDate);
 DECLARE MananaFestivo BOOLEAN;
 DECLARE TardeFestivo BOOLEAN;     
 DECLARE Result BOOLEAN;
 
 DECLARE CurMesPln CURSOR FOR StmMesPln;
 PREPARE StmMesPln FROM
   'SELECT MananaFestivo, TardeFestivo FROM gpMesPln WHERE Ano=? AND Mes=? AND Dia = ?';
 OPEN CurMesPln USING wkAno, wkMes, wkDia;
 -- Si no existeix sa data error
 IF BOF(CurMesPln) AND EOF(CurMesPln) THEN
   RAISE ERROR CODE 101 MESSAGE 'Todavía el usuario no ha creado la plantilla mensual para esta fecha';
 END IF;
 FETCH FIRST FROM CurMesPln INTO MananaFestivo, TardeFestivo;
 CLOSE CurMesPln;
 IF bManana THEN
   SET Result = MananaFestivo;
 ELSE
   SET Result = TardeFestivo;
 END IF;
 RETURN Result;
END;

CREATE TRIGGER "gpHistor_BeforeUpdate" BEFORE UPDATE ON "gpHistor"
BEGIN
 -- When EsFestivo is true, NEWROW.MananaIntervalo needs to be multiplied by 2
 -- At the moment, unique working solution was this:
 IF EsFestivo(NEWROW.Fecha,TRUE) THEN
   SET NEWROW.MananaIntervalo =
     CAST(NEWROW.MananaHasta - NEWROW.MananaDesde AS INTERVAL HOUR TO MINUTE) +
     CAST(NEWROW.MananaHasta - NEWROW.MananaDesde AS INTERVAL HOUR TO MINUTE);
     
   /* CAST(CAST(MananaHasta - MananaDesde AS INTERVAL MSECOND) * 2 AS INTERVAL HOUR TO MINUTE)  
      should work, but when CAST(MananaHasta - MananaDesde AS INTERVAL MSECOND) = 21840000
     it returns 3773952000000000 instead of 43680000
   */
   
 ELSE
   SET NEWROW.MananaIntervalo =
     CAST(NEWROW.MananaHasta - NEWROW.MananaDesde AS INTERVAL HOUR TO MINUTE);
 END IF;
 IF EsFestivo(NEWROW.Fecha,FALSE) THEN
   SET NEWROW.TardeIntervalo =
     CAST(NEWROW.TardeHasta - NEWROW.TardeDesde AS INTERVAL HOUR TO MINUTE) +
     CAST(NEWROW.TardeHasta - NEWROW.TardeDesde AS INTERVAL HOUR TO MINUTE);
 ELSE
   SET NEWROW.TardeIntervalo =
     CAST(NEWROW.TardeHasta - NEWROW.TardeDesde AS INTERVAL HOUR TO MINUTE);
 END IF;
END;

Tiago,

<< This is a fragment of a database. Please see gpHistor_BeforeUpdate
comments: >>

Okay, I'll check it out and see what I can find.

--
Tim Young
Elevate Software
www.elevatesoft.com