Firebirrd 5.0

[Cem Akkus]

Hi there,

I've been having an issue with Firebird 5. When I execute a query about Time zones, it works. When I use the same query in a function, it doesn't work. Let me explain :

If I write this query :
SELECT CAST('2023-02-24 16:00:00' AS TIMESTAMP) AT TIME ZONE 'UTC' FROM RDB$DATABASE
it returns : 2023-02-24 21:00:00.0000 UTC
So, it works.

But If I write this Function :

CREATE OR ALTER FUNCTION LOCAL2UTC  (
  GIVEN_TS Timestamp)
 RETURNS Timestamp
AS
DECLARE VARIABLE RESULT TIMESTAMP;
DECLARE VARIABLE STRRESULT VARCHAR(80);
BEGIN
  SELECT CAST(:GIVEN_TS AS TIMESTAMP) AT TIME ZONE 'UTC' FROM RDB$DATABASE INTO :RESULT;  
  --EXECUTE STATEMENT 'SELECT CAST(''' || :GIVEN_TS || ''' AS TIMESTAMP) AT TIME ZONE ''UTC'' FROM RDB$DATABASE' INTO :RESULT;
                                     
  STRRESULT=SUBSTRING(CAST(RESULT AS VARCHAR(80)) FROM 1 FOR 19);
  RESULT = CAST(STRRESULT AS TIMESTAMP);
  RETURN RESULT;  
END

and Use the same query with a parameter, it doesn't work and returns the given value.

When I write SELECT LOCAL2UTC('2023-02-24 16:00:00') FROM RDB$DATABASE

I get 

2023-02-24 4:00:00 PM

which is the same value I give! I tried several approaches in the function ie. using Execute Statement or converting returned data type to Varchar etc. but it didn't work.

Can anyone help me with this please?

Thank you

[Mark Rotteveel]

In the first case your end result is a TIMESTAMP WITH TIME ZONE, and in
the second case, the result is converted from TIMESTAMP [WITHOUT TIME
ZONE] to a TIMESTAMP WITH TIME ZONE and then back to a TIMESTAMP
[WITHOUT TIME ZONE].

If you want the behaviour you expect, you should declare the function to
return TIMESTAMP WITH TIME ZONE.

As an aside, the body of your stored procedure can be simplified to

RETURN GIVEN_TS AT TIME ZONE 'UTC';

There is no need for a select, nor is there a need for a cast, nor an
intermediate RESULT variable..

Mark
--
Mark Rotteveel