Re: Date Index

On Thu, Oct 30, 2008 at 2:49 PM, Ryan Hansen
<ryan.hansen@brightbuilders.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 1create 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.27rows=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.