Thread: pgsql DATE
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.
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/
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/
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/
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.
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