1

(This is a follow-up to: Time zone storage in PostgreSQL timestamps)

I am using PostgreSQL 9.x, the db server has default timezone as +9 for example, it has a table that contains a column of timezone type.

There is a Java program on the same server as the db, it query data from the the database. Both the server that the db resides, and the client (a browser) of the Java server program is in timezone -8.

My questions are:

  • Will the client (brower) show the original datetime or adjusted datetime with 9 - (-8) = 17 hours earlier?
  • If I want to get the same data in browser client as the db original value, should I change the timezone of Java server machine or the Client machine, or both?
    (I know it's better to make the db & its server have the same timezone, but let's assume they are not setting the same for now for some reason.)
Community
  • 1
  • 1
Eric
  • 22,183
  • 20
  • 145
  • 196

1 Answers1

2

You always get the same data in browser client as the db original value. There are just many different ways to display the same point in time (at different time zones). Two examples (text representations of a timestamptz value):

'2012-03-05 20:00:00+03'
'2012-03-05 18:00:00+01'

Same value.

As far as Postgres is concerned, the time zone setting of the server is completely irrelevant for this. The only relevant thing is the setting of the session. And the only thing that changes is the display of the value. It's always the same point in time.

So just set the time zone in your session to get the according text representation:

Another option would be the AT TIME ZONE construct. To get a timestamp as local timestamp without time zone:

SELECT my_timestamptz_column AT TIME ZONE '-8';

When dealing with a table that contains a column of timezone type, this might be the better approach, since the time zone can change for every row.

Again, all of this is in the reference answer:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So the value is always the same, just different browser client in different timezone will adjust the display value according to its local timezone, is that correct? – Eric Mar 05 '15 at 12:17
  • @EricWang: Mostly. "Its local timezone" is a bit fuzzy. The relevant thing for Postgres is the setting `timezone` of the current session. I added another link. If your client operates with a different time zone than what's declared in the session, that's your responsibility. – Erwin Brandstetter Mar 05 '15 at 12:19