Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:17, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
>
>> 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.

For reference, the domain based solution has been working for a while.

It has fleshed out bugs in apps that failed to set the zone either on the session level at the field level.

In those circumstances, the server raised an exception to say that a non-UTC timestamp was submitted.

It could have been the timestamp had a specific zone other than UTC. In this case, the behavior would be that Postgres stores it as UTC and therefore all is good.

But it also would be that a timestamp WITHOUT a zone was submitted, in which case, the server default would take precedence.

>
>>> 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 :-)

That is what the OP meant. That the zone value was not explicit in all cases. For example, if you fallback to the server default.

And yes, the OP could have set UTC both on the DB servers (assuming OP controls them) and within each server app (assuming OP controls them).