X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 20B599DCB8C for ; Sun, 4 Dec 2005 12:29:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04121-06 for ; Sun, 4 Dec 2005 12:29:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web31701.mail.mud.yahoo.com (web31701.mail.mud.yahoo.com [68.142.201.181]) by postgresql.org (Postfix) with SMTP id AEE0C9DCBEA for ; Sun, 4 Dec 2005 12:29:11 -0400 (AST) Received: (qmail 79880 invoked by uid 60001); 4 Dec 2005 16:28:54 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=MIED0NApbb+EtAKjaCjc1vU+UVqvD9LLqMHbmsJQ7XiK7W3RWf1+Cp/2aaFfPFONhbFKRS1+RB1K69elUTK8hXwZQEgyTdtG7MKhxh7vhSzlgZmMnYV1RzOtJYUiOZ+pva7QpmR2vmiAzNSKZJp+yJa9j/DPO0Z9UE2H4OVwEno= ; Message-ID: <20051204162854.79878.qmail@web31701.mail.mud.yahoo.com> Received: from [151.204.106.98] by web31701.mail.mud.yahoo.com via HTTP; Sun, 04 Dec 2005 08:28:54 PST Date: Sun, 4 Dec 2005 08:28:54 -0800 (PST) From: Theodore Petrosky Subject: Re: question using 'between' in a sql query To: Michael Fuhr Cc: postgres SQL In-Reply-To: <20051203181515.GA12719@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.359 required=5 tests=[AWL=-0.120, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.359 X-Spam-Level: X-Archive-Number: 200512/16 X-Sequence-Number: 23558 thanks... as is so often the case I discovered date_trunc('day', insertdate) between '11/20/2005' AND '11/20/2005' also works.... It wasn't until I pressed the send button that I thought of the solution. I have thought of creating a faux 'send' button for my email software... yours is a little easier to read (human).. Thanks again, Ted --- Michael Fuhr wrote: > On Sat, Dec 03, 2005 at 09:54:16AM -0800, Theodore > Petrosky wrote: > > if 'between' is inclusive of the start and end > dates why doesn't > > this query result in all the rows on '11/20/2005'? > > EXPLAIN shows what happens when you compare a date > against a timestamp: > > Filter: ((insertdate >= '2005-11-20 > 00:00:00-08'::timestamp with time zone) > AND (insertdate <= '2005-11-20 > 00:00:00-08'::timestamp with time zone)) > > One way around this is to cast the timestamp column > to date: > > SELECT employeecode > FROM mytable > WHERE insertdate::date BETWEEN '11/20/2005' AND > '11/20/2005'; > > -- > Michael Fuhr > __________________________________ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs