0

I have date like "9999-12-30 18:30:00+00" in database.

When i am accessing from local machine it is coming as 9999-12-31 00:00:00.0(In India) When i am accessing from live it is coming as 9999-12-30 00:00:00.0(In US)

data type is timestamp with time zone

What might be the wrong.Thanks in advance...

The following code i am using to save the date

SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");

Date date = formatter.parse("12/31/9999");

SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss z");

String formattedDate = formatter1.format(date);
//formatter1.setTimeZone(TimeZone.getDefault());

Date finalDate = formatter1.parse(formattedDate);

System.out.println(finalDate);

It is saving as "9999-12-30 18:30:00+00"

PSR
  • 39,804
  • 41
  • 111
  • 151

2 Answers2

2

The whole point of timestamp with time zone (as opposed to its counterpart timestamp without time zone) is that the value is dynamically rotated depending on the timezone of the client running the query.

Since you insist that you're connected to the same database, as if it would imply that both clients should retrieve the same value, maybe you don't understand that it's normal that they retrieve different values.

To illustrate, here's a reproducible example with your values:

create table ttz(t timestamptz);
insert into ttz values('9999-12-30 18:30:00+00');
  • When my timezone is the default 'localtime' which is in fact 'Europe/Paris', which at this future date if our world still exists, is supposed to be one hour ahead of UTC, here's what I get:
=> select * from ttz;
           t            
------------------------
 9999-12-30 19:30:00+01
  • Now let's switch to a timezone in India:
=> set timezone to 'Asia/Calcutta';
=> select * from ttz;
             t             
---------------------------
 9999-12-31 00:00:00+05:30

So if I pretend to be in India, it outputs the same value than your session in India. This is the expected result.

  • About the result in the US

US has several time zones so you should first check which one is used by your server. The strange thing is that in order to get your result of 9999-12-30 00:00:00 from the UTC 9999-12-30 18:30, you'd need to have an offset of +18:30 that doesn't exist in practice although it would coincide with India if going west instead of east from UTC.

=> set timezone to 'UTC+18:30';
=> select * from ttz ;
             t             
---------------------------
 9999-12-30 00:00:00-18:30
(1 row)

Thus it can be suspected that your client session in US just has a badly configured timezone.

See also PostgreSQL 9.1 timezones for the problem of conflicting sign conventions when using offsets from UTC.

Community
  • 1
  • 1
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • Thank u so much.You are right.Actually i got the problem when i inserted the record from and retrieve from the live server(Because database for both are same).I understand the problem – PSR Jul 17 '13 at 04:13
0

First, make sure that both your local and live databases are using proper time on server side - both should be using NTP to synchronize server time.

If you use proper datetime format of Z or X, it will keep timezone information present in your printed timestamp (but it may look textually different for local and live db).

If you don't want to keep this info or not willing to change your format, you can use AT TIME ZONE syntax when retrieving your datetime such that you always get it at some predetermined time zone, say UTC:

SELECT dt AT TIME ZONE 'UTC' FROM mytable

For time zone here you can use usual syntax like '+00' (same as 'UTC'), '+08', etc.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • i am accessing the same database from local which is located at US – PSR Jul 16 '13 at 10:42
  • I would be very surprised if querying `AT TIME ZONE ...` by different clients but from the same database gave you different result. – mvp Jul 16 '13 at 10:49
  • I am also unable to understand this problem – PSR Jul 16 '13 at 10:58