Thread: PostGreSQL Date Query?

PostGreSQL Date Query?

From
"Lennie De Villiers"
Date:

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

Re: PostGreSQL Date Query?

From
Scott Marlowe
Date:
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

Re: PostGreSQL Date Query?

From
Diego Gil
Date:
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
>
>
>
>
>


Re: PostGreSQL Date Query?

From
Dave Cramer
Date:
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


Re: PostGreSQL Date Query?

From
Dave Cramer
Date:
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


Re: PostGreSQL Date Query?

From
"Prasanth"
Date:
>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





Re: PostGreSQL Date Query?

From
Scott Marlowe
Date:
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
> >
> >
> >
> >

Re: PostGreSQL Date Query?

From
Dave Cramer
Date:
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