Hi,

ALTER TABLE "mytimes"
ADD COLUMN "c_wartezeit" timestamp COMPUTED ALWAYS AS CASE
when verladen_start is null and ankunft is null then 0
when ankunft is not null and verladen_start is null then current_timestamp()-ankunft
when ankunft is not null and verladen_start is not null then verladen_start-ankunft
end

is not working:
ElevateDB Error #700 An error was found in the c_wartezeit computed column expression at line 3 and column 58 (Expected NULL, SmallInt, Integer, BigInt, Float, Decimal, Interval Year, Interval Day, Interval Hour, Interval Minute, Interval Second, or Interval MSecond expression but instead found CURRENT_TIMESTAMP() - "ankunft")

but if i create it as a integer-field it is ok:
ALTER TABLE "mytimes"
ADD COLUMN "c_wartezeit" integer COMPUTED ALWAYS AS CASE
when verladen_start is null and ankunft is null then 0
when ankunft is not null and verladen_start is null then cast(current_timestamp()-ankunft as interval minute)
when ankunft is not null and verladen_start is not null then cast(verladen_start-ankunft as interval minute)
end

So the question is where is the bug, my script or in edb?
Thanks for some hits into the right direction.
--
Yusuf Zorlu
MicrotronX

Yusuf,

Not knowing what type is "ankunft" I can't tell if the expression is correct but in any case "0" is not a valid TIMESTAMP value for "c_wartezeit".

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

<< Not knowing what type is "ankunft" I can't tell if the expression is correct but in any case "0" is not a valid TIMESTAMP value for "c_wartezeit".


Hi Fernando,

all fields are timestamp fields. i mean, i can change "0" to NULL but works also not!
--
Yusuf Zorlu
MicrotronX

If i change it from "0" to NULL i get following error with this:

ALTER TABLE "mytimes"
ADD COLUMN "c_wartezeit4" timestamp COMPUTED ALWAYS AS
CASE
  when verladen_start is null and ankunft is null then NULL
  when ankunft is not null and verladen_start is null then current_timestamp()-ankunft
  when ankunft is not null and verladen_start is not null then verladen_start-ankunft
END

ElevateDB Error #700 An error was found in the c_wartezeit4 computed column expression at line 1 and column (Expected NULL, Date, or Timestamp expression but instead found CASE  WHEN "ankunft" IS NOT NULL AND "verladen_start" IS NULL THEN CURRENT_TIMESTAMP() - "ankunft" WHEN "ankunft" IS NOT NULL AND "verladen_start" IS NOT NULL THEN "verladen_start" - "ankunft" ELSE NULL END)
--
Yusuf Zorlu
MicrotronX

Yusuf,

A difference between 2 timestamp values is always of type INTERVAL DAY TO MSECOND, not INTEGER or TIMESTAMP.
Your second query is working only because you are converting the result to INTERVAL MINUTE that can be expressed as an integer value, that is the number of minutes between the timestamps.

What do you expect to obtain as the result from the expression current_timestamp()-ankunft ?

--
Fernando Dias
[Team Elevate]
 

Fernando Dias wrote:

>>What do you expect to obtain as the result from the expression current_timestamp()-ankunft ?


I need a time value in HH:NN as a result for the difference
--
Yusuf Zorlu
MicrotronX

Then the result should be of of type INTERVAL HOUR TO MINUTE and not TIMESTAMP.

--
Fernando Dias
[Team Elevate]

You might want to revisit an answer from Tim Young to yourself on how to deal with interval type display/edition here:

https://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=7760#7760


--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:


Then the result should be of of type INTERVAL HOUR TO MINUTE and not TIMESTAMP.

--

Thanks Fernando. I'm new to interval type an I have not found specific examples in pure SQL.
--
Yusuf Zorlu
MicrotronX