I want to get unix time on Firebird

Question

iwant to get the time in firebird similar to GetTime on Javascript i have created one but its not that accurate the time difference increment everyday

CREATE PROCEDURE GETTIMEINMILLISECONDS
RETURNS (
  MILL VARCHAR(50)
)
AS
  DECLARE VARIABLE VAR_PARAM INTEGER;
BEGIN
select
(((extract(year from current_timestamp)
  -extract(year from cast('01/01/1970 00:00:00' as timestamp))) *  31536000000) +
(extract(month from current_timestamp) * 2592000000) +
(extract(day from current_timestamp)   *  8640000) +
(extract(Hour from current_timestamp)   * 3600000) +
 (extract(minute from current_timestamp) * 60000) +
  (extract(second from current_timestamp) * 1000))-192960000
  from rdb$database  into :Mill;
  suspend;
END
;

Answer 1

On Firebird 2.1 and higher you can use

select DATEDIFF(second, timestamp '1/1/1970 00:00:00', current_timestamp)
from rdb$database

For earlier version you can use the UDF F_AGEINSECONDS from FreeAdhocUDF:

select F_AGEINSECONDS('1/1/1970 00:00:00', current_timestamp) from
rdb$database

See Re: [firebird-support] How to convert TIMESTAMP to unix timestamp (number of seconds since epoch)

You will need to account for your current timezone by adding the timezone offset to the timestamp if you are not in GMT. For exampe, for CEST (+2:00), you will need to use:

select DATEDIFF(second, timestamp '1970-01-01 02:00:00', current_timestamp)
from rdb$database

(note I switched to ISO-8601 date format here)

In Firebird 4.0 you can avoid this correction by using a TIMESTAMP WITH TIME ZONE literal:

select DATEDIFF(second, timestamp '1970-01-01 00:00:00 UTC', current_timestamp)
from rdb$database