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