Thread

1. RE: [SQL] indexing a datetime by date

   Jackson, DeJuan <djackson@cpsgroup.com> — 1999-03-30T04:07:20Z

   Part of the problem is that PostgreSQL Assumes that a functions value will
   change each time it is required, therefore automatic table scan and the
   function is called for each row.
   Try using 'now'::date instead of now()::date
   You index creation syntax is good but there's a bug in function indexes
   which require you to specify the ops. Try:
    create index when_ndx3 on notes (date(when) date_ops);
   
   Which won't work because the date(datetime) function isn't trusted.
   You can change this yourself in the system tables or you can use PL/PGSQL
   (the only trustable PL in PostgreSQL that I've found) to create another
   conversion function and use it instead.  Or you can as Thomas Lockhart (or
   is it Tom Lane) if he'd create a trusted function for the conversions in
   6.5.
   DISCLAIMER: I haven't tested this on the current CSV(?CVS I just can't think
   tonight) so it might already be fixed.
   	-DEJ
   
   > -----Original Message-----
   > From: Andrew Merrill [mailto:andrew@compclass.com]
   > Sent: Monday, March 29, 1999 9:28 PM
   > To: pgsql-sql@hub.org
   > Subject: [SQL] indexing a datetime by date
   > 
   > 
   > I have a table with a field, "when", of type "datetime".  I can't use
   > "date" because I need the times as well.  I'm using PostgreSQL 6.4.2.
   > 
   > I'd like to identify all of the records with today's date, as in:
   > 
   > select when from notes where when::date = now()::date;
   > 
   > The query works, but is very slow.  Explain confirms that a sequential
   > scan is being used.
   > 
   > I've tried indexing on when:
   > 
   > create index when_ndx1 on notes (when);
   > 
   > But that doesn't help, as (I suppose) the optimizer can't match
   > when::date with this index.
   > 
   > Neither of these works:
   > 
   > db=> create index when_ndx2 on notes (when::date);
   > ERROR:  parser: parse error at or near "::"
   > 
   > db=> create index when_ndx3 on notes (date(when));
   > ERROR:  DefineIndex:  class not found
   > 
   > As a workaround, I've been using this:
   > 
   > select when from notes where when >= '3/29/1999 0:0:0' and when <=
   > '3/29/1999 23:59:59';
   > 
   > but that's ugly and requires hardcoding today's date each time, rather
   > than using now().
   > 
   > So, the question is, is there a way to index a datetime field by date?
   > 
   > Andrew Merrill
   > 
   > 
   
   

2. Re: [SQL] indexing a datetime by date

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-03-30T06:14:13Z

   > Your index creation syntax is good but there's a bug in function 
   > indexes which require you to specify the ops. Try:
   >  create index when_ndx3 on notes (date(when) date_ops);
   > Which won't work because the date(datetime) function isn't trusted.
   > You can change this yourself in the system tables or you can use 
   > PL/PGSQL (the only trustable PL in PostgreSQL that I've found) to 
   > create another conversion function and use it instead.  Or you can as 
   > Thomas Lockhart (or is it Tom Lane) if he'd create a trusted function 
   > for the conversions in 6.5.
   
   Tom, does this ring a bell with you? istm that (almost) all builtin
   functions should be trusted, but I haven't done anything explicit
   about it that I can remember.
   
   In your new role as System Table Berserker, perhaps you would want to
   fix this? :)
   
                          - Tom
   
   

3. RE: [SQL] indexing a datetime by date

   Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-03-30T11:59:14Z

   At 06:07 +0200 on 30/03/1999, Jackson, DeJuan wrote:
   
   
   >
   > Part of the problem is that PostgreSQL Assumes that a functions value will
   > change each time it is required, therefore automatic table scan and the
   > function is called for each row.
   > Try using 'now'::date instead of now()::date
   
   How about using the ANSI standard CURRENT_DATE instead of either? It's
   already of type date. Or is it considered a function call?
   
   Herouth
   
   --
   Herouth Maoz, Internet developer.
   Open University of Israel - Telem project
   http://telem.openu.ac.il/~herutma