Thread: 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.

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 thetableGROUP BY CAST(timestampcol AS DATE);

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
PRIMARYKEY  );
 

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

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

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.

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:302004-02-16 14:27:20.888205+05:302004-02-15
00:00:00+05:302004-02-1500:00:00+05:302004-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:3015-02-2004 | 2004-02-15
00:00:00+05:3016-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