Postgres timestamp with time zone saving with hour shift

Question

I am trying to process input data putting timestamp when save it

   tsSrc timestamp with time zone;
   ...
   tsSrc := strTelegram.rte_data[ iPos ];-- this input data datetime 
   -- string e.g.'2015/12/13 21:35:26.000'
   ...
   insert into telegram(
      tld_id,
      ddt_num, tld_src_timestamp, 
      tld_dst_timestamp, tld_year, tld_month, 
      tld_day, tld_hour, tld_min, 
      tld_sec, tld_data 
   ) values(
      uuId,
      strTelegram.rte_type,
      tsSrc,
      strTelegram.rte_dst_timestamp,
      extract(year from tsSrc), extract(month from tsSrc), 
      extract(day from tsSrc), extract(hour from tsSrc), 
      extract(minute from tsSrc), extract(second from tsSrc), 
      strTelegram.rte_data 
  );

But I have got unexpected result, tsSrc saved as 2015-12-13 20:35:26+03 i.e. has hour -1 shift, at the same time extract(hour from tsSrc) returns right value and saved as 21. What I am doing wrong?

Timezone is set as 'MSK-3' in postgresql.conf, select now() returns right datetime, postgresql 9.3.

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

You need to understand handling of timestamp (timestamp without time zone) and timestamptz (timestamp with time zone) and how each interacts with the timezone setting of your current session.

To explain the "difference" you observe we would need to know the exact table definition and the timezone setting of the session saving the row, as well as the timezone setting of the session displaying the row.

For example, if you take the timestamp literal '2015-12-13 21:35:26' (use ISO format to avoid additional complication with the input format!) and save it to a timestamptz column in a session with time zone offset +2 and later select the same row in a session with with time zone offset +3, then you get what you see:

SELECT '2015-12-13 21:35:26'::timestamp  AT TIME ZONE '+2' AT TIME ZONE '+3';

Result:

'2015-12-13 20:35:26'

In other words: the timestamptz value '2015-12-13 20:35:26+03' is exactly the same (same point in time) as '2015-12-13 21:35:26+02', only the display has been adapted to your time zone setting. When you treat the timestamptz value according to the clock on the wall in your corner of the world (like you do with extract(hour from tsSrc)), you get different results depending where you are currently (the timezone setting of your session).

Detailed explanation:

Ignoring timezones altogether in Rails and PostgreSQL

edited May 23 '17 at 12:32

Community

1
1

answered Jul 01 '16 at 22:27

Erwin Brandstetter

605,456
145
1,078
1,228

It's seems not my case, because a) there are no special settings for timezone different from in postgresql.conf, b) data integration server which retrieves this timestamp and db server which stores timestamp are in one "corner of the world" – mikhail Jul 02 '16 at 14:53
@mikhail: It does not matter where the servers are located. The only thing that matters is the `timezone` setting of the session that saves the `timestamp` into a `timestamptz` column - which has to assume the current time zone barring other instructions. `SHOW timezone;` The timezone setting of your session reading the column only matters for display and does not *change* the value itself. Follow the link for details. – Erwin Brandstetter Jul 02 '16 at 23:11
I probably didn't put quite clear in a point a). Therefore more details, `show timezone` running from pgAdmin on client machine returns `MSK-3`, `show timezone` running from psql on db server returns `MSK-3` and as I mentioned earlier `timezone` in postgresql.conf set to `MSK-3` – mikhail Jul 03 '16 at 09:14
Resolved. It must explicitly set time zone in data integration server configuration file `setenv.sh` something like this `export CATALINA_OPTS="$CATALINA_OPTS -Duser.timezone=MSK-3"` – mikhail Jul 11 '16 at 09:03

Postgres timestamp with time zone saving with hour shift

1 Answers1