Thread

1. pgsql DATE

   Andras Balogh <abalogh@grafx.ro> — 2000-04-26T11:49:39Z

               Hi all,
   
    I have a question. I don't know what date type to use with postgres.
   I have to have a date like '26/04/2000 14:30'. I have created my field as
   varchar
   and doing all the querying with comparing:
   SELECT * FROM  pages WHERE  date_time  ~*  '04/2000'
   This would return all  records from April 2000.
   
   But if in my table are lot of records(150.000) this will be very slow. I
   have thought to
   create an index but in won't help me because the date_time field is varchar.
   
   So i am looking a date type that would allow me to do the following with one
   (or a few) query
   (and to be fast too):
   -select all records for a given month
   -select the count of records where the hour is 14.
   -select  all the records from the 7th week.
   -select count of records  where the day of the week is Sunday.
   etc.
   
   It is possible to create indexes with date types?
   
   If anybody has some comments please answer me.
   
   Thank You for reading this mail.
   
   Best wishes,
   
                       Andras.
   
   
   
   

2. Re: pgsql DATE

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-04-26T14:41:39Z

   Andras Balogh wrote:
   > So i am looking a date type that would allow me to do the following with one
   > (or a few) query
   > (and to be fast too):
   > -select all records for a given month
   > -select the count of records where the hour is 14.
   > -select  all the records from the 7th week.
   > -select count of records  where the day of the week is Sunday.
   > etc.
   
   How about you write your queries like:
   
   date >= '01/4/2000' and date < '01/5/2000'
   
   or equivalent. It allows use of the index but works the same.
   Except for the day is Sunday though...
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/
   
   
   

3. Re: pgsql DATE

   Charles Tassell <ctassell@isn.net> — 2000-04-26T18:06:19Z

   Might want to try date BETWEEN '01/4/2000' AND '01/5/2000' as well,  this 
   is probably optimized better (although I may be wrong.)
   
   At 11:41 AM 4/26/00, Martijn van Oosterhout wrote:
   >Andras Balogh wrote:
   > > So i am looking a date type that would allow me to do the following 
   > with one
   > > (or a few) query
   > > (and to be fast too):
   > > -select all records for a given month
   > > -select the count of records where the hour is 14.
   > > -select  all the records from the 7th week.
   > > -select count of records  where the day of the week is Sunday.
   > > etc.
   >
   >How about you write your queries like:
   >
   >date >= '01/4/2000' and date < '01/5/2000'
   >
   >or equivalent. It allows use of the index but works the same.
   >Except for the day is Sunday though...
   >--
   >Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   >http://cupid.suninternet.com/~kleptog/
   
   
   

4. Re: pgsql DATE

   Andras Balogh <abalogh@grafx.ro> — 2000-04-27T08:03:13Z

   Hi,
   
   Thank You for your reply.
   BUT as i mentioned the date_time field is VARCHAR.
   So if i use:
   date >= '01/4/2000' and date < '01/5/2000'
   
   this won't compare the dates it will compare Strings.
   so '02/4/2000' will be GREATER than '01/5/2000'.
   
   That is why i need a DATE TYPE for my field that can be used to create an
   index.
   
   So any other ideas?
   
   
   
   
   ----- Original Message -----
   From: Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   To: Andras Balogh <abalogh@grafx.ro>
   Cc: <pgsql-general@postgresql.org>
   Sent: Wednesday, April 26, 2000 5:41 PM
   Subject: Re: [GENERAL] pgsql DATE
   
   
   > Andras Balogh wrote:
   > > So i am looking a date type that would allow me to do the following with
   one
   > > (or a few) query
   > > (and to be fast too):
   > > -select all records for a given month
   > > -select the count of records where the hour is 14.
   > > -select  all the records from the 7th week.
   > > -select count of records  where the day of the week is Sunday.
   > > etc.
   >
   > How about you write your queries like:
   >
   > date >= '01/4/2000' and date < '01/5/2000'
   >
   > or equivalent. It allows use of the index but works the same.
   > Except for the day is Sunday though...
   > --
   > Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   > http://cupid.suninternet.com/~kleptog/
   
   
   

5. Re: pgsql DATE

   Lincoln Yeoh <lylyeoh@mecomb.com> — 2000-04-27T09:28:35Z

   At 11:03 AM 27-04-2000 +0300, Andras Balogh wrote:
   >Hi,
   >
   >Thank You for your reply.
   >BUT as i mentioned the date_time field is VARCHAR.
   >So if i use:
   >date >= '01/4/2000' and date < '01/5/2000'
   >
   >this won't compare the dates it will compare Strings.
   >so '02/4/2000' will be GREATER than '01/5/2000'.
   >
   >That is why i need a DATE TYPE for my field that can be used to create an
   >index.
   >
   >So any other ideas?
   
   If you need a date type for your field, use a date type.
   
   http://www.postgresql.org/docs/postgres/datatype1143.htm
   
   Cheerio,
   Link.
   
   
   

6. Re: pgsql DATE

   Peter Eisentraut <peter_e@gmx.net> — 2000-05-01T19:49:01Z

   Charles Tassell writes:
   
   > Might want to try date BETWEEN '01/4/2000' AND '01/5/2000' as well,  this 
   > is probably optimized better (although I may be wrong.)
   
   It isn't. It's immediately converted to the below, so it's actually a few
   cycles slower to use it.
   
   > >How about you write your queries like:
   > >
   > >date >= '01/4/2000' and date < '01/5/2000'
   
   
   -- 
   Peter Eisentraut                  Sernanders väg 10:115
   peter_e@gmx.net                   75262 Uppsala
   http://yi.org/peter-e/            Sweden