CODEWITHSUNDEEP
postgresqltimezonetimezone-offset
Eugen Konkov
Eugen Konkov

Reputation: 25113

Why datetime without time zone foormatted as it have timezone?

I am experimenting with time zones.

My postgres table created as:

                       Table "public.xx"
 Column |            Type             | Collation | Nullable | Default 
--------+-----------------------------+-----------+----------+---------
 dtz    | timestamp with time zone    |           |          | 
 dt     | timestamp without time zone |           |          |

The server timezone is 'UTC'. I know this from show timezone;

Then I insert data:

insert into xx values( TIMESTAMP WITH TIME ZONE '2018-08-01 13:00:00+3', TIMESTAMP WITH TIME ZONE '2018-08-01 13:00:00+3' );
INSERT 0 1
tucha=> select * from xx;
          dtz           |         dt          
------------------------+---------------------
 2018-08-01 10:00:00+00 | 2018-08-01 10:00:00
(1 row)

Results are easy to understand: the dates are stored in UTC thus 3 is subtracted.
Also notice that dtz have +00

Now when I use at time zone

tucha=> select dtz at time zone 'UTC', dt at time zone 'UTC' from xx;
      timezone       |        timezone        
---------------------+------------------------
 2018-08-01 10:00:00 | 2018-08-01 10:00:00+00
(1 row)

The +00 is added for field which has no timezone and viceversa: no +00 for dtz field.

Why this happened?

Upvotes: 0

Views: 485

Answers (2)

Eugen Konkov
Eugen Konkov

Reputation: 25113

I have found good article how to work with timezones (RU):

So – timestamp at time zone gives timestamptz which represents the moment in time when local time at given zone was as given.

and timestamptz at time zone gives timestamp, which shows what the time was in given timezone at given point in time.

Upvotes: 0

Vao Tsun
Vao Tsun

Reputation: 51406

a short answer is - because it is desined this way:

https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

timestamp without time zone AT TIME ZONE zone returns timestamp with time zone

timestamp with time zone AT TIME ZONE zone returns timestamp without time zone

Longer: as you said your client works in UTC timezone and server always stores timestamptz in UTC, thus select times match (you use UTC everywhere).

first select timestamptz is shown with +00 because this is timezone aware field - and it shows you the time zone. dt knows nothing of time zones, thus shows none...

Now when you use AT TIME ZONE you ask to show timestamps at SOME SPECIFIC time zone, thus time zone aware data type shows you the time for specific zone (adding hours and so), but after "moving" time it can't be reused as time zone aware (because it's not server UTC time anymore), thus it hides TZ. Opposite the time that was not aware of time zone, when displayed at some specific time zone, gains that "awareness" and deducting hours it shows +00 so you would know you operated on TZ not aware time stamp. I think that is the logic here.

Upvotes: 2

Related Questions