Re: Fetching record of the day

Gary,
test=> create table abc (startdate datetime, stuff text);
CREATE
test=> insert into abc values ('@01/03/00','http://www.is.com.fj/');
INSERT 7146378 1
test=> insert into abc values (NULL,'http://www.yellowpages.com.fj/');
INSERT 7146379 1
test=> insert into abc values
('@05/04/00','http://www.fiji-online.com.fj/');
INSERT 7146380 1
test=> select * from abc;
startdate |stuff
------------------------------+------------------------------
Wed 01 Mar 00:00:00 2000 FJDST|http://www.is.com.fj/
|http://www.yellowpages.com.fj/
Wed 05 Apr 00:00:00 2000 FJT |http://www.fiji-online.com.fj/
(3 rows)
test=> select stuff from abc
where date_trunc('day',startdate) = date_trunc('day',current_timestamp)
and not null = startdate;
stuff
------------------------------
http://www.fiji-online.com.fj/
(1 row)

Do I win?
John Henderson

-----Original Message-----
From: Gary Hoffman <ghoffman(at)ucsd(dot)edu>
To: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
Date: Tuesday, April 04, 2000 4:51
Subject: [GENERAL] Fetching record of the day

>To the PostGresQL mastermind:
>
>Working with postgresql and SQL in general is a blast. However, I need
>advice on how best to form the following query. I'm sure there are a dozen
>different ways to do it. Please give me your suggestion:
>
>I want to publish a "Link of the Day" on my website, with title,
>description, and URL. I have a field named startdate with type of
>datetime, but not all records have this field filled in. In those cases,
>it contains a null.
>
>I would like to retireve the record (there should be only one) whose field
>"startdate" contains today's date. So I only want to test for date, not
>date&time and I also want to ignore the null records. Of course, if it
>works better, I could store the intended startdate in text format or even
>an integer format, if that works better.
>
>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!
>
>Thanks,
>Gary
>
>**************************************************************************
>* Gary B. Hoffman, Computing Services Manager e-mail: ghoffman(at)ucsd(dot)edu *
>* Graduate School of International Relations and Pacific Studies (IR/PS) *
>* University of California, San Diego (UCSD) voice: (858) 534-1989 *
>* 9500 Gilman Dr., La Jolla, CA 92093-0519 USA fax: (858) 534-3939 *
>**************************************************************************
>
>