From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Ryan Hansen" <ryan(dot)hansen(at)brightbuilders(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Date Index |
Date: | 2008-10-30 21:06:15 |
Message-ID: | dcc563d10810301406n2743bd67x43e250b1852525b2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, Oct 30, 2008 at 2:49 PM, Ryan Hansen
<ryan(dot)hansen(at)brightbuilders(dot)com> wrote:
> Hey all,
>
>
>
> I'm apparently too lazy to figure this out on my own so maybe one of you can
> just make it easy on me. J
>
>
>
> I want to index a timestamp field but I only want the index to include the
> yyyy-mm-dd portion of the date, not the time. I figure this would be where
> the "expression" portion of the CREATE INDEX syntax would come in, but I'm
> not sure I understand what the syntax would be for this.
Really depends on what you want to do with it. Easiest way is to cast it:
smarlowe=# create table dtest (id int, ts timestamp);
CREATE TABLE
smarlowe=# insert into dtest values (1,'2008-09-01 12:30:00');
INSERT 0 1
smarlowe=# insert into dtest values (1,'2008-09-02 10:30:00');
INSERT 0 1
create index dtest_tstodate on dtest ((ts::date));
CREATE INDEX
set enable_seqscan=off;
SET
explain select * from dtest where ts::date='2009-09-02';
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using dtest_tstodate on dtest (cost=0.00..8.27 rows=1 width=12)
Index Cond: ((ts)::date = '2009-09-02'::date)
(2 rows)
Note that since the table is so small the db would have seq scanned it
if I hadn't turned off seqscans to test. But since it used the index,
that proves it's there and working.
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2008-10-31 07:48:54 | Re: Date Index |
Previous Message | Ryan Hansen | 2008-10-30 20:49:16 | Date Index |