Обсуждение: PostGreSQL Date Query?
Hi,
I'm using Java together with PostGreSQL database.
I'm having a problem with a date query, when I write:
select sRescheduleDate, * from tbl_leads
where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= '28/01/2005'
and bRescheduleCall = true
the query also return the values for "01/04/2005" where it should only
return those between that date range.
Anyone can help me.
Using Java 2 SE 1.4.2, PostGreSQL 8.0
Kind Regards,
Lennie De Villiers
On Tue, 2005-03-08 at 15:51, Lennie De Villiers wrote: > Hi, > > > I'm using Java together with PostGreSQL database. > > > > I'm having a problem with a date query, when I write: > > > > select sRescheduleDate, * from tbl_leads > > where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= > '28/01/2005' > > and bRescheduleCall = true > > > > the query also return the values for "01/04/2005" where it should only > return those between that date range. Sounds like your month and day are not where you think they are. http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT
Maybe you have a wrong datestyle format. Try a query with '01/01/2005' and '01/28/2005' range, in month-day-year format. Diego. El mar, 08-03-2005 a las 23:51 +0200, Lennie De Villiers escribió: > Hi, > > > I'm using Java together with PostGreSQL database. > > > > I'm having a problem with a date query, when I write: > > > > select sRescheduleDate, * from tbl_leads > > where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= > '28/01/2005' > > and bRescheduleCall = true > > > > the query also return the values for "01/04/2005" where it should only > return those between that date range. > > Anyone can help me. > > > > Using Java 2 SE 1.4.2, PostGreSQL 8.0 > > > Kind Regards, > > > > Lennie De Villiers > > > > >
That's very strange. have you tried the query in psql. Also you can use the between clause. I'd also recommend using prepared statements and setDate, just to make sure the date is formatted correctly. Dave Lennie De Villiers wrote: > Hi, > > > I'm using Java together with PostGreSQL database. > > > > I'm having a problem with a date query, when I write: > > > > select sRescheduleDate, * from tbl_leads > > where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= '28/01/2005' > > and bRescheduleCall = true > > > > the query also return the values for "01/04/2005" where it should only > return those between that date range. > > Anyone can help me. > > > > Using Java 2 SE 1.4.2, PostGreSQL 8.0 > > > Kind Regards, > > > > Lennie De Villiers > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
That's what I thought too, but pg should give him an error if he is asking for the 28th month. Dave Scott Marlowe wrote: >On Tue, 2005-03-08 at 15:51, Lennie De Villiers wrote: > > >>Hi, >> >> >>I'm using Java together with PostGreSQL database. >> >> >> >>I'm having a problem with a date query, when I write: >> >> >> >>select sRescheduleDate, * from tbl_leads >> >>where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= >>'28/01/2005' >> >>and bRescheduleCall = true >> >> >> >>the query also return the values for "01/04/2005" where it should only >>return those between that date range. >> >> > >Sounds like your month and day are not where you think they are. > >http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend > > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
>Sounds like your month and day are not where you think they are. In which case you will get an error message saying that "date/time field value out of range." -Prasanth. -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Scott Marlowe Sent: Thursday, March 10, 2005 2:39 PM To: Lennie De Villiers Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] PostGreSQL Date Query? On Tue, 2005-03-08 at 15:51, Lennie De Villiers wrote: > Hi, > > > I'm using Java together with PostGreSQL database. > > > > I'm having a problem with a date query, when I write: > > > > select sRescheduleDate, * from tbl_leads > > where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= > '28/01/2005' > > and bRescheduleCall = true > > > > the query also return the values for "01/04/2005" where it should only > return those between that date range. Sounds like your month and day are not where you think they are. http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-DA TETIME-INPUT ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
You'd think that as the guy who'd pissed and moaned until the old behaviour was changed I would have thought of that. but no... Too much going on today. I bet it's a text field. On Thu, 2005-03-10 at 14:45, Dave Cramer wrote: > That's what I thought too, but pg should give him an error if he is > asking for the 28th month. > > Dave > > Scott Marlowe wrote: > > >On Tue, 2005-03-08 at 15:51, Lennie De Villiers wrote: > > > > > >>Hi, > >> > >> > >>I'm using Java together with PostGreSQL database. > >> > >> > >> > >>I'm having a problem with a date query, when I write: > >> > >> > >> > >>select sRescheduleDate, * from tbl_leads > >> > >>where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= > >>'28/01/2005' > >> > >>and bRescheduleCall = true > >> > >> > >> > >>the query also return the values for "01/04/2005" where it should only > >>return those between that date range. > >> > >> > > > >Sounds like your month and day are not where you think they are. > > > >http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT > > > >---------------------------(end of broadcast)--------------------------- > >TIP 8: explain analyze is your friend > > > > > > > >
This is why I almost never, no make that never try to set a timestamp or a date parameter in a java query without using prepared statements let the driver work out the details... Dave Scott Marlowe wrote: >You'd think that as the guy who'd pissed and moaned until the old >behaviour was changed I would have thought of that. but no... > >Too much going on today. I bet it's a text field. > >On Thu, 2005-03-10 at 14:45, Dave Cramer wrote: > > >>That's what I thought too, but pg should give him an error if he is >>asking for the 28th month. >> >>Dave >> >>Scott Marlowe wrote: >> >> >> >>>On Tue, 2005-03-08 at 15:51, Lennie De Villiers wrote: >>> >>> >>> >>> >>>>Hi, >>>> >>>> >>>>I'm using Java together with PostGreSQL database. >>>> >>>> >>>> >>>>I'm having a problem with a date query, when I write: >>>> >>>> >>>> >>>>select sRescheduleDate, * from tbl_leads >>>> >>>>where sRescheduleDate >= '01/01/2005' AND sRescheduleDate <= >>>>'28/01/2005' >>>> >>>>and bRescheduleCall = true >>>> >>>> >>>> >>>>the query also return the values for "01/04/2005" where it should only >>>>return those between that date range. >>>> >>>> >>>> >>>> >>>Sounds like your month and day are not where you think they are. >>> >>>http://www.postgresql.org/docs/7.4/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 8: explain analyze is your friend >>> >>> >>> >>> >>> >>> > > > > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561