Re: Why data of timestamptz does not store value of timezone passed to it? - Mailing list pgsql-hackers

From rohtodeveloper
Subject Re: Why data of timestamptz does not store value of timezone passed to it?
Date
Msg-id BAY178-W27A1DD52AA183CE723FBFBC4C60@phx.gbl
Whole thread Raw
In response to Re: Why data of timestamptz does not store value of timezone passed to it?  (Craig Ringer <craig@2ndquadrant.com>)
List pgsql-hackers
> On 08/29/2014 04:59 AM, Kevin Grittner wrote:
>> I just took a quick look at the spec to refresh my memory, and it
>> seems to require that the WITH TIME ZONE types store UTC (I suppose
>> for fast comparisons), it requires the time zone in the form of a
>> hour:minute offset to be stored with it, so you can determine the
>> local time from which it was derived. I concede that this is not
>> usually useful, and am glad we have a type that behaves as
>> timestamptz does; but occasionally a type that behaves in
>> conformance with the spec would be useful, and it would certainly
>> be less confusing for people who are used to the standard behavior.
> 
> FWIW, MS SQL's DateTimeOffset data type:
> 
> http://msdn.microsoft.com/en-AU/library/bb630289.aspx
> 
> is much more like what I, when I was getting started, expected TIMESTAMP
> WITH TIME ZONE to be. We don't really have anything equivalent in
> PostgreSQL.
> 

That's also what i expect,a timestamptz = timestampt + offset . Just like the current implementation  of TIME WITH TIME
ZONE.

typedef struct
{TimeADT  time;   /* all time units other than months and years */int32  zone;   /* numeric time zone, in seconds */
} TimeTzADT;

And, it's inconvenient for client(jdbc,npgsql...) to understand a strict 'timezone' (such as 'America/New_York') which
comesfrom PostgreSQL and transform it to theirown data type(Such as DateTimeOffset in .NET). But a *offset* is easy to
parseand process.
 


Beast Regards
rohto

pgsql-hackers by date:

Previous
From: Fabrízio de Royes Mello
Date:
Subject: Re: COPY and heap_sync
Next
From: Stephen Frost
Date:
Subject: Re: RLS Design