From: | rohtodeveloper <rohtodeveloper(at)outlook(dot)com> |
---|---|
To: | Craig Ringer <craig(at)2ndquadrant(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Why data of timestamptz does not store value of timezone passed to it? |
Date: | 2014-09-01 04:05:37 |
Message-ID: | BAY178-W27A1DD52AA183CE723FBFBC4C60@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 comes from PostgreSQL and transform it to theirown data type(Such as DateTimeOffset in .NET). But a *offset* is easy to parse and process.
Beast Regards
rohto
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2014-09-01 04:15:08 | Re: RLS Design |
Previous Message | Fabrízio de Royes Mello | 2014-09-01 03:49:39 | Re: COPY and heap_sync |