Thread: 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 * **************************************************************************
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
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 * >************************************************************************** >
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 * >************************************************************************** > >
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
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 > >