How to convert from Unix time to datetime in Firebird?

Question

Does Firebird have something similar to MySQL's FROM_UNIXTIME function for converting Unix time to datetime?

Answer 1

There is no separate built-in function to do this in Firebird. The solution is to use DATEADD against the epoch:

dateadd(<your-unix-timestamp> second to timestamp '1970-01-01 00:00:00')

or

dateadd(second, <your-unix-timestamp>, timestamp '1970-01-01 00:00:00')

For example:

SQL> select dateadd(1655563246 second to timestamp '1970-01-01 00:00:00') from rdb$database;

                  DATEADD
=========================
2022-06-18 14:40:46.0000

Important caveat: the result is a TIMESTAMP, which is without timezone. Interpretation varies by platform (e.g. Java will interpret it in the current JVM timezone). In Firebird 4.0, you can make it a TIMESTAMP WITH TIME ZONE at UTC by using timestamp '1970-01-01 00:00:00 UTC' instead.