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: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

> Adrian Klaver wrote: > > On 9/21/21 4:23 PM, Bryn Llewellyn wrote: >> I'm surprised by the results that I describe below. Please help me interpret >> them. >> Briefly, why does transaction_timestamp() report a later value than >> statement_timestamp() when they're both invoked in the very

Thanks, David! Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

> David G. Johnston wrote: > > Bryn wrote: > > I'm surprised by the results that I describe below. Please help me interpret > them. > > Briefly, why does transaction_timestamp() report a later value than > statement_timestamp() when they're both invoked in the very first statement > after

Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

On 9/21/21 4:23 PM, Bryn Llewellyn wrote: I'm surprised by the results that I describe below. Please help me interpret them. Briefly, why does transaction_timestamp() report a later value than statement_timestamp() when they're both invoked in the very first statement after "start

Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

On Tuesday, September 21, 2021, Bryn Llewellyn wrote: > I'm surprised by the results that I describe below. Please help me > interpret them. > > Briefly, why does transaction_timestamp() report a later value > than statement_timestamp() when they're both invoked in the very first > statement

Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

I'm surprised by the results that I describe below. Please help me interpret them. Briefly, why does transaction_timestamp() report a later value than statement_timestamp() when they're both invoked in the very first statement after "start transaction". (They report identical values in an

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

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

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

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

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

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

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

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

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.

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

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

Re: Question about behavior of conditional indexes

You likely need to tune your autovacuum settings such that the index bloat reaches a steady-state and does not continue to increase. When vacuum runs, it will remove dead (and no longer visible to any running transactions) tuples aka row versions from each page (8KB block by default) in the file

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

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

Re: Question about behavior of conditional indexes

On Tue, 21 Sept 2021 at 15:59, Koen De Groote wrote: > Greetings all, > > Working on postgres 11. > > I'm researching an index growing in size and never shrinking, and not > being used anymore after a while. > > The index looks like this: > > "index002" btree (action_performed,

Re: Question about behavior of conditional indexes

On 21/09/21 22:28, Koen De Groote wrote: Greetings all, Working on postgres 11. I'm researching an index growing in size and never shrinking, and not being used anymore after a while. The index looks like this: |"index002" btree (action_performed, should_still_perform_action,

Question about behavior of conditional indexes

Greetings all, Working on postgres 11. I'm researching an index growing in size and never shrinking, and not being used anymore after a while. The index looks like this: "index002" btree (action_performed, should_still_perform_action, action_performed_at DESC) WHERE should_still_perform_action

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

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

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

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,

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

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

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: Possibilities for optimizing inserts across oracle_fdw foreign data wrapper

On Mon, 2021-09-20 at 15:41 +0200, Magnus Hagander wrote: > Just as a note, with PostgreSQL 14 the FDW APIs allow batch insert. It > should be possible to update oracle_fdw to take advantage of that as > well, right? Yes. The exercise is more complicated in Oracle, because they don't support

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

Synchronous logical replication

Hello, I have a question about synchronous logical replication. There is the following description in the document "49.8. Synchronous Replication Support for Logical Decoding". > A synchronous replica receiving changes via logical decoding will work in the > scope of a single database. >

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

SV: Possibilities for optimizing inserts across oracle_fdw foreign data wrapper

-Oprindelig meddelelse- >Fra: Laurenz Albe >Sendt: 20. september 2021 15:18 >Til: Niels Jespersen ; pgsql-gene...@postgresql.org >Emne: Re: Possibilities for optimizing inserts across oracle_fdw foreign data >wrapper > >On Sun, 2021-09-19 at 10:28 +, Niels Jespersen wrote: >> We are

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