Different timezones in an application with Postgresql

Question

We have a system that serves several distribution centers. A distribution center is a physical location that can be located anywhere in the country. A single customer can have several centers. Our system can operate in all these centers. As we are now expanding to more locations, we will have to face the problem of different timezones. A same customer can also have centers with different timezones.

Several events can be created and stored (date and time) using our system in a customer’s center. The ideal behavior for different timezones in the same client is as follows:

Given that an event takes place in a center that is in Timezone A, at noon. If a supervisor of a center that is in Timezone B to look at the date/time of this event, it must view this date/time formatted respecting the Timezone where the event took place (including the daylight saving time of the event venue, if any). This is because what matters is whether an event was held locally (in the Timezone where it was made) at noon (or any other example time). For the supervisor, it is not important to know that at noon when the event was done was 14h where he was.

We use Postgresql as our database and I saw that there are two types timestamps to save date/time. One of them is the TIMESTAMP and the other is TIMESTAMPTZ. Our entire database uses only the type TIMESTAMP.

Another scenario that may be quite rare to happen but is still possible, is the case of a center to be changed geographically, with this impacting on the change of its Timezone.

According to my research, the most correct thing to do seems to be to save the Timezone that each center has in a column in the table CENTRO_DISTRIBUICAO. Trade all kinds TIMESTAMP in our bank to TIMESTAMPTZ and at the time of each input of something that saves date/time use the Timezone of the center that is entering the data to save along the offset of the Timezone (since the field TIMESTAMPTZ does not save the Timezone itself, but only the offset).

My question is whether this is really the best way and the right way to deal with these different timezones. Since I never implemented anything that needed this support, I can’t say.

If I decide to follow this approach, we will need to change the type of all columns in the TIMESTAMP for TIMESTAMPTZ. You will also need to recreate all the views that depend on these columns, as the types are being changed. Also, you’ll need to change all queries that handle these columns to apply the center Timezone using AT TIMEZONE.

The bank currently has as Timezone America/Sao_Paulo and my fear is to end up doing something wrong while performing the exchange of the columns to TIMESTAMPTZ. Can this cast end the consistency of timestamps already stored? Which is the correct way to do this, I must first cast or first change the bank Timezone to UTC?

The solution I described is the best way to deal with the problem?

This approach also deals correctly with each place’s daylight saving time?

Extra information: our server is java (jersey) and the front is both mobile and web.

Answer 1

You can change the environment variable TIMEZONE when the application (client) connects to the database.

The idea here is: Each customer would be responsible for adjusting their Timezone, and the server would remain with your Timezone standard, for example:

SET TIMEZONE TO 'Brazil/Acre';
SELECT NOW(); -- Horário oficial do Acre

SET TIMEZONE TO 'America/Sao_Paulo';
SELECT NOW(); -- Horário oficial de Brasília/São Paulo

SET TIMEZONE TO 'Brazil/DeNoronha';
SELECT NOW(); -- Horário oficial de Fernando de Noronha

SET TIMEZONE TO 'UTC';
SELECT NOW(); -- Universal Time Coordinated

SET TIMEZONE TO 'GMT';
SELECT NOW(); -- Greenwich Mean Time

To verify the TIMEZONE configured on the client:

SHOW TIMEZONE;

Follow a practical example illustrating the idea:

1) Creating a test table tb_foobar containing a field of the type TIMESTAMP WITH TIME ZONE:

CREATE TABLE public.tb_foobar
(
    id BIGINT, 
    datahora TIMESTAMP WITH TIME ZONE
);

2) Each Distribution Centre INSERT using their respective TIMEZONE:

-- CENTRO DE DISTRIBUICAO #1
SET TIMEZONE TO 'Brazil/Acre';
INSERT INTO tb_foobar ( id, datahora ) VALUES ( 1, now() );

-- CENTRO DE DISTRIBUICAO #2
SET TIMEZONE TO 'America/Sao_Paulo';
INSERT INTO tb_foobar ( id, datahora ) VALUES ( 2, now() );

-- CENTRO DE DISTRIBUICAO #3
SET TIMEZONE TO 'Brazil/DeNoronha';
INSERT INTO tb_foobar ( id, datahora ) VALUES ( 3, now() );

-- CENTRO DE DISTRIBUICAO #4
SET TIMEZONE TO 'UTC';
INSERT INTO tb_foobar ( id, datahora ) VALUES ( 4, now() );

-- CENTRO DE DISTRIBUICAO #5
SET TIMEZONE TO 'GMT';
INSERT INTO tb_foobar ( id, datahora ) VALUES ( 5, now() );

3) The same applies to reporting, each distribution centre has a TIMEZONE configured:

São Paulo:

-- GERACAO DE RELATORIO NO CENTRO DE DISTRIBUCAO #2
SET TIMEZONE TO 'America/Sao_Paulo';
SELECT * FROM tb_foobar ORDER BY id;

Exit:

Greenwich:

-- GERACAO DE RELATORIO NO CENTRO DE DISTRIBUCAO #5
SET TIMEZONE TO 'GMT';
SELECT * FROM tb_foobar ORDER BY id;

Exit: