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

SELECT
derived_tsoil_fifteen_min_stacked.time2::date,
extract('hour' FROM derived_tsoil_fifteen_min_stacked.time2),
data_key.plot,
data_key.variable_name,
AVG(derived_tsoil_fifteen_min_stacked.value)
FROM
data_key,
derived_tsoil_fifteen_min_stacked
WHERE
data_key.variable_channel =3D derived_tsoil_fifteen_min_stacked.variable AN=
D
data_key.block_name =3D derived_tsoil_fifteen_min_stacked.block_name
AND data_key.plot =3D 'a2'
AND derived_tsoil_fifteen_min_stacked.time2::date =3D '2010-07-07'::date
AND derived_tsoil_fifteen_min_stacked.variable =3D 'tsoil_avg1_sc'
GROUP BY
derived_tsoil_fifteen_min_stacked.time2::date,
extract('hour' FROM derived_tsoil_fifteen_min_stacked.time2),
data_key.variable_name,
data_key.plot
ORDER BY 1

Sent from my Windows Phone
------------------------------
From: Kirk Wythers
Sent: 05/02/2013 04:40
To: Misa Simic
Cc: Kirk Wythers; pgsql-general@postgresql.org
Subject: Re: [GENERAL] partial time stamp query


On Feb 4, 2013, at 7:03 PM, Misa Simic <misa.simic@gmail.com> wrote:

Select time2::date, extract('hour' from time2), AVG(avg) from tablename
group by time2::date, extract('hour' from time2)


Thanks Misa,

But this gives the same result as the way I was using date_trunc (not
GROUPING BY the hour portion of the timestamp, or in this case the re-cast
date). I have simplified the query, as much as I can, and it is below:

--COPY (
SELECT
derived_tsoil_fifteen_min_stacked.time2::date,
extract('hour' FROM derived_tsoil_fifteen_min_stacked.time2),
data_key.plot,
data_key.variable_name,
AVG(derived_tsoil_fifteen_min_stacked.value)
FROM
data_key,
derived_tsoil_fifteen_min_stacked
WHERE
data_key.variable_channel =3D derived_tsoil_fifteen_min_stacked.variable AN=
D
data_key.block_name =3D derived_tsoil_fifteen_min_stacked.block_name
AND data_key.plot =3D 'a2'
AND derived_tsoil_fifteen_min_stacked.time2::date =3D '2010-07-07'::date
AND derived_tsoil_fifteen_min_stacked.variable =3D 'tsoil_avg1_sc'
GROUP BY
derived_tsoil_fifteen_min_stacked.time2::date,
extract('hour' FROM derived_tsoil_fifteen_min_stacked.time2),
derived_tsoil_fifteen_min_stacked.time2,
data_key.variable_name,
data_key.plot
ORDER BY
derived_tsoil_fifteen_min_stacked.time2
--) TO '/tmp/derived_tsoil_hourly.csv' WITH CSV HEADER
;

This query returns 96 records (again, one for each 15 minute interval in
the 24 hour day).

2010-07-07 0 a2 tsoil_sc 21.0599994659424
2010-07-07 0 a2 tsoil_sc 20.9599990844727
2010-07-07 0 a2 tsoil_sc 20.8799991607666
2010-07-07 0 a2 tsoil_sc 20.7999992370605
2010-07-07 1 a2 tsoil_sc 20.7199993133545
2010-07-07 1 a2 tsoil_sc 20.6399993896484
2010-07-07 1 a2 tsoil_sc 20.5499992370605
2010-07-07 1 a2 tsoil_sc 20.4699993133545
2010-07-07 2 a2 tsoil_sc 20.3899993896484
2010-07-07 2 a2 tsoil_sc 20.3099994659424
2010-07-07 2 a2 tsoil_sc 20.25
2010-07-07 2 a2 tsoil_sc 20.1599998474121
2010-07-07 3 a2 tsoil_sc 20.1000003814697
2010-07-07 3 a2 tsoil_sc 20.0100002288818
2010-07-07 3 a2 tsoil_sc 19.9400005340576
2010-07-07 3 a2 tsoil_sc 19.8600006103516
2010-07-07 4 a2 tsoil_sc 19.7700004577637
2010-07-07 4 a2 tsoil_sc 19.7199993133545
2010-07-07 4 a2 tsoil_sc 19.6499996185303
2010-07-07 4 a2 tsoil_sc 19.5599994659424
etc=E2=80=A6.

Could there be anything in the JOIN part of this query that is causing
problems? I'm really grasping at straws now!

Thanks again,

Kirk




On Monday, February 4, 2013, Kirk Wythers wrote:

