Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/13/2018 03:45 PM, Peter J. Holzer wrote: On 2018-05-13 12:46:42 -0700, Adrian Klaver wrote: Not trying to trick anyone and no magic. The difference in the represented values between ts_tz and ts_naive is the heart of my argument. Timestamptz values are stored in manner that allows you to h

Re: Domain based on TIMEZONE WITH TIME ZONE

On 2018-05-13 12:46:42 -0700, Adrian Klaver wrote: > Not trying to trick anyone and no magic. The difference in the represented > values between ts_tz and ts_naive is the heart of my argument. Timestamptz > values are stored in manner that allows you to have the output with a time > zone offset. Ti

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/13/2018 01:55 AM, Francisco Olarte wrote: On Sat, May 12, 2018 at 8:19 PM, Adrian Klaver wrote: I would agree that timestamp and timestamptz are both stored as numbers. Well, after reading source that is a fact. I was trying to say they are like real numbers, a point in a line. I wou

Re: Domain based on TIMEZONE WITH TIME ZONE

On Sat, May 12, 2018 at 8:19 PM, Adrian Klaver wrote: > I would agree that timestamp and timestamptz are both stored as numbers. Well, after reading source that is a fact. I was trying to say they are like real numbers, a point in a line. > I would not agree they do not have timezones: > show t

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/12/2018 10:22 AM, Francisco Olarte wrote: Adrian: On Sat, May 12, 2018 at 6:42 PM, Adrian Klaver wrote: On 05/12/2018 04:04 AM, Francisco Olarte wrote: ... Can you post an example ( correct, error inputs, preferrably done with psql ) ? At this point I do not know if it is working or

Re: Domain based on TIMEZONE WITH TIME ZONE

Adrian: On Sat, May 12, 2018 at 6:42 PM, Adrian Klaver wrote: > On 05/12/2018 04:04 AM, Francisco Olarte wrote: ... >> Can you post an example ( correct, error inputs, preferrably done with >> psql ) ? >> >> At this point I do not know if it is working or if it seems to working >> for you. I see

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/12/2018 04:04 AM, Francisco Olarte wrote: On Thu, May 10, 2018 at 10:37 PM, Ben Hood wrote: On 10 May 2018, at 16:33, Francisco Olarte wrote: For what you want to do I think you'll have to parse the text value, maybe by definig a view with a text columns and using some rule/trigger magi

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 10:37 PM, Ben Hood wrote: > On 10 May 2018, at 16:33, Francisco Olarte wrote: > > For what you want to do I think you'll have to parse the text value, > maybe by definig a view with a text columns and using some > rule/trigger magic for insert / updates. > > > Sorry for be

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 23:43, Adrian Klaver wrote: > > Trying to tame time and time zones is maybe quixotic, but not weird. Quixotic is a very good description, I’d happily admit that using the UTC domain in this way is not as pragmatic as I thought it would when I introduced it. > While I was

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 22:17, Peter J. Holzer wrote: > > I don't understand how this can work. As Francisco demonstrated, > EXTRACT(TIMEZONE FROM ts) doesn't extract the time zone from the value > ts, it reports the offset of the client's time zone. Yes, you and Francisco are right. If you do:

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 01:37 PM, Ben Hood wrote: On 10 May 2018, at 16:33, Francisco Olarte > wrote: For what you want to do I think you'll have to parse the text value, maybe by definig a view with a text columns and using some rule/trigger magic for insert / updates.

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 02:17 PM, Peter J. Holzer wrote: On 2018-05-10 21:37:26 +0100, Ben Hood wrote: On 10 May 2018, at 16:33, Francisco Olarte wrote: For what you want to do I think you'll have to parse the text value, maybe by definig a view with a text columns and using some rul

Re: Domain based on TIMEZONE WITH TIME ZONE

On 2018-05-10 21:37:26 +0100, Ben Hood wrote: > On 10 May 2018, at 16:33, Francisco Olarte wrote: > > For what you want to do I think you'll have to parse the text value, > maybe by definig a view with a text columns and using some > rule/trigger magic for insert / updates. > >

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 16:33, Francisco Olarte wrote: > > For what you want to do I think you'll have to parse the text value, > maybe by definig a view with a text columns and using some > rule/trigger magic for insert / updates. Sorry for being unclear - the solution I have in production appea

Re: Domain based on TIMEZONE WITH TIME ZONE

