Re: Timestamp with vs without time zone.

On Tue, Sep 21, 2021 at 05:49:21PM -0400, Tom Lane wrote: > I think there is plenty of application for timestamps that actually > include (civil) time zones. Calendaring, for example. If I make an > appointment to see a friend at 2PM some months from now, it's > understood that that's in the loca

Re: Timestamp with vs without time zone.

On 9/22/21 9:56 AM, Michael Lewis wrote: On Wed, Sep 22, 2021 at 12:44 AM cen > wrote: On 21. 09. 21 23:34, Michael Lewis wrote: Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as t

Re: Timestamp with vs without time zone.

On Wed, Sep 22, 2021 at 12:44 AM cen wrote: > On 21. 09. 21 23:34, Michael Lewis wrote: > > Related to this current discussion and exchange of ideas... is there a > best practice for retrieving data in such a way as the rows are localized > to a timezone for where/group by purposes. That is, if I

Re: Timestamp with vs without time zone.

Am Wed, Sep 22, 2021 at 08:11:50PM +1200 schrieb Tim Uckun: > A korean user will fetch you single page app as static HTML from S3 > with cloudfront. It will hit your japanese API server, which will > fetch the data from your japanese read only replica with the master > being in Australia. > > The

Re: Timestamp with vs without time zone.

On 22/09/21 20:11, Tim Uckun wrote: I'll add another layer of complication. You have a database server hosted in Australia, and that's also where your web server and api server is. You have customers all over the world though so you set up additional API servers in Europe, USA, Japan etc. A kor

Re: Timestamp with vs without time zone.

Tim Uckun writes: > I'll add another layer of complication. > > You have a database server hosted in Australia, and that's also where > your web server and api server is. You have customers all over the > world though so you set up additional API servers in Europe, USA, > Japan etc. > > A korea

Re: Timestamp with vs without time zone.

I'll add another layer of complication. You have a database server hosted in Australia, and that's also where your web server and api server is. You have customers all over the world though so you set up additional API servers in Europe, USA, Japan etc. A korean user will fetch you single page ap

Re: Timestamp with vs without time zone.

On 21. 09. 21 23:34, Michael Lewis wrote: Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as the rows are localized to a timezone for where/group by purposes. That is, if I have a table which has events, but those events be

Re: Timestamp with vs without time zone.

FWS Neil writes: > On Sep 21, 2021, at 12:34 PM, Dave Cramer wrote: > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > That's all true and I won't argue about the madness that is timezones > > in the world. I am simply thinking

Re: Timestamp with vs without time zone.

My 2-cents. "Time stamp with time zone" is a terrible name for the data type. "Point in time" would be better but we are stuck with historical baggage in that regard. The following are equivalent points in time (AKA timesamptz): 2021-09-21 12:34:56-07 Tue 21 Sep 2021 12:34:56 PM PDT 2021-09-21 12:

Re: Timestamp with vs without time zone.

"David G. Johnston" writes: > On Tuesday, September 21, 2021, Peter J. Holzer wrote: >> These two values are completely indistinguishable. That's good for a >> timestamp (they are the same time after all). But they are not a >> "timestamp with time zone". The time zone is not part of the value bu

Re: Timestamp with vs without time zone.

Related to this current discussion and exchange of ideas... is there a best practice for retrieving data in such a way as the rows are localized to a timezone for where/group by purposes. That is, if I have a table which has events, but those events belong to a tenant or some entity that has a loca

Re: Timestamp with vs without time zone.

From my experience, and some might disagree, I prefer to do db stores purely in UTC and handle timezones in ORM or client side. The only time I actually needed to store timezone information in a dedicated column is when needing to convey that information to the end user, for example "your plan

Re: Timestamp with vs without time zone.

On 2021-09-21 12:58:13 -0700, David G. Johnston wrote: > On Tuesday, September 21, 2021, Peter J. Holzer wrote: > These two values are completely indistinguishable. That's good for a > timestamp (they are the same time after all). But they are not a > "timestamp with time zone". The ti

Re: Timestamp with vs without time zone.

On Tuesday, September 21, 2021, Peter J. Holzer wrote: > > These two values are completely indistinguishable. That's good for a > timestamp (they are the same time after all). But they are not a > "timestamp with time zone". The time zone is not part of the value but > of the environment. > No,

Re: Timestamp with vs without time zone.

On 9/21/21 11:17 AM, FWS Neil wrote: On Sep 21, 2021, at 12:34 PM, Dave Cramer > wrote: On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer > wrote: On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > That's all true and I won't argue

Re: Timestamp with vs without time zone.

On 2021-09-21 12:37:49 -0700, Adrian Klaver wrote: > On 9/21/21 11:45 AM, Peter J. Holzer wrote: > > On 2021-09-21 13:43:46 -0400, Dave Cramer wrote: > > > On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer wrote: > > > On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > > > > I would say thi

Re: Timestamp with vs without time zone.

On 9/21/21 11:45 AM, Peter J. Holzer wrote: On 2021-09-21 13:43:46 -0400, Dave Cramer wrote: On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer wrote: On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: >     On 2021-09-21 20:5

Re: Timestamp with vs without time zone.

[Can y'all please trim your quotes? Having to scroll down 80+ lines to get to the content is cumbersome] On 2021-09-21 13:17:39 -0500, FWS Neil wrote: > A timestamp cannot have a time zone and be a valid timestamp. > > Let me explain. > > A timestamp is a single time that exists in the world. C

Re: Re: Timestamp with vs without time zone.

On 2021-09-21 13:43:46 -0400, Dave Cramer wrote: > > > On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer wrote: > > On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > >     On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > >

Re: Timestamp with vs without time zone.

> On Sep 21, 2021, at 12:34 PM, Dave Cramer wrote: > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer > wrote: > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > That's all true and I won't argue about the madness that is timezones > > in the world. I am simply thinking

Re: Re: Timestamp with vs without time zone.

On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer wrote: > On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > > It's just that the phrase "timestamp with time zone" would seem to >

Re: Re: Timestamp with vs without time zone.

On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > It's just that the phrase "timestamp with time zone" would seem to > > indicate the time zone is stored somewhere in there. > >

Re: Re: Timestamp with vs without time zone.

On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > That's all true and I won't argue about the madness that is timezones > > in the world. I am simply thinking it would be some sort of a struct > > like thing which would store the numerical

Re: Re: Timestamp with vs without time zone.

On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > That's all true and I won't argue about the madness that is timezones > in the world. I am simply thinking it would be some sort of a struct > like thing which would store the numerical value of the time stamp and > also the time zone that time was r

Re: Timestamp with vs without time zone.

Adrian Klaver writes: > On 9/20/21 11:00 PM, Tim Uckun wrote: >> I am hoping to get some clarification on timestamp with time zone. >> >> My understanding is that timestamp with time zone stores data in UTC >> but displays it in your time zone. Does this also work on queries? If >> I query betwe

Re: Timestamp with vs without time zone.

On 9/20/21 11:00 PM, Tim Uckun wrote: I am hoping to get some clarification on timestamp with time zone. My understanding is that timestamp with time zone stores data in UTC but displays it in your time zone. Does this also work on queries? If I query between noon and 2:00 PM on some date in ti

Aw: Re: Re: Timestamp with vs without time zone.

> It's just that the phrase > "timestamp with time zone" would seem to indicate the time zone is > stored somewhere in there. Now, I can fully agree with _that_ :-) Karsten

Re: Re: Timestamp with vs without time zone.

That's all true and I won't argue about the madness that is timezones in the world. I am simply thinking it would be some sort of a struct like thing which would store the numerical value of the time stamp and also the time zone that time was recorded in. Presumably everything else is an insane ca

Re: Re: Re: Timestamp with vs without time zone.

Yes it would record the timestamp and then also record the time zone. That way all the conversion functions would still work. That's the way it works in the programming languages I use anyway. On Tue, Sep 21, 2021 at 8:09 PM Karsten Hilbert wrote: > > > > > It seems like it would be so much more

Re: Re: Timestamp with vs without time zone.

On 2021-09-21 09:39:59 +0200, Karsten Hilbert wrote: > > It seems like it would be so much more useful if the timestamp with > > time zone type actually stored the time zone in the record. > > Which one ? To expand on that question a bit: There are several formats to specify a time zone: By offs

Aw: Re: Re: Timestamp with vs without time zone.

> > > It seems like it would be so much more useful if the timestamp with > > > time zone type actually stored the time zone in the record. > > > > Which one ? > > > One specified by the user. Many date formats carry either an offset > or the time zone information. What would that TZ mean, exactl

Re: Re: Timestamp with vs without time zone.

One specified by the user. Many date formats carry either an offset or the time zone information. On Tue, Sep 21, 2021 at 7:39 PM Karsten Hilbert wrote: > > > It seems like it would be so much more useful if the timestamp with > > time zone type actually stored the time zone in the record. > > W

Aw: Re: Timestamp with vs without time zone.

> > It seems like it would be so much more useful if the timestamp with > > time zone type actually stored the time zone in the record. > > This has been requested before, and it would be closer to the intention > of the SQL standard, but I guess it won't happen. > > For one, it would change on-dis

Aw: Re: Timestamp with vs without time zone.

> It seems like it would be so much more useful if the timestamp with > time zone type actually stored the time zone in the record. Which one ? Karsten

Re: Timestamp with vs without time zone.

On Tue, 2021-09-21 at 19:35 +1200, Tim Uckun wrote: > It seems like it would be so much more useful if the timestamp with > time zone type actually stored the time zone in the record. This has been requested before, and it would be closer to the intention of the SQL standard, but I guess it won't

Re: Timestamp with vs without time zone.

It seems like it would be so much more useful if the timestamp with time zone type actually stored the time zone in the record. On Tue, Sep 21, 2021 at 7:25 PM Laurenz Albe wrote: > > On Tue, 2021-09-21 at 18:00 +1200, Tim Uckun wrote: > > I am hoping to get some clarification on timestamp with

Re: Timestamp with vs without time zone.

On Tue, 2021-09-21 at 18:00 +1200, Tim Uckun wrote: > I am hoping to get some clarification on timestamp with time zone. > > My understanding is that timestamp with time zone stores data in UTC > but displays it in your time zone. That is correct. When a timestamp is rendered as string, it it sho

Timestamp with vs without time zone.

I am hoping to get some clarification on timestamp with time zone. My understanding is that timestamp with time zone stores data in UTC but displays it in your time zone. Does this also work on queries? If I query between noon and 2:00 PM on some date in time zone XYZ does pg translate the query