Re: calculating elapsed times between timestamps

On Mon, 02 Feb 2009 21:36:54 -0500
justin <justin(at)emproshunts(dot)com> wrote:

> rhubbell wrote:
> > (posted on novice too, no idea what difference is between lists)
> >
> > I have been trying to do this and have been unsuccessful so far.
> >
> > I have a table:
> >
> > perf:
> > timestamp = timestamp with time zone
> > timeelapsed = numeric
> > bobble = text
> >
> > timeelapsed records are the time elapsed metric in seconds.
> >
> > e.g. 0.350058078765869
> >
> > Typical scenario is that I'll have multiple entries where timeelapsed
> > is greater than some value and will be greater than that value for
> > some time interval.
> >
> >
> > I want to find the length of those intervals.
> >
> > select timestamp, timeelapsed, bobble from perf where bobble like "pokerflat"
> > and timeelapsed > 0.4;
> >
> > The records returned by that query will have an oldest and newest timestamp for
> > which I would like to calculate the interval.
> >
> I'm not real clear on what you are asking here with oldest and newest
> timestamps and getting the interval, please clarify

time a = etime == .211
time b = etime == .312
time c = etime == .311
time d = etime == .301
time e = etime == .201

select returned all etime < .29

I want to know "time d" - "time b"
"time d" == newest
"time b" == oldest

> > I found lots of examples of doing arithmetic on timestamps but I never saw
> > any extracting data from a table. All the examples I found were using now()
> > or current_date + 3 or the like.
> >
> Just substitute current_date or now() appears in the examples with the
> column name

Ok, will try that.

> > Do aggregate function work on time data?
> >
>
> For date time function go here
> http://www.postgresql.org/docs/8.3/static/functions-datetime.html
>