Time zone in postgeSQL

Question

I use postgeSQL 12.3. The default timezone is 'US/Pacific'. When i use the Now() function, postgres returns the current time and date correctly but with the a time zone different from mine. I changed the time zone to be 'Africa/Cairo' via the command

SET TIMEZONE = 'Africa/Cairo';

After that, I called the function Now() again. It returned a wrong date and time but with a correct time zone. How can i fix this error?. I want the new time (the time after changing the time zone) to be the same as the old time (the time before changing the time zone). In other words, I want to change the time zone only from -7 to +02. What should I do?

Here is the output before changing the time zone

This is the output after changing the time zone to 'Africa/Cairo'

To verify what is happening, perhaps you can provide a screenshot of your terminal session along with a display of current time such a Time.is in a web browser. — Basil Bourque, Jul 09 '20 at 21:02
Just a note. Unlike Oracle, PostgreSQL does not store the time zone or time offset information; internally it just stores the timestamp in UTC and converts it back to the server time offset (never to the time zone) when read **on the fly**. — The Impaler, Jul 09 '20 at 21:08
It will not be 2300 in 'US/Pacific' for another eight-and-a-half hours. It looks like the server hosting your DB server has its timezone set to 'US/Pacific' and then was forced to show 'Africa/Cairo' time. — Mike Organek, Jul 09 '20 at 21:33

Adrian Klaver · Answer 1 · 2020-07-10T01:02:22.107

I am in 'US/Pacific' so:

select now();
              now               
--------------------------------
 07/09/2020 17:23:19.817048 PDT

test(5432)=> SET TIMEZONE = 'Africa/Cairo';
SET

test(5432)=> select now();
              now               
--------------------------------
 07/10/2020 02:24:02.617442 EET
(1 row)

test(5432)=> select now() at time zone  'US/Pacific';
          timezone          
----------------------------
 07/09/2020 17:24:21.577493
(1 row)

UPDATE. Example of timestamp, timestamptz:

ts_test 
                        Table "public.ts_test"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 ts_tz  | timestamp with time zone    |           |          | 
 ts     | timestamp without time zone |           |          | 
 ts_txt | character varying           |           |          | 

show timezone;
  TimeZone  
------------
 US/Pacific

test(5432)=# insert into ts_test values(localtimestamp, localtimestamp, localtimestamp);
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/09/2020 17:57:26.005347 PDT | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347

test(5432)=# SET TIMEZONE = 'Africa/Cairo';
SET
test(5432)=# insert into ts_test values(localtimestamp, localtimestamp, localtimestamp);
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/10/2020 02:57:26.005347 EET | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347
 07/10/2020 02:57:44.661465 EET | 07/10/2020 02:57:44.661465 | 07/10/2020 02:57:44.661465
(2 rows)

test(5432)=# insert into ts_test values('07/09/2020 5:45', '07/09/2020 5:45', '07/09/2020 5:45');
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/10/2020 02:57:26.005347 EET | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347
 07/10/2020 02:57:44.661465 EET | 07/10/2020 02:57:44.661465 | 07/10/2020 02:57:44.661465
 07/09/2020 05:45:00 EET        | 07/09/2020 05:45:00        | 07/09/2020 5:45

test(5432)=# SET TIMEZONE = default;
SET
test(5432)=# show timezone;
  TimeZone  
------------
 US/Pacific
(1 row)

test(5432)=# insert into ts_test values('07/09/2020 5:45', '07/09/2020 5:45', '07/09/2020 5:45');
INSERT 0 1
test(5432)=# select * from ts_test ;
             ts_tz              |             ts             |           ts_txt           
--------------------------------+----------------------------+----------------------------
 07/09/2020 17:57:26.005347 PDT | 07/09/2020 17:57:26.005347 | 07/09/2020 17:57:26.005347
 07/09/2020 17:57:44.661465 PDT | 07/10/2020 02:57:44.661465 | 07/10/2020 02:57:44.661465
 07/08/2020 20:45:00 PDT        | 07/09/2020 05:45:00        | 07/09/2020 5:45
 07/09/2020 05:45:00 PDT        | 07/09/2020 05:45:00        | 07/09/2020 5:45
(4 rows)

I don`t understand what the benefit of this. The problem is that the time zone is different when switching from US/Pacific to Africa/Cairo. I want time and date to remain as they were before changing the time zone, and just change the time zone from -7 to +02. By the way, I use the default server created by the pgAdmin. — Hussein Eid, Jul 10 '20 at 00:42
That can't happen, a fixed_timestamp -7 cannot be the same as fixed_timestamp +02 and you really don't want to work without timezones. — Adrian Klaver, Jul 10 '20 at 00:54
See update to answer to show effect of timestamp vs timestamptz. — Adrian Klaver, Jul 10 '20 at 01:02

score 1 · Answer 2 · answered Jul 10 '20 at 05:33

Without going into details about the workings of time zones in PostgreSQL (the other answers provided that), here is a solution. You take the local time without time zone and use AT TIME ZONE to interpret it in a different time zone:

SELECT localtime AT TIME ZONE 'Africa/Cairo';

The result will of course be displayed according to the current setting of timezone.

Basil Bourque · Answer 3 · 2020-07-10T02:59:52.657

Use `clock_timestamp()` for current moment

now() is a traditional PostgreSQL equivalent to transaction_timestamp() which in turn is equivalent to CURRENT_TIMESTAMP. These three commands return the moment when the transaction began. See manual.

To get the start time of the current statement, more specifically, the time of receipt of the latest command message from the client, call statement_timestamp().

To capture the moment when the function executes, call clock_timestamp().

Setting default time zone

You can set the default time zone in either of two ways, as discussed here:

SQL-standard: SET TIME ZONE 'Africa/Cairo' ;
Postgres-specific: SET timezone TO 'Africa/Cairo' ;

Example

Here is a screenshot showing three things:

Current moment as seen in UTC, in the web site Time.is.
A query showing current default time zone, and the current moment.
A query (a moment later, as I switched windows) showing a change to the current default time zone, along with a query for current zone and the current moment.

For this demo I used the managed database service by DigitalOcean.com for Postgres 12.

We can see in this shot that the current moment in UTC is 2:30 AM. The west coast time in US is 7:30 PM the prior day, seven hours behind UTC. The current default time zone in the first session is UTC (GMT), showing a correct time of 2:30 AM, in agreement with Time.is web site. The second session we switch to Africa/Cairo time zone, to find the current moment seen through that time zone is 4:30 AM, two hours ahead of UTC.

All this makes sense, and seems correct to me.

Based on other's comments, I suspect the system clock of your host computer is incorrect. I wonder if the time zone on the server is set to one time zone, but the actually time-of-day is set to the current time of a different time zone. This would only happen if manually set by a sysadmin, I expect, as the host OS should auto-correct if set to check a time server.

To verify this, I suggest you do something similar to what I did, to verify the database server’s clock:

Open a browser window for Time.is, selecting UTC.
Set current default time zone to UTC: SET TIME ZONE 'GMT' ;.
Query database for current default time zone and the current moment. Use clock_timestamp rather than a transaction-start function like now to avoid confusion with a long-running txn.

➥If Time.is does not agree with your result from clock_timestamp, then we know your server time is set incorrectly.

Take a screenshot, post with your Question.

Tips:

Learn to think, work, log, and debug in UTC. Forget about your own parochial time zone while on the job. Think of UTC as the One True Time, and all other zones are but mere variations.
Generally best to set your servers to UTC (offset of zero hours-minutes-seconds).
I recommend configuring your servers to check with a time server for auto-correction of their clocks, if you have access to reliable time servers.

Time zone in postgeSQL

3 Answers3

Use clock_timestamp() for current moment

Setting default time zone

Example

Use `clock_timestamp()` for current moment