From: Robert Creager <Robert_Creager@LogicalChaos.org>
To: stermic@gw.co.jackson.mo.us
Cc: pgsql-sql@postgresql.org
Subject: Re: max timestamp
Date: Sun, 15 Feb 2004 13:00:25 -0700
Message-ID: <20040215130025.4e74ebf1.Robert_Creager@LogicalChaos.org> (raw)
In-Reply-To: <f7359f44.0402101014.77198f7b@posting.google.com>
References: <f7359f44.0402101014.77198f7b@posting.google.com>
--Multipart_Sun__15_Feb_2004_13_00_25_-0700_=.9v9EXutjLmb9oy
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
When grilled further on (10 Feb 2004 10:14:04 -0800),
stermic@gw.co.jackson.mo.us (Michael Sterling) confessed:
> i'm trying to get the max time stamp, from each day, of a range of
> dates, not just the max time stamp for the complete range dates but
> for each day.
>
Well, one gross and ugly way is:
SELECT MAX( "when" ) FROM readings, (SELECT DATE_TRUNC( 'day', "when" ) AS
period FROM readings GROUP BY period) AS p WHERE DATE_TRUNC( 'day', "when" ) =
p.period GROUP BY p.period;
given the table readings looks something like:
CREATE TABLE readings
(
"when" TIMESTAMP DEFAULT timeofday()::timestamp
NOT NULL PRIMARY KEY
);
I'm curious to see other, 'better' solutions.
Cheers,
Rob
--
12:52:57 up 20:36, 2 users, load average: 2.08, 2.17, 2.18
Linux 2.4.21-0.13_test #60 SMP Sun Dec 7 17:00:02 MST 2003
--Multipart_Sun__15_Feb_2004_13_00_25_-0700_=.9v9EXutjLmb9oy
Content-Type: application/pgp-signature
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)
iEYEARECAAYFAkAvz98ACgkQLQ/DKuwDYzkKPACfXQSPtclOmvynZqNkJni8JaN8
rMkAniko3AXdzIAMNJK1Cq9P/Nk+90+5
=ODLo
-----END PGP SIGNATURE-----
--Multipart_Sun__15_Feb_2004_13_00_25_-0700_=.9v9EXutjLmb9oy--
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Robert_Creager@LogicalChaos.org, stermic@gw.co.jackson.mo.us
Subject: Re: max timestamp
In-Reply-To: <20040215130025.4e74ebf1.Robert_Creager@LogicalChaos.org>
* 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