Thread: Getting the year from a date column

Getting the year from a date column

From
Lynna Landstreet
Date:
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


Re: Getting the year from a date column

From
"David Busby"
Date:
try:
$sql = "select exhibition_name,to_char('YYYY',start_date) as start_date \
        from exhibitions where exhibition_id = '$exhibition_id'";


----- Original Message -----
From: "Lynna Landstreet" <lynna@gallery44.org>
To: <pgsql-php@postgresql.org>
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


Re: Getting the year from a date column

From
"David Busby"
Date:
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" <lynna@gallery44.org>
To: "David Busby" <busby@pnts.com>
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" <lynna@gallery44.org>
> > 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