Firebird 5.0.3, problem with current_time

[Tomasz Dubiel]

Hello.

When we have DataTypeCompatibility set to 3.0, we have a problem:

execute block
returns (
    ZMIENNA_OUT integer)
as
declare variable ZMIENNA time;
begin
  ZMIENNA = '00:10:00';
  ZMIENNA_OUT = 0;
  if (current_time < ZMIENNA) then
    ZMIENNA_OUT = 1;
  suspend;
end

Why does it return 1?

When we change it to:

execute block
returns (
    ZMIENNA_OUT integer)
as
declare variable ZMIENNA time;
declare variable ZMIENNA2 time;
begin
  ZMIENNA = '00:10:00';
  ZMIENNA_OUT = 0;
  ZMIENNA2 = current_time;
  if (ZMIENNA2 < ZMIENNA) then
    ZMIENNA_OUT = 1;
  suspend;
end

then we receive expected result.

What is wrong here?

Best regards,

Tomek.

[Dmitry Yemanov]

ZMIENNA and ZMIENNA2 are defined as TIME WITHOUT TIME ZONE, while
CURRENT_TIME returns TIME WITH TIME ZONE. Replace CURRENT_TIME with
LOCALTIME or declare variables as TIME WITH TIME ZONE (and specify the
time zone for ZMIENNA).


Dmitry

[Mark Rotteveel]

On 25/07/2025 08:50, Tomasz Dubiel wrote:
> Hello.
> When we have DataTypeCompatibility set to 3.0, we have a problem:

The datatype compatability only applies on the edge (communication
between client and server). If you want to use TIME [WITHOUT TIME ZONE],
switch to using LOCALTIME, not CURRENT_TIME (and LOCALTIMESTAMP not
CURRENT_TIMESTAMP for TIMESTAMP [WITHOUT TIME ZONE]).

The difference, IIRC, is that in the first case the timezoneless value
is converted to a TIME WITH TIME ZONE to perform the comparison, and
IIRC this uses the offset on January 1st 2020, making the time off by 1
hour compared to your expectation.

In the second case, you're converting the TIME WITH TIME ZONE to a TIME
[WITHOUT TIME ZONE].

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

I don't quite get it.

"Specifies the compatibility level that defines what SQL data types can be exposed to the client API. Currently two options are available: "3.0" and "2.5". The "3.0" emulation mode hides data types introduced after Firebird 3.0 release, in particular DECIMAL/NUMERIC with precision 19 or higher, DECFLOAT, TIME/TIMESTAMP WITH TIME ZONE." ".."

"This setting allows legacy client applications to work with Firebird 4.0 without recompiling and adjusting them to understand the new data types"

 So I would say that with datatypecompatibility set to 3.0, I should be able to work with Firebird 5.0 just as I would work with Firebird 3.0, "without recompiling and adjusting them to understand the new data types".

[Mark Rotteveel]

On 25/07/2025 10:26, Tomasz Dubiel wrote:
> I don't quite get it.
> "Specifies the compatibility level that defines what SQL data types can
> be exposed to the client API. Currently two options are available: "3.0"
> and "2.5". The "3.0" emulation mode hides data types introduced after
> Firebird 3.0 release, in particular DECIMAL/NUMERIC with precision 19 or
> higher, DECFLOAT, TIME/TIMESTAMP WITH TIME ZONE." ".."
> "This setting allows legacy client applications to work with Firebird
> 4.0 without recompiling and adjusting them to understand the new data types"
>  So I would say that with datatypecompatibility set to 3.0, I should be
> able to work with Firebird 5.0 just as I would work with Firebird 3.0,
> "without recompiling and adjusting them to understand the new data types".

The only thing that DataTypeCompatibility affects is _"what SQL data
types can be exposed to the client API"_

The thing you're also missing is that the setting talks about not
needing to change _"legacy client applications"_. PSQL code is not
client code, it is server-side code.

Mark
--
Mark Rotteveel