0

What I mean is the following: if I create a timestamp without timezone (timestamp) column and store there some value, internally microseconds are stored, but when issuing select this is converted to a string with date and time. But in which timezone the converted time is? I tried to change timezone in my computer (PostgreSQL is running locally, so I reopened psql), tried changing it in postgresql.conf (restarting the service) and setting timezone per session. However as far as I saw, the output doesn't change. I'm sure I'm missing something. Thanks!

Sample code:

create table tzo (wotz timestamp);
insert into tzo values (now());
table tzo;

Output:

            wotz            
----------------------------
 2020-08-11 17:51:37.244901
(1 row)
4
  • The session time zone: show timezone; – a_horse_with_no_name Aug 11 '20 at 15:09
  • Could you run date command in OS and print timestamp in psql session at nearly the same time to investigate further and then match both the outputs with the world clock. – Atty Aug 11 '20 at 15:11
  • @a_horse_with_no_name It is not in the session timezone, unfortunately.set timezone='Europe/Berlin'; table tzo; wotz ---------------------------- 2020-08-11 17:51:37.244901 (1 row) (I'm obviously in different timezone with different offset) – igorepst Aug 11 '20 at 15:18
  • @a_horse_with_no_name I know that this kind of timestamp doesn't store timezone. However, as far as I understand, conversion to string HAVE to use some timezone (let's say, UTC) to show something meaningful. I'm trying actually to match info from DB that came from client that is in a different timezone with my local time. When, say, in psql I see 6:00 PM, is it 6 PM in client's zone/mine/UTC/whatever? – igorepst Aug 11 '20 at 15:24
2

timestamp without time zone is time zone agnostic, so it is not adjusted to any time zone on display. This data type just doesn't have a time zone.

You can convert it to a certain time zone with AT TIME ZONE:

SHOW timezone;

   TimeZone    
---------------
 Europe/Vienna
(1 row)

SELECT TIMESTAMP WITHOUT TIME ZONE '2020-08-01 12:00:00' AT TIME ZONE 'UTC';

        timezone        
------------------------
 2020-08-01 14:00:00+02
(1 row)

Then the timestamp is interpreted as Viennese time, converted to UTC and displayed in my local Viennese time zone, which is offset two hours.

8
  • Let's concentrate on the first part of your answer, please. I do realize timestamp doesn't have timezone info attached, however, as far as my familiarity with programming goes, conversion to string always uses one or another, so I may realize an answer "it is converted as if the input was done in UTC, the output is also always like in UTC, but no offset is added when the output is displayed, no matter in which timezone the server or the psql client run". Then it may make sense ( I hope :) ) – igorepst Aug 11 '20 at 15:43
  • Essentially it means that what is written in timezone X as time 'A:B C:D' will always be represented as 'A:B C:D', no matter in which timezone Y the output is displayed (without any explicit conversions, only as far as the implementation in psql goes!) – igorepst Aug 11 '20 at 15:46
  • 1
    I don't know why you insist on involving time zones into something where there are none. If that is easier to understand for you, imagine that there is only a single time zone, UTC if you want, and everything is done in that time zone - but there will be no time zone in the output. – Laurenz Albe Aug 12 '20 at 5:49
  • That is because visual representation of Date has no meaning without timezone. It is perfectly possible though, that in whatever programming language PostgreSQL or psql are written, the conversion of Date to string may be done with null as a timezone. For Java or JavaScript there are fallbacks in such a case - a local one, or 'GMT', if no local is defined. What was crucial for me to understand is that if timestamp has 09:00PM it will be presented as is no matter in which timezone I do select from – igorepst Aug 12 '20 at 7:16
  • You are wrong. It is perfectly correct to say "At 2000-01-01 00:00:00, most people celebrated the new millennium" without any reference to a time zone. – Laurenz Albe Aug 12 '20 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.