Re: Fetching record of the day

On Mon, Apr 03, 2000 at 09:03:44PM -0700, Gary Hoffman wrote:
> To the PostGresQL mastermind:
>
>
> So, in general, here's what I want to do:
>
>   select [stuff] from [table] where date(startdate) = date(today)
>
> Several approaches I have tried have all died because of the nulls stored
> in the datetime-type field startdate.
>
> Please suggest a workable solution that you have tested. The best one
> (that works) gets their website listed on my "link of the day". What a
> deal!
>
Gee, Gary, making demands when begging for help is _not_ usually the
way to do things. People might think you're just a lazy bum, trying
to get them to do your work for you.  I'm going to be generous, just
this once, since I always test what I suggest (unless it's trivially
obvious). However, since you're being less than cordial, I'm just going
to post the transcript of what works, instead of my usual transcript
plus analysis of _why_ it works:

reedstrm=> \d test
Table    = test
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| d                                | datetime                         |     8 |
| t                                | text                             |   var |
+----------------------------------+----------------------------------+-------+
reedstrm=> select * from test;
d                           |t
----------------------------+----------
Tue Apr 04 10:04:57 2000 CDT|Today
                            |<- a null
Thu Mar 16 15:10:54 2000 CST|not a null
(3 rows)

reedstrm=> select * from test where date(coalesce(d,'1/1/100')) = date(now());
d                           |t
----------------------------+-----
Tue Apr 04 10:04:57 2000 CDT|Today
(1 row)

reedstrm=>

HTH,
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005