Re: Why data of timestamptz does not store value of timezone passed to it?

From: arhipov <arhipov(at)dc(dot)baikal(dot)ru>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Why data of timestamptz does not store value of timezone passed to it?
Date: 2014-08-29 09:04:12
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 08/29/2014 05:28 AM, Tom Lane wrote:
> "ktm(at)rice(dot)edu" <ktm(at)rice(dot)edu> writes:
>> On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote:
>>> So the standard requires storing of original timezone in the data type?
>>> I was not aware of that.
>> I do not have a copy of the SQL 92 spec, but several references to the
>> spec mention that it defined the "time zone" as a format "SHH:MM" where
>> S represents the sign (+ or -), which seems to be what PostgreSQL uses.
> Yeah, the spec envisions timezone as being a separate numeric field
> (ie, a numeric GMT offset) within a timestamp with time zone. One of
> the ways in which the spec's design is rather broken is that there's
> no concept of real-world time zones with varying DST rules.
> Anyway, I agree with the upthread comments that it'd have been better
> if we'd used some other name for this datatype, and also that it's
> at least ten years too late to revisit the choice :-(.
> regards, tom lane
What about an alias for timestamptz? The current name is really confusing.
As for timestamp + time-zone (not just the offset) data type, it would
be very useful. For example, in Java they have 5 time types: LocalDate
for representing dates (date in Postgres), LocalTime for representing
times (time in Postgres), LocalDateTime to represent a date with a time
(timestamp in Postgres), Instant to represent a point on the time-line
(timestamptz in Postgres) and ZonedDateTime that models a point on the
time-line with a time-zone. Having a type for a time-zone itself would
be useful as well.

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Davis 2014-08-29 09:15:53 Re: 9.5: Better memory accounting, towards memory-bounded HashAgg
Previous Message Thomas Munro 2014-08-29 08:53:32 Re: Multithreaded SIGPIPE race in libpq on Solaris