Thread: Date Index
<div class="Section1"><p class="MsoNormal">Hey all,<p class="MsoNormal"> <p class="MsoNormal">I’m apparently too lazy tofigure this out on my own so maybe one of you can just make it easy on me. <span style="font-family:Wingdings">J</span> <p class="MsoNormal"> <p class="MsoNormal">I want to index a timestamp field but Ionly 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 forthis.<p class="MsoNormal"> <p class="MsoNormal">Any suggestions?<p class="MsoNormal"> <p class="MsoNormal">Thanks!</div>
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.
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 If you have a timestamp whitout timezone it is much easier: test=# create table foo (ts timestamp); CREATE TABLE test=*# create index idx_foo on foo(extract(date from ts)); CREATE INDEX Hope thats help, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Incidentally, extract(date from ts) doesn't work on my install of 8.3 (standard Ubuntu Hardy apt install). I get a "timestamp units "date" not recognized" error when I try it. The field I'm trying to create it on is "timestamp without time zone". -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of A. Kretschmer Sent: Friday, October 31, 2008 1:49 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Date Index 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 If you have a timestamp whitout timezone it is much easier: test=# create table foo (ts timestamp); CREATE TABLE test=*# create index idx_foo on foo(extract(date from ts)); CREATE INDEX Hope thats help, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
"Ryan Hansen" <ryan.hansen@brightbuilders.com> writes: > Incidentally, extract(date from ts) doesn't work on my install of 8.3 The field names recognized by extract() are quite well documented http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT and "date" isn't one of them. What you probably want is just to cast the timestamp to date:cast(ts as date) -- SQL standard syntaxts::date --traditional Postgres abbreviation regards, tom lane
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?
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