On Fri, 2008-10-31 at 08:48 +0100, A. Kretschmer wrote: > am Thu, dem 30.10.2008, um 14:49:16 -0600 mailte Ryan Hansen folgendes: > > 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. > > Any suggestions? > Sure. > You can create an index based on a function, but only if the function is > immutable: > test=# create table foo (ts timestamptz); > CREATE TABLE > test=*# create index idx_foo on foo(extract(date from ts)); > ERROR: functions in index expression must be marked IMMUTABLE > To solve this problem specify the timezone: > For the same table as above: > test=*# create index idx_foo on foo(extract(date from ts at time zone 'cet')); > CREATE INDEX
I'm attempting to create an index as specified in this [old] thread; but the adapted example fails. OGo=> create index job_date_only on job(extract(date from start_date at time zone 'utc')); ERROR: timestamp units "date" not recognized I assume this is because the data type is 'timestamp with timezone' which differs slightly from the original example. But - select extract(month from start_date) from job; - [for example] works. Is there an equivalent syntax to 'date' for timestamp? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
