Re: Domain based on TIMEZONE WITH TIME ZONE

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 language they 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
--