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

From Ben Hood
Subject Re: Domain based on TIMEZONE WITH TIME ZONE
Date
Msg-id BCC1C385-7AAC-42BD-B790-08D7624C9414@relops.com
Whole thread Raw
In response to Re: Domain based on TIMEZONE WITH TIME ZONE  (Karsten Hilbert <Karsten.Hilbert@gmx.net>)
Responses Re: Domain based on TIMEZONE WITH TIME ZONE
Re: Domain based on TIMEZONE WITH TIME ZONE
List pgsql-general
> On 10 May 2018, at 11:36, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> On Thu, May 10, 2018 at 11:19:36AM +0100, Ben Hood wrote:
>
> I dare say it is one of PG's strengths' to be usable as a
> "linter”.


Interesting that you share this view, because after thinking about why I was doing this, using UTC domains is for PG to
bea linter. 


> However, maybe rephrase to:
>
>     Is it an anti-pattern to use Postgres as a linter for
>     apps that forget to use ... timezones *appropriately* ... ?
>
> As long as you can force apps to submit proper timestamp-with-
> timezone data is there _really_ a need to care whether apps
> do submit in UTC ?

OK, so by using TIMESTAMP WITH TIME ZONE, you force all apps to submit timezone qualified timestamps in what language
theyare written in. 

> After all, it is always converted to UTC
> servside anyway ?

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

>
> In case you want to enforce only ever _handing out_ UTC data
> you could wrap the table in a view with forces the output
> timezone to UTC and only offers timestamp-withOUT-timezone to
> the outside. Then force read access via the view.

So on balance there is no need to use a domain for this?

Or are we saying that domains are one way of achieving the timestamp hygiene, but equally, you can get the same result
asdescribed above? 

Or is there a specific downside to using a domain for this purpose?

…..maybe I’ve just got a subjective prejudice for using domains to refine and tighten built in data types….




pgsql-general by date:

Previous
From: "a"
Date:
Subject: Is there any C functions that convert the entry to string?
Next
From: Adrian Klaver
Date:
Subject: Re: ON CONFLICT DO UPDATE