pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Date Index
2+ messages / 2 participants
[nested] [flat]
* Re: Date Index
@ 2012-11-05 10:54 Adam Tauno Williams <awilliam@whitemice.org>
2012-11-05 15:13 ` Re: Date Index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Adam Tauno Williams @ 2012-11-05 10:54 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Date Index
2012-11-05 10:54 Re: Date Index Adam Tauno Williams <awilliam@whitemice.org>
@ 2012-11-05 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2012-11-05 15:13 UTC (permalink / raw)
To: awilliam@whitemice.org; +Cc: pgsql-sql
Adam Tauno Williams <awilliam@whitemice.org> writes:
> OGo=> create index job_date_only on job(extract(date from start_date at
> time zone 'utc'));
> ERROR: timestamp units "date" not recognized
There's no field called "date" in a timestamp. I think what you're
trying to achieve is "date_trunc('day', start_date at time zone 'utc')"
http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-11-05 15:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-05 10:54 Re: Date Index Adam Tauno Williams <awilliam@whitemice.org>
2012-11-05 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox