Re: partial time stamp query - Mailing list pgsql-general

Thanks. That worked great! Now I am trying to aggregate these same =
fifteen minute to hourly. I have tried using date_trunk:

date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),

but date_truck only seems to aggriage the timestamp. I thought I could =
use=20

AVG(derived_tsoil_fifteen_min_stacked.value)

in combination with date_trunk, but I still get 15 minute values, not =
the hourly average from the four 15 minute records.

rowid                            date_truck        =
        time2                site    canopy    plot    =
variable    name            value    avg
2010-07-07_00:00:00_b4warm_a    2010-07-07 00:00:00    1    =
2010-07-07 00:00:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    21.06    21.0599994659424
2010-07-07_00:15:00_b4warm_a    2010-07-07 00:00:00    1    =
2010-07-07 00:15:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    20.96    20.9599990844727
2010-07-07_00:30:00_b4warm_a    2010-07-07 00:00:00    1    =
2010-07-07 00:30:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    20.88    20.8799991607666
2010-07-07_00:45:00_b4warm_a    2010-07-07 00:00:00    1    =
2010-07-07 00:45:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    20.8    20.7999992370605
2010-07-07_01:00:00_b4warm_a    2010-07-07 01:00:00    1    =
2010-07-07 01:00:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    20.72    20.7199993133545
2010-07-07_01:15:00_b4warm_a    2010-07-07 01:00:00    1    =
2010-07-07 01:15:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    20.64    20.6399993896484
2010-07-07_01:30:00_b4warm_a    2010-07-07 01:00:00    1    =
2010-07-07 01:30:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    20.55    20.5499992370605
2010-07-07_01:45:00_b4warm_a    2010-07-07 01:00:00    1    =
2010-07-07 01:45:00    cfc    closed    a2    tsoil_sc    =
tsoil_avg1_sc    20.47    20.4699993133545

I was tying to get two records out of this set, with the 'avg" column =
representing the mean of the first and last four of each 15 minute =
records.=20

Perhaps date_trunk only works for the timestamp?=20



On Feb 4, 2013, at 8:50 AM, Misa Simic <misa.simic@gmail.com> wrote:

> WHERE derived_tsoil_fifteen_min_stacked.time2::date =3D =
'2010-07-07'::date
>=20
> On Monday, February 4, 2013, Kirk Wythers wrote:
> I am trying to write a query that grabs one particular day from a =
timestamp column. The data are ordered in 15 minute chunks like this:
>=20
> 2010-07-07 12:45:00
> 2010-07-07 13:00:00
> 2010-07-07 13:15:00
> 2010-07-07 13:30:00
> etc=85
>=20
> If I wanted all records from july 7th 2010, I would expect 4 x 24 =3D =
96 records per day.
>=20
> I have tried the '=3D' operator, like this
>=20
> WHERE derived_tsoil_fifteen_min_stacked.time2 =3D '2010-07-07*'
>=20
> but that grabs nothing, and using the '~' operator grabs everything =
with a 2010 or 07 in it=85 in other words all days from July of 2010.
>=20
> Any suggestions would be much appreciated.
>=20
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general