Re: calculating elapsed times between timestamps

On Mon, 2 Feb 2009 18:53:07 -0800
rhubbell <Rhubbell(at)iHubbell(dot)com> wrote:

> 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

Uh, meant 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
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general