Thread: Date & Time with time zone
I try to understand how the D&T information is stored/presented in PG. In the application, the data is reported as Sun Apr 09 12:40:52 PDT 2006 - Pacific Standard Time But, in the DB, it is stated as 2006-04-09 14:40:53.093-07 It doesn't seen right to me. Both are on the same box and the date column is with time zone. Any thought? Thanks. -- _______________________________________________ Search for businesses by name, location, or phone number. -Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10
On Sun, Apr 09, 2006 at 12:07:50PM -0800, Wei Wei wrote: > I try to understand how the D&T information is stored/presented in PG. In the application, the data is reported as > > Sun Apr 09 12:40:52 PDT 2006 - Pacific Standard Time > > But, in the DB, it is stated as > > 2006-04-09 14:40:53.093-07 > > It doesn't seen right to me. Both are on the same box and the date column is with time zone. Read the docs for the exact details but the gist of it is that PostgreSQL always displays dates to you in your own timezone (or whatever is configured with "set timezone" anyway). timestamp with timezone represents an instant in time, which may appear as different actual values depending on which timezone you're talking about. timestamp without timezone is a timestamp that appears the same to everyone, all the time. The uses for this are not entirely clear, given you can't represent the overlaps or gaps created by daylight savings shifts. However, sometimes this is what you want. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Thanks for your reply, Martijn. And see below. > On Sun, Apr 09, 2006 at 12:07:50PM -0800, Wei Wei wrote: > > I try to understand how the D&T information is stored/presented > > in PG. In the application, the data is reported as > > > > Sun Apr 09 12:40:52 PDT 2006 - Pacific Standard Time > > > > But, in the DB, it is stated as > > > > 2006-04-09 14:40:53.093-07 > > > > It doesn't seen right to me. Both are on the same box and the > > date column is with time zone. > > Read the docs for the exact details but the gist of it is that > PostgreSQL always displays dates to you in your own timezone (or > whatever is configured with "set timezone" anyway). > I already read the document. But, I haven't found any related information. > timestamp with timezone represents an instant in time, which may appear > as different actual values depending on which timezone you're talking > about. > I am not sure that I can follow you above. Someone told me that the timezone is always set as what it is with DB regardlessthe application nor the user choice. > timestamp without timezone is a timestamp that appears the same to > everyone, all the time. The uses for this are not entirely clear, given > you can't represent the overlaps or gaps created by daylight savings > shifts. However, sometimes this is what you want. > It doesn't seem to be the way as you describe above. Before the Summer Saving Time, the all timestamp with time zone datashown in the DB with "-8" ending. After the SST, they are shown with "-7" ending. -- _______________________________________________ Search for businesses by name, location, or phone number. -Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10
"Wei Wei" <wei725@lycos.com> writes:
> In the application, the data is reported as
>
> Sun Apr 09 12:40:52 PDT 2006 - Pacific Standard Time
>
> But, in the DB, it is stated as
>
> 2006-04-09 14:40:53.093-07
>
> It doesn't seen right to me. Both are on the same box and the
> date column is with time zone.
It looked odd to me too, but you haven't told us anything useful.
What's the platform, what's the PG version, how exactly did you get the
value from PG (is it "select now()", or what?), what exactly do you have
PG's TIMEZONE variable set to? And what application are you comparing
the results to? Calling "PDT" "Pacific Standard Time" doesn't exactly
leave me with warm fuzzies about that application's level of timezone-fu,
whatever it is.
regards, tom lane
Thanks for your response, Tom. And please see the below. > > In the application, the data is reported as > > > > Sun Apr 09 12:40:52 PDT 2006 - Pacific Standard Time > > > > But, in the DB, it is stated as > > > > 2006-04-09 14:40:53.093-07 > > > > It doesn't seen right to me. Both are on the same box and the > > date column is with time zone. > > It looked odd to me too, but you haven't told us anything useful. > What's the platform, what's the PG version, how exactly did you get the > value from PG (is it "select now()", or what?), what exactly do you have > PG's TIMEZONE variable set to? And what application are you comparing > the results to? Calling "PDT" "Pacific Standard Time" doesn't exactly > leave me with warm fuzzies about that application's level of timezone-fu, > whatever it is. > My development box is Window XP and the depolyment box is Linux. I observe the issue on the both platforms although I haven't done a thorough test on the deployment box. And both PG versions are 8.0.x. A returned value of "select new()" is correct, but the TZ is -7 where the TZ of OS is set to Pacific Day Time Saving Time. The application is written with Java. And I use the Java API Calendar class to match with the PG data type timestamp with time zone through Hibernate. The date log message is from the getTime and getTimezone methods of the class. Both are correct at this point. I do some search on various forums of the java.sun.com. It might relate with the jdbc driver. Shall I move this issue to the jdbc mailing list? -- _______________________________________________ Search for businesses by name, location, or phone number. -Lycos Yellow Pages http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.asp?SRC=lycos10
"Wei Wei" <wei725@lycos.com> writes:
> A returned value of "select new()" is correct, but the TZ is -7 where
> the TZ of OS is set to Pacific Day Time Saving Time.
That sounds like PG is working like it's supposed to.
> The application is
> written with Java. And I use the Java API Calendar class to match
> with the PG data type timestamp with time zone through Hibernate. The
> date log message is from the getTime and getTimezone methods of the
> class. Both are correct at this point.
> I do some search on various forums of the java.sun.com. It might
> relate with the jdbc driver. Shall I move this issue to the jdbc mailing
> list?
Yeah, it sounds like a JDBC issue. Search their archives before
posting; I seem to recall having seen similar discussions there before.
regards, tom lane