How to make a different time zone in different databases on the same server

Dear Habravchane, I decided to share with you a technical solution of some interesting and at first glance not obvious problems. All my life, I basically just scooped up information, read wonderful articles (including on Habré) helped me a lot in solving various technical problems. But in the last 2 years, having started developing my own cloud-based enterprise automation platform, I had to solve quite a lot of tasks “for the first time”, i.e. which, if someone decided, I did not find descriptions (even close). In this regard, I decided to write 3 cycles of articles: a cycle on databases, a cycle on security and a cycle on automation systems. This article is the first article of the first cycle.

A brief statement of the problem : somehow current_date, current_time and current_timestamp should give date-time, depending on the settings in the user account and at the same time the Moscow time will be on the server itself.

The system was conceived infinitely scalable, with a huge number of potential users in any region of our vast country, at some point the question arose whether it would be convenient for users, for example in Vladivostok, to work in a system with Moscow time. Servers are located in Moscow, Moscow time is on them, and each user (no matter what region he is from) is allocated several of his (individual) databases and all of them are, roughly speaking, on the same server. It’s stupid to do your server for every time zone, and it’s not possible from the point of view of the system concept, because any user can change the time zone at any time.

The type of database currently used is firebird 3
')
After a day of throwing and studying manuals, the following simple solution was found. 3 simple VIEWs were created:

CREATE OR ALTER VIEW D_CURRENT_DATE(DD) AS SELECT cast((current_timestamp+0.000000000000000)as date) as dd FROM RDB$DATABASE; CREATE OR ALTER VIEW D_CURRENT_TIME(DD) AS SELECT cast((current_timestamp+0.000000000000000)as time) as dd FROM RDB$DATABASE; CREATE OR ALTER VIEW D_CURRENT_TIMESTAMP(DD) AS SELECT (current_timestamp+0.000000000000000) as dd FROM RDB$DATABASE; 

The above VIEW - for Moscow time, which is the default. If the user selects a time zone different from Moscow time, for example “UTC + 7 Krasnoyarsk time”, the php script (with the help of which the user selects it) changes the VIEW in his database as follows.

 CREATE OR ALTER VIEW D_CURRENT_DATE(DD) AS SELECT cast((current_timestamp+0.166666666666667)as date) as dd FROM RDB$DATABASE; CREATE OR ALTER VIEW D_CURRENT_TIME(DD) AS SELECT cast((current_timestamp+0.166666666666667)as time) as dd FROM RDB$DATABASE; CREATE OR ALTER VIEW D_CURRENT_TIMESTAMP(DD) AS SELECT (current_timestamp+0.166666666666667) as dd FROM RDB$DATABASE; 

Accordingly, from the base current_timestamp, the number of hours will be added (or subtracted), depending on the time zone that the user selects.

Further everywhere, in the code of all scripts, procedures, triggers instead of current_timestamp is always written d_current_timestamp. Due to this, all records in the database will always contain user time, not Moscow time, despite the fact that all databases are on the same server as Moscow time.