pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmax timestamp
7+ messages / 7 participants
[nested] [flat]
* max timestamp
@ 2004-02-10 18:14 Michael Sterling <stermic@gw.co.jackson.mo.us>
2004-02-10 19:50 ` Re: max timestamp boyd (remove_) tb_moore_@bealenet_.com <HeWillRejoice@yahoo.com>
2004-02-15 19:51 ` Re: max timestamp Bruno Wolff III <bruno@wolff.to>
2004-02-15 20:00 ` Re: max timestamp Robert Creager <Robert_Creager@LogicalChaos.org>
2004-02-15 20:01 ` Re: max timestamp Tomasz Myrta <jasiek@klaster.net>
2004-02-15 20:03 ` Re: max timestamp Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-16 08:57 ` Re: max timestamp Denis <sqllist@coralindia.com>
0 siblings, 6 replies; 7+ messages in thread
From: stermic@gw.co.jackson.mo.us @ 2004-02-10 18:14 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: max timestamp
2004-02-10 18:14 max timestamp Michael Sterling <stermic@gw.co.jackson.mo.us>
@ 2004-02-10 19:50 ` boyd (remove_) tb_moore_@bealenet_.com <HeWillRejoice@yahoo.com>
5 siblings, 0 replies; 7+ messages in thread
From: boyd (remove_) tb_moore_@bealenet_.com @ 2004-02-10 19:50 UTC (permalink / raw)
To: pgsql-sql
Michael Sterling wrote:
> 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.
I don't trust the news client I was using. So will answer again from
the Netscape version; This worked for me:
I have a table, sis, with schema:
Table "sis"
Column | Type | Modifiers
--------+--------------------------+-----------
time | timestamp with time zone |
stat10 | integer |
ipf10 | real |
ipf30 | integer |
select date_part ( 'doy',time ) as jday, max ( time ) from sis group by jday
(doy means day of year, which I called jday)
Boyd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: max timestamp
2004-02-10 18:14 max timestamp Michael Sterling <stermic@gw.co.jackson.mo.us>
@ 2004-02-15 19:51 ` Bruno Wolff III <bruno@wolff.to>
5 siblings, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2004-02-15 19:51 UTC (permalink / raw)
To: Michael Sterling <stermic@gw.co.jackson.mo.us>; +Cc: pgsql-sql
On Tue, Feb 10, 2004 at 10:14:04 -0800,
Michael Sterling <stermic@gw.co.jackson.mo.us> wrote:
> 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.
SELECT DISTINCT ON can probably do what you want. Something like:
SELECT DISTINCT ON (timestamp::DATE) * FROM table ORDER BY timestamp DESC;
This won't produce any records for days where there are no timestamps.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: max timestamp
2004-02-10 18:14 max timestamp Michael Sterling <stermic@gw.co.jackson.mo.us>
@ 2004-02-15 20:00 ` Robert Creager <Robert_Creager@LogicalChaos.org>
5 siblings, 0 replies; 7+ messages in thread
From: Robert Creager @ 2004-02-15 20:00 UTC (permalink / raw)
To: stermic@gw.co.jackson.mo.us; +Cc: pgsql-sql
--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--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: max timestamp
2004-02-10 18:14 max timestamp Michael Sterling <stermic@gw.co.jackson.mo.us>
@ 2004-02-15 20:01 ` Tomasz Myrta <jasiek@klaster.net>
5 siblings, 0 replies; 7+ messages in thread
From: Tomasz Myrta @ 2004-02-15 20:01 UTC (permalink / raw)
To: Michael Sterling <stermic@gw.co.jackson.mo.us>; +Cc: pgsql-sql
Dnia 2004-02-10 19:14, Użytkownik Michael Sterling napisał:
> 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.
select max(some_time)
group by some_time::date
or
select max(some_time)
group by date_trunc('day',some_time)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: max timestamp
2004-02-10 18:14 max timestamp Michael Sterling <stermic@gw.co.jackson.mo.us>
@ 2004-02-15 20:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
5 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2004-02-15 20:03 UTC (permalink / raw)
To: Michael Sterling <stermic@gw.co.jackson.mo.us>; +Cc: pgsql-sql
On Tue, 10 Feb 2004, Michael Sterling wrote:
> 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.
Maybe something like?
SELECT CAST(timestampcol AS DATE), max(timestampcol)
FROM thetable
GROUP BY CAST(timestampcol AS DATE);
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: max timestamp
2004-02-10 18:14 max timestamp Michael Sterling <stermic@gw.co.jackson.mo.us>
@ 2004-02-16 08:57 ` Denis <sqllist@coralindia.com>
5 siblings, 0 replies; 7+ messages in thread
From: Denis @ 2004-02-16 08:57 UTC (permalink / raw)
To: pgsql-sql
Hi Michael,
Try this..
ace=> create table test( mytime timestamp );
CREATE
ace=> insert into test values (now() );
INSERT 1823542 1
ace=> insert into test values (now() );
INSERT 1823543 1
ace=> insert into test values (now()-1);
INSERT 1823544 1
ace=> insert into test values (now()-1);
INSERT 1823545 1
ace=> insert into test values (now()-2);
INSERT 1823546 1
ace=> select * from test;
mytime
----------------------------------
2004-02-16 14:27:15.936368+05:30
2004-02-16 14:27:20.888205+05:30
2004-02-15 00:00:00+05:30
2004-02-15 00:00:00+05:30
2004-02-14 00:00:00+05:30
(5 rows)
ace=> select to_char(mytime,'dd-mm-yyyy'),max(mytime) from test group by 1;
to_char | max
------------+----------------------------------
14-02-2004 | 2004-02-14 00:00:00+05:30
15-02-2004 | 2004-02-15 00:00:00+05:30
16-02-2004 | 2004-02-16 14:27:20.888205+05:30
(3 rows)
HTH
Thanx
Denis
----- Original Message -----
From: "Michael Sterling" <stermic@gw.co.jackson.mo.us>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, February 10, 2004 11:44 PM
Subject: [SQL] max timestamp
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-02-16 08:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-10 18:14 max timestamp Michael Sterling <stermic@gw.co.jackson.mo.us>
2004-02-10 19:50 ` boyd (remove_) tb_moore_@bealenet_.com <HeWillRejoice@yahoo.com>
2004-02-15 19:51 ` Bruno Wolff III <bruno@wolff.to>
2004-02-15 20:00 ` Robert Creager <Robert_Creager@LogicalChaos.org>
2004-02-15 20:01 ` Tomasz Myrta <jasiek@klaster.net>
2004-02-15 20:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-02-16 08:57 ` Denis <sqllist@coralindia.com>
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