From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Shane <shane-pgsql(at)cm(dot)nu> |
Cc: | juleni(at)livetrade(dot)cz, pgsql-general(at)postgresql(dot)org |
Subject: | Re: TIMESTAMP vs TIMESTAMP WITHOUT TIME ZONE |
Date: | 2005-11-11 02:47:22 |
Message-ID: | 20051111024722.GA88086@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Nov 10, 2005 at 02:44:28PM -0800, Shane wrote:
> The only thing I would add is you don't seem to be able to
> index a part of the timestamp with time zone value. As an
> example, I had a table with around 10m rows where I wanted
> to query by date. In order to add an index liki
> date_part(mytimestamp), you need to use timestamp without
> time zone.
Indexing part of a timestamp with time zone would be ambiguous.
For example, what date would you index for 2005-11-10 23:00:00-10?
If you're in Hawaii that moment's date is 2005-11-10, but nearly
everywhere else it's 2005-11-11. You can cheat by wrapping
date-extracting code in your own immutable function and indexing
on that function, but be sure you understand the problem with doing
so. Example:
CREATE FUNCTION mydate(timestamptz) RETURNS date AS $$
SELECT date($1);
$$ LANGUAGE sql IMMUTABLE STRICT;
CREATE TABLE foo (
id serial PRIMARY KEY,
ts timestamp with time zone NOT NULL
);
CREATE INDEX foo_date_idx ON foo (mydate(ts));
SET timezone TO 'US/Hawaii';
INSERT INTO foo (ts) VALUES ('2005-11-10 23:00:00-10');
SELECT * FROM foo;
id | ts
----+------------------------
1 | 2005-11-10 23:00:00-10
(1 row)
SELECT * FROM foo WHERE mydate(ts) = '2005-11-10';
id | ts
----+------------------------
1 | 2005-11-10 23:00:00-10
(1 row)
SET timezone TO 'Asia/Tokyo';
SELECT * FROM foo;
id | ts
----+------------------------
1 | 2005-11-11 18:00:00+09
(1 row)
SELECT * FROM foo WHERE mydate(ts) = '2005-11-11';
id | ts
----+----
(0 rows)
SET enable_indexscan TO off;
SELECT * FROM foo WHERE mydate(ts) = '2005-11-11';
id | ts
----+------------------------
1 | 2005-11-11 18:00:00+09
(1 row)
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Uwe C. Schroeder | 2005-11-11 03:44:36 | Re: Where |
Previous Message | Richard Sydney-Smith | 2005-11-11 02:09:37 | Re: pgdump from 8.0.4 not valid for import into 8.1.0 |