From: Nick Barr <nicky@chuckie.co.uk>
To: Michael Hanna <zen@hwcn.org>
To: pgsql-php@postgresql.org
Subject: Re: query, display questions
Date: Thu, 3 Jul 2003 16:39:21 +0100
Message-ID: <003f01c34179$461f23c0$2802a8c0@webbased10> (raw)
References: <B46415C5-AD69-11D7-80D3-00039308EB2C@hwcn.org>
Michael,
----------------------------------------
$sql = "SELECT EXTRACT(DAY FROM posted) FROM healthnotes GROUP BY
EXTRACT(DAY FROM posted)";
Then loop through each of these results and do a further query
$sql = "SELECT * FROM healthnotes t1 WHERE EXTRACT(DAY FROM posted) = " .
$day . " ORDER BY posted ASC";
And print out the results for each of these.
----------------------------------------
Bascially the EXTRACT function allows you to pull out different parts of the
timestamp. See the following for a better description of the different bits
that can be pulled out.
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-da
tetime.html#FUNCTIONS-DATETIME-EXTRACT
If you want to display the timestamp in PHP you could convert it to an
EPOCH, then use the php date() function to change the output format. i.e.
change the second sql query above to something like:
$sql = "SELECT *, EXTRACT(EPOCH FROM posted) AS note_epoch FROM healthnotes
t1 WHERE EXTRACT(DAY FROM posted) = " . $day . " ORDER BY posted ASC";
then in PHP feed the note_epoch data into date() like the following:
$timestamp_string = date("d/m/Y H:ia", $note_epoch);
Again there are loads of different output formats if you want, more details
can be found here:
http://www.php.net/manual/en/function.date.php
Hope that helps. If I havent quite understood you then please explain a bit
further.
Nick
----- Original Message -----
From: "Michael Hanna" <zen@hwcn.org>
To: <pgsql-php@postgresql.org>
Sent: Thursday, July 03, 2003 4:19 PM
Subject: [PHP] query, display questions
> I have a table:
>
> michael=# \d healthnotes
> Table "public.healthnotes"
> Column | Type | Modifiers
> --------+--------------------------
> +-------------------------------------------------------------
> posted | timestamp with time zone | not null default
> ('now'::text)::timestamp(6) with time zone
> notes | text |
> Indexes: healthnotes_pkey primary key btree (posted)
>
> Often there are multiple entries per day. I want to display the day
> once, with all the entries on that day.
>
> So do I select * from notes and group by date, then write a nested
> for-loop in php that ignores the extra timestamp items? Seems a little
> inelegant. Or can I select distinct timestamps somehow after converting
> them to dates?
>
> Not sure how to go about this.
>
> Another question: I want to echo in an html page the timestamp and the
> note after it is entered. What php-pgsql commands do this?
>
> Michael
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: nicky@chuckie.co.uk, zen@hwcn.org
Subject: Re: query, display questions
In-Reply-To: <003f01c34179$461f23c0$2802a8c0@webbased10>
References: <B46415C5-AD69-11D7-80D3-00039308EB2C@hwcn.org> <003f01c34179$461f23c0$2802a8c0@webbased10>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox