How do I convert an epoch into a datetime, taking into account the time zone?

Question

Can Firebird 4 convert an epoch to a datetime taking timezone into account?

Example:

Time zone is CET (+01:00).

SELECT
   EPOCH,
   SUBSTRING(CAST(DATEADD(SECOND,CAST(EPOCH AS BIGINT),TIMESTAMP '1970-01-01 00:00:00') AS VARCHAR(24)) FROM 1 FOR 19) AS DATETIME
FROM unix_epoch

Outputs

EPOCH	DATETIME
86400	1970-01-02 00:00:00

The required output

EPOCH	DATETIME
86400	1970-01-02 01:00:00

Answer 1

You've used too much casts, everything is simpler:

select dateadd(second, epoch, timestamp '1970-01-01 UTC') at time zone '+01:00' from unix_epoch;

Using timezone name:

select dateadd(second, epoch, timestamp '1970-01-01 UTC') at time zone 'CET' from unix_epoch;

Output:

AT
=========================================================
1970-01-02 01:00:00.0000 +01:00

AT
=========================================================
1970-01-02 01:00:00.0000 CET