Modifying CURRENT_TIMESTAMP per Firebird database

Question

I have many databases on the same server, representing different time zones in each one (pacific, mountain, central and eastern). Right now my programs use things like 'NOW' and CURRENT_TIMESTAMP to get the current time for the records. This is a problem.

The data is being synchronized from separate computers to this central server using SymmetricDS. However, I'm having the problem that the programs being run at this central server are written as if they were local to the office. This means that when it calls for timestamps, it does it at the time zone of the server that may not represent their office. Certain data continuity is being jumbled as a result.

Aside from going through the programs and rewriting every situation that utilizes the current time, we would much rather find a way to represent time differently in each database... somehow offsetting the timezone in each database separately.

Having done as much reading as I can find relevant to this topic, what are my options?

Answer 1

I think you should create a procedure for this task.

First of all you could start with a new table. Let's use the following structure:

Moreover let's create the following stored procedure:

create or alter procedure GETTIMESTAMP (
    IOFFICE_ID bigint)
returns (
    RTIMESTAMP timestamp)
as
begin
  RTIMESTAMP=dateadd(hour,coalesce((select TIMEDIFF from OFFICE_TIMEZONE where OFFICE_ID_=:IOFFICE_ID),0), current_timestamp);
  suspend;
end

This procedure will return the correct time. If the office does not exsits, it will return the current_timestamp of the local server.

Of course it takes some work to get this into your database. To make it easy you could search in your database metadata. IBExpert features a simple search dialog, but you could also export your meta data and search via notepad.