> Hi Brent,
>
> Nice to hear from you. I hope your world is good.
>
> On Feb 4, 2013, at 2:14 PM, Brent Wood <Brent.Wood@niwa.co.nz<javascript:=
;>>
> wrote:
>
> > Hi Kirk,
> >
> > We have a (near) real time data database for instrument observations
> from our research vessels. All observations (summarised to one minute
> intervals - the actual raw data is in netCDF, this database makes for
> easier access & meets most users needs) go into a single table, with othe=
r
> tables for metadata about platforms, instruments, etc. Now approaching 35=
0m
> records, so reasonably substantial.
> >
> > Underlying optimisations include
> >
> > partitioned readings table, with a separate partition for each year (no=
w
> 23 years)
> > clustered index on timestamp for the previous years partitions.
> > largeish filesystem block size - tested to work well with the clustered
> index & small size records)
> >
> > These generally make a big difference to performance. To address one
> issue, much like yours, where some users want hourly data for a year, som=
e
> want daily data for 10 years & some want 1 minute data for the last month
> (& some, no doubt, want one minute data for 20+ years!) I introduced an
> integer column called timer. This value is set according to the time (not
> date) of each record.
>
> Very similar to what I need to do. Our main table consists of records tha=
t
> have been standardized to 15 minute timestamps. Here is a simplified exam=
ple
>
> record  timestamp               variable                value
> 1               12:00:00                temp            12.6
> 2               12:15:00                temp            12.3
> 3               12:30:00                temp            11.7
> 4               12:45:00                temp            12.3
> 5               13:00:00                temp            13.9
> 6               13:15:00                temp            12.5
> 7               13.30:00                temp            13.7
> 8               13:45:00                temp            12.0
>
> You are exactly right, some people will want the original 15 minute
> version, some people will want these summarized to hourly data, and other=
s
> will want these summarized to daily data. Still others may be satisfied
> with monthly summaries.
>
> >
> > Along the lines of (from memory) :an even no of minutes after the hour
> is 2, 5 minutes is 4, 10 minutes is 8, 15 minute is 16, 30 minutes is 32,
> 60 minutes is 64, 6 hourly is 128, 12:00 AM is 256 & 12:00PM is 512.   Wh=
en
> any timestamp is in more than one category (eg: 12:00 is all of even, 5,
> 15m 30m 60 minutes), the timer value is set to the largest appropriate on=
e.
>
> I'm not quite following. In my case, if I want hourly data, I'd be lookin=
g
> for=E2=80=A6
>
> record  timestamp               variable                value
> 1               12:00:00                temp            12.225
> 2               13:00:00                temp            13.025
>
> Are you saying that I could use an approach that WHILE statement?
> Something like:
>
> WHILE data_truc('hour', timestamp) =3D 12:00:00, then calulate AVG(value)=
?
>
> >
> > So a request for:
> > 1 minute data is select from table;
> > 2 minute data is select from table where timer >=3D2 and timer !=3D15 a=
nd
> timer !=3D4;
> > hourly data is select from table where timer >=3D64 and timer !=3D 15 a=
nd
> timer !=3D 4;
> > etc
> >
> > 5 & 15 minute add a bit of complexity, but we gave the users what they
> wanted. This has worked well for years now, & we have an internal web
> (mapserver/openlayers based) application allowing users to visualise &
> download their selected data - they choose from an interval pick list & t=
he
> SQL is hidden. Some extra enhancements are the automatic collation of lat=
 &
> lon gps readings into a Postgis point for each reading record, & the
> automatic aggregation of daily points into daily track lines, so the trac=
k
> for any selected set of dates can easily be displayed on a map (the
> platforms are mobile vessels - not fixed sites)
> >
> > You might adapt some of these ideas for your use case?
> >
> > Cheers
> >
> > Brent Wood
> >
> > Programme leader: Environmental Information Delivery
> > NIWA
> > DDI:  +64 (4) 3860529
> > ________________________________________
> > From: pgsql-general-owner@postgresql.org [
> pgsql-general-owner@postgresql.org] on behalf of Kirk Wythers [
> wythe001@umn.edu]
> > Sent: Tuesday, February 05, 2013 5:58 AM
> > To: pgsql-general@postgresql.org
> > Subject: Fwd: [GENERAL] partial time stamp query
> >
> > 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
> >
> > 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 record=
s.
> >
> > Perhaps date_trunk only works for the timestamp?
> >
> >
> >
> > On Feb 4, 2013, at 8:50 AM, Misa Simic <misa.simic@gmail.com<mailto:
> misa.simic@gmail.com>> wrote:
> >
> > WHERE derived_tsoil_fifteen_min_stacked.time2::date =3D '2010-07-07'::d=
ate
> >
> > 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:
> >
> > 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=E2=80=A6
> >
> > If I wanted all records from july 7th 2010, I would expect 4 x 24 =3D 9=
6
> records per day.
> >
> > I have tried the '=3D' operator, like this
> >
> > WHERE derived_tsoil_fifteen_min_stacked.time2 =3D '2010-07-07*'
> >
> > but that grabs nothing, and using the '~' operator grabs everything wit=
h
> a 2010 or 07 in it=E2=80=A6 in other word