I think for the use case you describe, and given the fact that it does work in production, your solution simply shows The Power Of PostgreSQL. Java tries to be cross-platform, JDBC tries to be cross-database -- these goals don't seem to lend themselves to advocating or understanding what one parti

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 17:38, Adrian Klaver wrote: > > 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 j

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 17:35, David G. Johnston > wrote: > > ​'2018-05-10T15:23:00-07:00​'::timestamptz is unambiguous That is true. Mandating UTC is not the only way to eliminate ambiguity. Apologies for appearing to suggest that this is case. > > Allowing client applications to represent

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 18:29, Adrian Klaver wrote: > > Per my previous post a timestamp with timezone is going to be stored as UTC, > so there is no ambiguity there. On reflection I realized your concern maybe > with determining the original input timezone. That information is not stored > by

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 09:13 AM, Ben Hood wrote: On 10 May 2018, at 15:12, Vick Khera > wrote: On Thu, May 10, 2018 at 7:31 AM, Ben Hood > wrote: Or are we saying that domains are one way of achieving the timestamp hygiene, but equally, you can

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 09:09 AM, Ben Hood wrote: On 10 May 2018, at 14:41, 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-

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:33, Tom Lane wrote: > > Ben Hood writes: >> So the question is not how does the timestamp get stored, rather, is it an >> anti-pattern to use Postgres as a linter for apps that forget to use UTC >> exclusively? > > Well, using a domain to enforce additional constraint

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 9:13 AM, Ben Hood wrote: > On 10 May 2018, at 15:12, Vick Khera wrote: > > On Thu, May 10, 2018 at 7:31 AM, Ben Hood wrote: > >> Or are we saying that domains are one way of achieving the timestamp >> hygiene, but equally, you can get the same result as described above?

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:17, Karsten Hilbert 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 Ti

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 15:12, Vick Khera wrote: > > On Thu, May 10, 2018 at 7:31 AM, Ben Hood > wrote: > Or are we saying that domains are one way of achieving the timestamp hygiene, > but equally, you can get the same result as described above? > > The *only* way to hav

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 14:41, 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 times

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 12:19 PM, Ben Hood wrote: >> On 10 May 2018, at 09:59, Francisco Olarte wrote: >> Maybe I'm confussing you more, its not too easy to explain. >> The point is TIMEZONE is not stored in either of them. > > Many thanks for clarification, very much appreciated. > > Your p

Re: Domain based on TIMEZONE WITH TIME ZONE

Ben Hood writes: > So the question is not how does the timestamp get stored, rather, is it an > anti-pattern to use Postgres as a linter for apps that forget to use UTC > exclusively? Well, using a domain to enforce additional constraints on a field's value is certainly not an anti-pattern in i

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 >

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 7:31 AM, Ben Hood wrote: > Or are we saying that domains are one way of achieving the timestamp > hygiene, but equally, you can get the same result as described above? > The *only* way to have timestamp hygiene is to require them to have time zones at all times, even if t

Re: Domain based on TIMEZONE WITH TIME ZONE

On 05/10/2018 04:31 AM, Ben Hood wrote: On 10 May 2018, at 11:36, Karsten Hilbert 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 w

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 11:36, Karsten Hilbert 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 domain

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 11:19:36AM +0100, Ben Hood wrote: > The question should not be “how does Postgres store the timestamp internally”. > > Rather it should read “is enforcing the submission of UTC denominated > timestamps in the server by using a domain a sensible way to enforce a policy >

Re: Domain based on TIMEZONE WITH TIME ZONE

> On 10 May 2018, at 09:59, Francisco Olarte wrote: > > On Thu, May 10, 2018 at 10:03 AM, Ben Hood wrote: > ... >> Am I missing the point of how Postgres stores TIMEZONE WITH TIME ZONE >> internally? > > After reading in the follow up TIMEZONE is a typo for TIMESTAMP, yes, you are. > > Aproxi

Re: Domain based on TIMEZONE WITH TIME ZONE

On Thu, May 10, 2018 at 10:03 AM, Ben Hood wrote: ... > Am I missing the point of how Postgres stores TIMEZONE WITH TIME ZONE > internally? After reading in the follow up TIMEZONE is a typo for TIMESTAMP, yes, you are. Aproximately, postgres stores ( virtually ) a point in the time line, for bot

Re: Domain based on TIMEZONE WITH TIME ZONE

Sorry about the bug in the subject: the data type is TIMESTAMP WITH TIME ZONE, not TIMEZONE WITH TIME ZONE > On 10 May 2018, at 09:03, Ben Hood wrote: > > Hi, > > I'm using a domain to specialize the built in TIMEZONE WITH TIME ZONE type. I > want to sanity check this approach before continu

Domain based on TIMEZONE WITH TIME ZONE

Hi, I'm using a domain to specialize the built in TIMEZONE WITH TIME ZONE type. I want to sanity check this approach before continuing to use this. I want to prevent timestamps with non-UTC offsets from getting inserted into the database. Having a UTC-only database at the schema level means no