Re: calculating elapsed times between timestamps

(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 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.

Do aggregate function work on time data?

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
> 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
> Do aggregate function work on time data?
>

For date time function go here
http://www.postgresql.org/docs/8.3/static/functions-datetime.html

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
>

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

On Mon, Feb 2, 2009 at 5:54 PM, rhubbell <Rhubbell(at)ihubbell(dot)com> wrote:
>
> 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.

The easiest way is to just subtract one timestamp from another... If
you want the difference in seconds, then use extract

select '2009-01-31 12:34'::timestamp - '2009-01-12 15:34'::timestamp;
?column?
------------------
18 days 21:00:00
select extract(epoch from '2009-01-31 12:34'::timestamp - '2009-01-12
15:34'::timestamp);
date_part
-----------
1630800

Does that get you closer to an answer?

On Tue, 3 Feb 2009 02:08:54 -0700
Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Mon, Feb 2, 2009 at 5:54 PM, rhubbell <Rhubbell(at)ihubbell(dot)com> wrote:
> >
> > 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.
>
> The easiest way is to just subtract one timestamp from another... If
> you want the difference in seconds, then use extract
>
> select '2009-01-31 12:34'::timestamp - '2009-01-12 15:34'::timestamp;
> ?column?
> ------------------
> 18 days 21:00:00
> select extract(epoch from '2009-01-31 12:34'::timestamp - '2009-01-12
> 15:34'::timestamp);
> date_part
> -----------
> 1630800
>
> Does that get you closer to an answer?

Yes, thanks. I actually solved in a different way but this helps toward
understanding sql a little better. I think the s in sql is crippling.
I feel like I've traveled back in time whenever I use sql.
>
> --
> 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