Thread: Fetching record of the day

Fetching record of the day

From
ghoffman@ucsd.edu (Gary Hoffman)
Date:
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@ucsd.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 *
**************************************************************************


Re: Fetching record of the day

From
"Ross J. Reedstrom"
Date:
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

Re: Fetching record of the day

From
davidb@vectormath.com
Date:
Hello Gary,

What I would do (everyone on list groans) is store the date as a string in
YYYY-MM-DD
format.  Actually, I always store dates that way.  Should you decide you
want to do that, I have a date validation function I could send you (written
in VB (sorry)).

Good luck
David Boerwinkle

-----Original Message-----
From: Gary Hoffman <ghoffman@ucsd.edu>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Tuesday, April 04, 2000 12:21 AM
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@ucsd.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 *
>**************************************************************************
>


Re: Fetching record of the day

From
"John Henderson"
Date:
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@ucsd.edu>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.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@ucsd.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 *
>**************************************************************************
>
>


startup / shutdown scripts for *BSD?

From
"Bruce Bantos"
Date:
Hello all. Does anyone have a PostgreSQL startup / shutdown script for a
freeBSD machine? I have one for a linux machine, but I am having trouble
converting it to work with the BSD environment. My knowledge of the BSD
platform is very limited. I have frequently seen messages on this list from
users of PostgreSQL on BSD, so if anyone could share a script I would be
very appreciative. Thanks!

-B


RE: startup / shutdown scripts for *BSD?

From
"Chris Knight"
Date:
Howdy,
    Assuming you've installed the FreeBSD package, or built it from the ports
tree:

% cat /usr/local/etc/rc.d/pgsql.sh
#!/bin/sh
[ -d /usr/local/pgsql/lib ] && /sbin/ldconfig -m /usr/local/pgsql/lib

[ -x /usr/local/pgsql/bin/postmaster ] && {
        su -l pgsql -c 'exec /usr/local/pgsql/bin/postmaster -i -S -o -F'
>/usr/local/pgsql/errlog 2>&1
        echo -n ' pgsql'
}

    I haven't bothered with a shutdown script.

Regards,
Chris Knight
Systems Administrator
AIMS Independent Computer Professionals
Tel: +61 3 6334 6664  Fax: +61 3 6331 7032  Mob: +61 419 528 795
Web: http://www.aims.com.au



> -----Original Message-----
> From: pgsql-general-owner@hub.org
> [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Bruce Bantos
> Sent: Wednesday, 5 April 2000 13:41
> To: pgsql-general@postgreSQL.org
> Subject: [GENERAL] startup / shutdown scripts for *BSD?
>
>
> Hello all. Does anyone have a PostgreSQL startup / shutdown
> script for a
> freeBSD machine? I have one for a linux machine, but I am
> having trouble
> converting it to work with the BSD environment. My knowledge
> of the BSD
> platform is very limited. I have frequently seen messages on
> this list from
> users of PostgreSQL on BSD, so if anyone could share a script
> I would be
> very appreciative. Thanks!
>
> -B
>
>