TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice...

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice...
Date: 2003-02-17 19:53:54
Message-ID: 20030217195354.GW27077@perrin.int.nxad.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Randy Zierman 2003-02-17 19:55:37 Compatible UNION query for postgresql and MS SQL Server
Previous Message Gregory Wood 2003-02-17 19:42:39 Re: PostGreSQL Replication question !!