Thread: Dates and NULL's`
Hi, Maybe this is a dumb question but if I have a date field that contains a NULL will it show up when I ask for a where date range for the same date field. Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date With the above where will the NULL's be selected???? I ask because I was always told that a NULL matches everything and nothing! Johnf
On Tue, May 10, 2011 at 9:48 AM, John Fabiani <johnf@jfcomputer.com> wrote: > Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date > With the above where will the NULL's be selected???? Here is what I get when I try: spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= '2011-04-30'::DATE;?column? ---------- (1 row) spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= '2011-04-30'::DATE) IS TRUE;?column? ----------f (1 row) spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= '2011-04-30'::DATE) IS FALSE;?column? ----------f (1 row) spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= '2011-04-30'::DATE) IS UNKNOWN;?column? ----------t (1 row) -- Regards, Richard Broersma Jr.
On Tuesday, May 10, 2011 10:16:21 am Richard Broersma wrote: > On Tue, May 10, 2011 at 9:48 AM, John Fabiani <johnf@jfcomputer.com> wrote: > > Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date > > With the above where will the NULL's be selected???? > > Here is what I get when I try: > > spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE; > ?column? > ---------- > > (1 row) > > > spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE) IS TRUE; > ?column? > ---------- > f > (1 row) > > > spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE) IS FALSE; > ?column? > ---------- > f > (1 row) > > > spi=> SELECT (NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > '2011-04-30'::DATE) IS UNKNOWN; > ?column? > ---------- > t > (1 row) Sorry Richard, I do not understand. It looks like you are saying the NULLS will be returned too Johnf
On Tue, May 10, 2011 at 10:24 AM, John Fabiani <johnf@jfcomputer.com> wrote: >> spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= >> '2011-04-30'::DATE; >> ?column? >> ---------- >> >> (1 row) > It looks like you are saying the NULLS will be returned too The WHERE clause will only return rows is the arguments all evaluate to TRUE. No rows will be returned for rows that cause the WHERE clause to evaluate to a NULL value. -- Regards, Richard Broersma Jr.
On Tuesday, May 10, 2011 10:30:58 am Richard Broersma wrote: > On Tue, May 10, 2011 at 10:24 AM, John Fabiani <johnf@jfcomputer.com> wrote: > >> spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > >> '2011-04-30'::DATE; > >> ?column? > >> ---------- > >> > >> (1 row) > > > > It looks like you are saying the NULLS will be returned too > > The WHERE clause will only return rows is the arguments all evaluate > to TRUE. No rows will be returned for rows that cause the WHERE > clause to evaluate to a NULL value. That is what I have seen (so far). But I knew I should ask the question anyway of suffer later. Johnf
> if I have a date field that contains a NULL > will it show up when I ask for a where date range for the same date field. > > Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date > > With the above where will the NULL's be selected???? > > I ask because I was always told that a NULL matches everything and nothing! I think the answer is no. when mydate is null, record will not be returned. e.g., select 'abc' where (null::date >='2011-01-01'::date) ; 0 rows returned. Emi
On May 10, 2011, at 9:48 AM, John Fabiani wrote: > Hi, > Maybe this is a dumb question but if I have a date field that contains a NULL > will it show up when I ask for a where date range for the same date field. > > Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date > > With the above where will the NULL's be selected???? > > I ask because I was always told that a NULL matches everything and nothing! > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql The NULLs will not be included. NULL matches nothing, not even itself. Brent D.
>>> Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date >>> With the above where will the NULL's be selected???? >> >> Here is what I get when I try: >> >> spi=> SELECT NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<= >> '2011-04-30'::DATE; >> ?column? >> ---------- >> >> (1 row) >> >> >> spi=> SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<= >> '2011-04-30'::DATE) IS TRUE; >> ?column? >> ---------- >> f >> (1 row) >> >> >> spi=> SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<= >> '2011-04-30'::DATE) IS FALSE; >> ?column? >> ---------- >> f >> (1 row) >> >> >> spi=> SELECT (NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<= >> '2011-04-30'::DATE) IS UNKNOWN; >> ?column? >> ---------- >> t >> (1 row) > Sorry Richard, I do not understand. > > It looks like you are saying the NULLS will be returned too As a summary: ============================================ (1) null:date COMPARE 'real date'::DATE Will always return NUll, so you will not get the record returned at all! (2) examples he provided: . (null:date COMPARE 'real date'::DATE) IS NULL; . (null:date COMPARE 'real date'::DATE) IS UNKNOWN; Null returns true. (3) null is true/false; returns false Emi
If you think of it more like a null is an unknown. two unknowns can never be the same. they are unknown. and a known date can never equal an UNknown date. if the unknown date is equal to a date, then it is known!!!! my head hurts!! Ted --- On Tue, 5/10/11, Brent Dombrowski <brent.dombrowski@gmail.com> wrote: > From: Brent Dombrowski <brent.dombrowski@gmail.com> > Subject: Re: [SQL] Dates and NULL's` > To: "John Fabiani" <johnf@jfcomputer.com> > Cc: pgsql-sql@postgresql.org > Date: Tuesday, May 10, 2011, 4:33 PM > On May 10, 2011, at 9:48 AM, John > Fabiani wrote: > > > Hi, > > Maybe this is a dumb question but if I have a date > field that contains a NULL > > will it show up when I ask for a where date range for > the same date field. > > > > Where mydate >= "2011/04/01"::date and mydate<= > "2011/04/30"::date > > > > With the above where will the NULL's be selected???? > > > > I ask because I was always told that a NULL matches > everything and nothing! > > > > Johnf > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > > The NULLs will not be included. NULL matches nothing, not > even itself. > > Brent D. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On 05/10/2011 12:48 PM, John Fabiani wrote: > Hi, > Maybe this is a dumb question but if I have a date field that contains a NULL > will it show up when I ask for a where date range for the same date field. > > Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date > > With the above where will the NULL's be selected???? How many question marks does it take to indicate an interrogative? > I ask because I was always told that a NULL matches everything and nothing! That's not a useful viewpoint. The useful point is that NULL matches nothing. It's a simple three-valued logic with NULL standing in for UNKNOWN: <http://www.postgresql.org/docs/9.0/interactive/functions-logical.html> The WHERE clause only selects rows for which the clause evaluates to TRUE. So in the WHERE clause, both NULL > "2011/04/01"::date and NULL <= "2011/04/30"::date will fail, and so would NULL <= "2011/04/01"::date , since none of these evaluates to TRUE. -- Lew Honi soit qui mal y pense. http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg
