Different timezones in an application with PostgreSQL

Question

Different timezones in an application with PostgreSQL

Navigation

#1 by (1 votes)

6

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

Various events can be created and stored (with date and time) using our system in a customer's centers. The ideal behavior for different timezones on the same client is as follows:

Given that an event happens in a center that is at timezone A, at noon. If a supervisor of a center that is in timezone B is looking at the date / time of this event, it should view that formatted date / time respecting timezone where the event occurred (including daylight saving time of the event location, if any). That's because what matters is whether an event was locally (at the timezone where it was made) performed at noon (or any other sample time). For the supervisor, it is not important to know that at noon when the event was done it was 2pm where he was.

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

Another scenario that may be very rare to happen but is still possible, is the case of a center being geographically changed, thus impacting the change of your timezone.

According to my research, the best thing to do seems to be saving the timezone each center has in a column in the CENTRO_DISTRIBUICAO table. Change all types TIMESTAMP in our database to TIMESTAMPTZ and at the time of each insert of something that saves date / time use the timezone of the center that is inserting the data to save together the timezone offset (since the TIMESTAMPTZ does not save the timezone itself, but only the offset).

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

If I resolve to follow this approach, I'll need to change the type of all columns in the database from TIMESTAMP to TIMESTAMPTZ . You'll also need to re-create all the views that depend on those columns as the types are being changed. In addition, you'll need to change all the queries that handle these columns to apply the center timezone using AT TIMEZONE .

The bank currently has timezone America/Sao_Paulo and my fear is that you end up doing something wrong by performing the column switching to TIMESTAMPTZ . Can this cast end up with the consistency of timestamps already stored? What is the correct way to do this, should I first cast or first change the timezone of the bank to UTC ?

Is the solution I described the best way to deal with the problem?

Does this approach also correctly handle daylight saving time for each location?

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

postgresql

asked by anonymous 21.02.2018 / 16:58

1 answer

Can I create an ExpandableListView with the swipe effect? .bat Settings that runs a .dstx

score 1 · Answer 1

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

The idea here is: Each client would be responsible for adjusting its timezone , and the server would remain with its default timezone , 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 check the% com_configured on the client:

SHOW TIMEZONE;

Here's a practical example illustrating the idea:

1) Creation of a test table TIMEZONE containing a field of type tb_foobar :

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

2) Each Distribution Center makes TIMESTAMP WITH TIME ZONE using its INSERT :

-- 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 report generation, each distribution center has a% configured_configure:

São Paulo:

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

Output:

Greenwich:

--GERACAODERELATORIONOCENTRODEDISTRIBUCAO#5SETTIMEZONETO'GMT';SELECT*FROMtb_foobarORDERBYid;

Output: