Thread: TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice...
What's the point of indexing a column with TIMESTAMP WITH TIME ZONE? More to the point, why aren't all timestamp formats stored as TIMESTAMP WITHOUT TIME ZONE and then converted to WITHOUT at parse time? Only reason I ask is because I was banging my head against a query that was doing a sequential scan over some 40M rows when I had a perfectly valid TIMESTAMP WITHOUT TIME ZONE index and that got me thinking that the two should be the same on disk (which they are) and in the planner's mind (which they aren't). Since all timestamps are internally calculated and stored as Julian dates, why doesn't the planner treat all timestamps as the same regardless of whether or not a timezone is specified. This post is rhetorical post-thump therapy, if anything. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> What's the point of indexing a column with TIMESTAMP WITH TIME ZONE?
> More to the point, why aren't all timestamp formats stored as
> TIMESTAMP WITHOUT TIME ZONE and then converted to WITHOUT at parse
> time?
Those are two different types, with different behaviors. We're not
about to eliminate a hard-won distinction that's required by the spec.
Also, although the on-disk format of the two types might be the same,
that doesn't mean they have the same interpretation of a given byte
value. They don't (unless you run your database with TimeZone = GMT).
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Sean Chittenden <sean@chittenden.org> writes: > > Also, although the on-disk format of the two types might be the same, > that doesn't mean they have the same interpretation of a given byte > value. They don't (unless you run your database with TimeZone = GMT). This is a point I'm unclear on as well, the documentation is very confusing. The documentation I've read makes it sound like these two data types are equivalent in every way except for the default timezone assumed interpretation when converting to and from textual representations. Is that not true? -- greg
Greg Stark <gsstark@mit.edu> writes:
> The documentation I've read makes it sound like these two data types are
> equivalent in every way except for the default timezone assumed interpretation
> when converting to and from textual representations. Is that not true?
I wouldn't think so. For example, you get dissimilar results near
daylight-savings-time boundaries:
regression=# select '2003-04-06 01:00'::timestamptz + '3 hours'::interval;
?column?
------------------------
2003-04-06 05:00:00-04
(1 row)
regression=# select '2003-04-06 01:00'::timestamp + '3 hours'::interval;
?column?
---------------------
2003-04-06 04:00:00
(1 row)
Now in some sense I suppose you could regard this as strictly a matter
of textual display --- the underlying stored values are indeed three
hours apart in both cases. But in practice I think it's a lot easier
to regard the datatypes as having distinct semantics. timestamptz
is "GMT under the hood": it has a definite idea that it is storing an
absolute time with a universal meaning, which it will translate into
the local timezone during I/O. timestamp just stores the nominal
HH:MM:SS value you give it, with no sense that it knows what time that
really is, and no attempt to correct for different local timezones nor
for daylight-savings changes. The applications of the two types are
very different.
Because the semantics are in fact different, conversion between
timestamp and timestamptz is not just a binary-equivalent mapping:
there is arithmetic happening in here. And that's why the previous
suggestion that we could index them interchangeably doesn't fly.
regards, tom lane
I've been following this thread, and I am planning a multinational
project. So, will this work correctly? ( I am still waiting for SSH
access to my site to play with Postgres ):
create table test_timestamp(
created timestamp DEFAULT CURRENT_TIMESTAMP::timestamptz NOT NULL
);
Will that be a UTC based timestamp value?
Tom Lane wrote:
>
> Greg Stark <gsstark@mit.edu> writes:
> > The documentation I've read makes it sound like these two data types are
> > equivalent in every way except for the default timezone assumed interpretation
> > when converting to and from textual representations. Is that not true?
>
> I wouldn't think so. For example, you get dissimilar results near
> daylight-savings-time boundaries:
>
> regression=# select '2003-04-06 01:00'::timestamptz + '3 hours'::interval;
> ?column?
> ------------------------
> 2003-04-06 05:00:00-04
> (1 row)
>
> regression=# select '2003-04-06 01:00'::timestamp + '3 hours'::interval;
> ?column?
> ---------------------
> 2003-04-06 04:00:00
> (1 row)
>
> Now in some sense I suppose you could regard this as strictly a matter
> of textual display --- the underlying stored values are indeed three
> hours apart in both cases. But in practice I think it's a lot easier
> to regard the datatypes as having distinct semantics. timestamptz
> is "GMT under the hood": it has a definite idea that it is storing an
> absolute time with a universal meaning, which it will translate into
> the local timezone during I/O. timestamp just stores the nominal
> HH:MM:SS value you give it, with no sense that it knows what time that
> really is, and no attempt to correct for different local timezones nor
> for daylight-savings changes. The applications of the two types are
> very different.
>
> Because the semantics are in fact different, conversion between
> timestamp and timestamptz is not just a binary-equivalent mapping:
> there is arithmetic happening in here. And that's why the previous
> suggestion that we could index them interchangeably doesn't fly.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon
Dennis Gearon <gearond@cvc.net> writes:
> So, will this work correctly? ( I am still waiting for SSH
> access to my site to play with Postgres ):
> create table test_timestamp(
> created timestamp DEFAULT CURRENT_TIMESTAMP::timestamptz NOT NULL
> );
I think you want
created timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL
CURRENT_TIMESTAMP is already timestamptz, you need not cast it. But you
do want the column to be type timestamptz not timestamp.
regards, tom lane
> create table test_timestamp(
> created timestamp DEFAULT CURRENT_TIMESTAMP::timestamptz NOT NULL
> );
Don't take chances:
CREATE TABLE test (
created TIMESTAMP WITHOUT TIME ZONE DEFAULT 'CURRENT_TIMESTAMP' NOT NULL
);
iirc, you have to put quotes around CURRENT_TIMESTAMP otherwise the
default value is set to the CURRENT_TIMESTAMP at this very instant
instead of being evaluated at runtime when a record is inserted. -sc
--
Sean Chittenden
I have a manual in PDF for 7.2. On page 25 of the user manual, it has a
three column table. Columns are labeled:
Type name / Aliases / Description.
'timestamptz' appears in the 'Aliases' column.
The types in 'Aliases' are legal and future stable?
--
Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon
Sean Chittenden <sean@chittenden.org> writes:
> iirc, you have to put quotes around CURRENT_TIMESTAMP otherwise the
> default value is set to the CURRENT_TIMESTAMP at this very instant
> instead of being evaluated at runtime when a record is inserted. -sc
Not so --- CURRENT_TIMESTAMP is a function, not a literal, despite the
lack of parentheses (you can blame the SQL spec for that weirdness).
It will in fact *not* work when quoted.
I think you are thinking of now() --- now() is a function, 'now' is
an acceptable timestamp literal, but the latter is generally a bad
choice for a column default because of the time-of-evaluation issue.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > The documentation I've read makes it sound like these two data types are > > equivalent in every way except for the default timezone assumed interpretation > > when converting to and from textual representations. Is that not true? > > I wouldn't think so. For example, you get dissimilar results near > daylight-savings-time boundaries: Well how is that different from just saying the timestamp is always in GMT? The confusing part is what happens when you cast from a timestamptz to a timestamp. It doesn't seem to adjust for the current time zone of the timestamptz, it just drops it. > timestamp just stores the nominal HH:MM:SS value you give it, with no sense > that it knows what time that really is, and no attempt to correct for > different local timezones nor for daylight-savings changes. Ok, I guess I understand now the difference between timestamp and timestamptz, I just don't see what use a timestamp that doesn't represent a particular time would ever be. It seems to serve only as a gotcha for unwary programmers who take the default. -- greg
Greg Stark <gsstark@mit.edu> writes:
> I just don't see what use a timestamp that doesn't represent a particular time
> would ever be. It seems to serve only as a gotcha for unwary programmers who
> take the default.
I have a problem with that too :-( ... timestamptz seems much the more
generally useful type. But the SQL spec is pretty definite that
"timestamp" means "timestamp without time zone".
Is anybody interested in the idea of a GUC parameter to choose whether
"timestamp" means "timestamp with time zone" (useful) or "timestamp
without time zone" (anally spec-compliant)? Or would that just muddy
the waters even more than they are already? We already changed the
meaning once...
regards, tom lane
I think a switch would be useful, because it means that people who don't need the TZ can be compliant if they want to. The default should be WITH time zone. When the Time Zone is off, the time zone is actually recorded as +00, so The NO timezone version makes sense for people who only want to time stamp events in their computer. For those people, the TZ is wasted space. Maybe they wrote the spec. The TZ is only useful if you want to record events in different time zones - ie - outside your computer. The argument for the simpler, no time zone version would be that you can convert any time to any time zone, in the special case (to them, more general case in my opinion) where you have info from multiple time zones. I am archiving mail messages using a timestamptz. The time zone is useful to me. Of course, I usually program in OmniMark, which uses a 4 digit time zone with date/time values, so it is easy and natural to record times that way. Yes, I said 4 digit time zone. Have you heard of Newfoundland? Their time zone is 1/2 hour off the grid, I think it's minus 3hrs and 30 minutes. You can't represent that in PostgreSQL, but you can in OmniMark. There are several other areas with time zones that are not exact hours, possibly not half hours. Robert Echlin Infrastructure Specialist OmniMark Technologies Ottawa, Ontario, Canada rechlin@omnimark.com This message, including any attachments, is for the sole use of the intended recipient and may contain confidential and privileged information. Any unauthorized review, use, disclosure, copying, or distribution is strictly prohibited. If you are not the intended recipient please contact the sender by reply email and destroy all copies of the original message and any attachments. ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Greg Stark" <gsstark@mit.edu> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, February 19, 2003 1:34 AM Subject: Re: [GENERAL] TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice... > Greg Stark <gsstark@mit.edu> writes: > > I just don't see what use a timestamp that doesn't represent a particular time > > would ever be. It seems to serve only as a gotcha for unwary programmers who > > take the default. > > I have a problem with that too :-( ... timestamptz seems much the more > generally useful type. But the SQL spec is pretty definite that > "timestamp" means "timestamp without time zone". > > Is anybody interested in the idea of a GUC parameter to choose whether > "timestamp" means "timestamp with time zone" (useful) or "timestamp > without time zone" (anally spec-compliant)? Or would that just muddy > the waters even more than they are already? We already changed the > meaning once... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > I just don't see what use a timestamp that doesn't represent a particular time > > would ever be. It seems to serve only as a gotcha for unwary programmers who > > take the default. > > I have a problem with that too :-( ... timestamptz seems much the more > generally useful type. But the SQL spec is pretty definite that > "timestamp" means "timestamp without time zone". > > Is anybody interested in the idea of a GUC parameter to choose whether > "timestamp" means "timestamp with time zone" (useful) or "timestamp > without time zone" (anally spec-compliant)? Or would that just muddy > the waters even more than they are already? We already changed the > meaning once... I think it would be a better idea to stick with the standard but spam the user with plenty of notification that they're probably doing the wrong thing. I'm thinking a warning whenever converting from timestamp with timezone to time zone, "Warning conversion to timestamp without time zone loses information, consider using timestamp with time zone". This would basically force users of timestamps to use localtimestamp and not current_timestamp to avoid the warnings. -- greg
On Wed, Feb 19, 2003 at 12:55:30PM -0500, Greg Stark wrote:
> I think it would be a better idea to stick with the standard but
> spam the user with plenty of notification that they're probably
> doing the wrong thing. I'm
Please don't. That's the sort of naggy thing that drives one mad
when one _really_ wants the behaviour one is asking for.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Wed, Feb 19, 2003 at 01:34:26AM -0500, Tom Lane wrote:
> Is anybody interested in the idea of a GUC parameter to choose whether
> "timestamp" means "timestamp with time zone" (useful) or "timestamp
> without time zone" (anally spec-compliant)? Or would that just muddy
> the waters even more than they are already? We already changed the
> meaning once...
Well, if you added the GUC and made the default the (silly, I agree)
SQL-spec behaviour, then those who care about this sort of thing will
be able to put it back the way they want.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110