Re: Domain based on TIMEZONE WITH TIME ZONE - Mailing list pgsql-general

From Karsten Hilbert
Subject Re: Domain based on TIMEZONE WITH TIME ZONE
Date
Msg-id 20180510141721.GE3622@hermes.hilbert.loc
Whole thread Raw
In response to Re: Domain based on TIMEZONE WITH TIME ZONE  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Domain based on TIMEZONE WITH TIME ZONE  (Ben Hood <ben@relops.com>)
List pgsql-general
On Thu, May 10, 2018 at 06:41:04AM -0700, Adrian Klaver wrote:

>> OK, so by using TIMESTAMP WITH TIME ZONE, you force all apps to submit timezone qualified timestamps in what
languagethey are written in.
 
> 
> Not really:
> 
> https://www.postgresql.org/docs/10/static/datatype-datetime.html
> 
> "For timestamp with time zone, the internally stored value is always in UTC
> (Universal Coordinated Time, traditionally known as Greenwich Mean Time,
> GMT). An input value that has an explicit time zone specified is converted
> to UTC using the appropriate offset for that time zone. If no time zone is
> stated in the input string, then it is assumed to be in the time zone
> indicated by the system's TimeZone parameter, and is converted to UTC using
> the offset for the timezone zone."

True enough, I didn't remember that behaviour.

And since a BEFORE UPDATE/INSERT trigger will see the data to
be inserted PG will have already done that while parsing from
on-the-wire data into in-memory ts-w-tz presentation so we
can't simply use a trigger to enforce explicit specification
of a timezone.

Therefore, a domain could work but will require client
language support for easy integration.

> > And because of the internal UTC representation, there is no room for ambiguous timezones.
> 
> Define ambiguous timezone?

OP likely means underspecified for his use case (= not
assuming "unspecified" to mean "TimeZone value"). But, then,
OP could always force TimeZone to UTC on his servers :-)

Karsten
-- 


pgsql-general by date:

Previous
From: Vick Khera
Date:
Subject: Re: Domain based on TIMEZONE WITH TIME ZONE
Next
From: Tom Lane
Date:
Subject: Re: Domain based on TIMEZONE WITH TIME ZONE