pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDates and NULL's`
10+ messages / 6 participants
[nested] [flat]
* Dates and NULL's`
@ 2011-05-10 16:48 John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:16 ` Re: Dates and NULL's` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:55 ` Re: Dates and NULL's` Lew <noone@lewscanon.com>
2011-05-10 20:16 ` Re: Dates and NULL's` Emi Lu <emilu@encs.concordia.ca>
2011-05-10 20:33 ` Re: Dates and NULL's` Brent Dombrowski <brent.dombrowski@gmail.com>
0 siblings, 4 replies; 10+ messages in thread
From: John Fabiani @ 2011-05-10 16:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
@ 2011-05-10 17:16 ` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:24 ` Re: Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
3 siblings, 1 reply; 10+ messages in thread
From: Richard Broersma @ 2011-05-10 17:16 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:16 ` Re: Dates and NULL's` Richard Broersma <richard.broersma@gmail.com>
@ 2011-05-10 17:24 ` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:30 ` Re: Dates and NULL's` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 20:36 ` Re: Dates and NULL's` Emi Lu <emilu@encs.concordia.ca>
0 siblings, 2 replies; 10+ messages in thread
From: John Fabiani @ 2011-05-10 17:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:16 ` Re: Dates and NULL's` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:24 ` Re: Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
@ 2011-05-10 17:30 ` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:51 ` Re: Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
1 sibling, 1 reply; 10+ messages in thread
From: Richard Broersma @ 2011-05-10 17:30 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:16 ` Re: Dates and NULL's` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:24 ` Re: Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:30 ` Re: Dates and NULL's` Richard Broersma <richard.broersma@gmail.com>
@ 2011-05-10 17:51 ` John Fabiani <johnf@jfcomputer.com>
0 siblings, 0 replies; 10+ messages in thread
From: John Fabiani @ 2011-05-10 17:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:16 ` Re: Dates and NULL's` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:24 ` Re: Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
@ 2011-05-10 20:36 ` Emi Lu <emilu@encs.concordia.ca>
1 sibling, 0 replies; 10+ messages in thread
From: Emi Lu @ 2011-05-10 20:36 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
>>> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
@ 2011-05-10 17:55 ` Lew <noone@lewscanon.com>
3 siblings, 0 replies; 10+ messages in thread
From: Lew @ 2011-05-10 17:55 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
@ 2011-05-10 20:16 ` Emi Lu <emilu@encs.concordia.ca>
3 siblings, 0 replies; 10+ messages in thread
From: Emi Lu @ 2011-05-10 20:16 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
@ 2011-05-10 20:33 ` Brent Dombrowski <brent.dombrowski@gmail.com>
2011-05-10 20:51 ` Re: Dates and NULL's` Theodore Petrosky <tedpet5@yahoo.com>
3 siblings, 1 reply; 10+ messages in thread
From: Brent Dombrowski @ 2011-05-10 20:33 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Dates and NULL's`
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
2011-05-10 20:33 ` Re: Dates and NULL's` Brent Dombrowski <brent.dombrowski@gmail.com>
@ 2011-05-10 20:51 ` Theodore Petrosky <tedpet5@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Theodore Petrosky @ 2011-05-10 20:51 UTC (permalink / raw)
To: John Fabiani <johnf@jfcomputer.com>; Brent Dombrowski <brent.dombrowski@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-05-10 20:51 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-10 16:48 Dates and NULL's` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:16 ` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:24 ` John Fabiani <johnf@jfcomputer.com>
2011-05-10 17:30 ` Richard Broersma <richard.broersma@gmail.com>
2011-05-10 17:51 ` John Fabiani <johnf@jfcomputer.com>
2011-05-10 20:36 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-10 17:55 ` Lew <noone@lewscanon.com>
2011-05-10 20:16 ` Emi Lu <emilu@encs.concordia.ca>
2011-05-10 20:33 ` Brent Dombrowski <brent.dombrowski@gmail.com>
2011-05-10 20:51 ` Theodore Petrosky <tedpet5@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox