X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 02488D1C947 for ; Thu, 24 Jul 2003 07:20:36 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 16122-09 for ; Thu, 24 Jul 2003 04:19:53 -0300 (ADT) Received: from seapdc01.PNTS (ip63.c216.blk1.bel.nwlink.com [209.20.216.63]) by svr1.postgresql.org (Postfix) with ESMTP id AEE00D1C941 for ; Thu, 24 Jul 2003 04:20:33 -0300 (ADT) Received: from busbydev (BUSBY-DEV [10.0.0.17]) by seapdc01.PNTS with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id PRCQ8VAZ; Thu, 24 Jul 2003 00:36:39 -0700 Message-ID: <000a01c351b3$3f686d30$1100000a@busbydev> Reply-To: "David Busby" From: "David Busby" To: "Lynna Landstreet" , References: Subject: Re: Getting the year from a date column Date: Thu, 24 Jul 2003 00:14:39 -0700 Organization: Pacific Northwest Ticket Service MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/45 X-Sequence-Number: 2157 The "\" character is commonly used as a line continuation character. The PostgreSQL manual (which you should read) shows to_char working like this (on this page: http://www.postgresql.org/docs/7.3/static/functions-formatting.html) : to_char([data],[format]). I guess I had the parameters reversed, but AFAICT to_char doesn't work with date anyways. So now the PHP manual shows many useful date functions (http://www.php.net/manual/en/ref.datetime.php). Of note is date() (http://www.php.net/manual/en/function.date.php) and strtotime() (http://www.php.net/manual/en/function.strtotime.php). So perhaps this would work: $x=date('r', strtotime(pg_result($exhibition_info, 0, 'start_date'))); /B ----- Original Message ----- From: "Lynna Landstreet" To: "David Busby" Sent: Wednesday, July 23, 2003 15:46 Subject: Re: [PHP] Getting the year from a date column > on 7/23/03 5:59 PM, David Busby at busby@pnts.com wrote: > > > try: > > $sql = "select exhibition_name,to_char('YYYY',start_date) as start_date \ > > from exhibitions where exhibition_id = '$exhibition_id'"; > > That got me a whole slew of error messages - originally it choked on the \, > and when I took that out I got this: > > Warning: PostgreSQL query failed: ERROR: Function 'to_char(unknown, date)' > does not exist Unable to identify a function that satisfies the given > argument types You may need to add explicit typecasts in /[path to my home > directory]/db/artist_exhibitions.php on line 104 > > Followed by about five other error messages, but those seemed to stem from > this query failing. Not sure what that first message means... > > > Lynna > > > > ----- Original Message ----- > > From: "Lynna Landstreet" > > To: < > > > Sent: Wednesday, July 23, 2003 14:49 > > Subject: [PHP] Getting the year from a date column > > > > > >> I'm having a bit of trouble with one part of the PHP front end of the art > >> gallery database I'm working on - specifically, extracting the year from > > the > >> start date of an exhibition. I'm trying to print a list of exhibitions on > >> the artist info pages, with the name, gallery space and year for each one, > >> and everything's working except the year part. > >> > >> Here's what I have. First, near the beginning of the PHP portion of the > >> page: > >> > >> $exh_query = "SELECT exhibition_name, start_date > >> FROM exhibitions WHERE exhibition_id = '$exhibition_id'"; > >> $exhibition_info = pg_exec($db, $exh_query); > >> > >> Then further down, where I want the year to appear in the exhibition list: > >> > >> $date = getdate (pg_result($exhibition_info, 0, 'start_date')); > >> $year = $date['year']; > >> echo $year; > >> > >> (The 0 for row is because the way the code is structured, $exhibition_info > >> only contains one row.) But I'm not sure if getdate() is the right > > function > >> to be using. It was the only function I could find in the PHP manual that > >> was capable of breaking out the individual components of a date (day, > > month, > >> year, etc.), but it seems to be ignoring the actual date in the query > >> results and returning '1969' for everything. > >> > >> Any idea what I'm doing wrong? Is there a different function I should be > >> using instead for this? BTW, I'm using PHP 4.1 (and PostgreSQL 7.2), thus > >> the old-style pg function names (pg_result(), etc.). > >> > >> Thanks, > >> > >> Lynna > >> -- > >> Resource Centre Database Coordinator > >> Gallery 44 > >> www.gallery44.org > >> > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 9: the planner will ignore your desire to choose an index scan if your > >> joining column's datatypes do not match > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > > -- > Resource Centre Database Coordinator > Gallery 44 > www.gallery44.org