Re: TIMESTAMP vs TIMESTAMP WITHOUT TIME ZONE

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