Re: Domain based on TIMEZONE WITH TIME ZONE
От | Adrian Klaver |
---|---|
Тема | Re: Domain based on TIMEZONE WITH TIME ZONE |
Дата | |
Msg-id | 93d7120e-ca04-467f-4b55-a5ca0e8d255c@aklaver.com обсуждение исходный текст |
Ответ на | Re: Domain based on TIMEZONE WITH TIME ZONE (Ben Hood <ben@relops.com>) |
Ответы |
Re: Domain based on TIMEZONE WITH TIME ZONE
(Ben Hood <ben@relops.com>)
|
Список | pgsql-general |
On 05/10/2018 09:09 AM, Ben Hood wrote: > >> On 10 May 2018, at 14:41, Adrian Klaver <adrian.klaver@aklaver.com> 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, traditionallyknown as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted toUTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumedto be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset forthe timezone zone.” > > > Many thanks for this clarification. So therefore you can’t rely on the TIMESTAMP WITH TIME ZONE forcing the app to explicitlyspecify the offset. This is is because if the app is not specific, then the server will default back to its configuredtimezone. > > So to get deterministic timestamps, you could either: > > a) make sure the server is always configured to run in UTC; > b) issue SET TIME ZONE ‘UTC’; at the beginning of any application session Well if you are using a timestamp with timezone field the value is always going to be stored as UTC. The TimeZone setting just determines the rotation from the input value to the stored value and the reverse. My previous point was just that Postgres will not enforce an offset on input data. > > > >> >>>> After all, it is always converted to UTC >>>> servside anyway ? >>> And because of the internal UTC representation, there is no room for ambiguous timezones. >> >> Define ambiguous timezone? > > What I meant to say that is there should be no possibility for an effective timezone to arise implicitly. > > For example, if you > > (1) didn’t control the db server config > > and > > (2) and you forgot to enforce UTC at a client driver level > > and > > (3) didn’t set the offset in the app session > > > Then the only way to know what the effective zone offset will be is to find out what the server default is. > > Is this plausible? If you mean find the server default then yes: test_(aklaver)> select current_setting('TimeZone'); current_setting ----------------- US/Pacific https://www.postgresql.org/docs/10/static/functions-admin.html#FUNCTIONS-ADMIN-SET > > > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: