Search Postgresql Archives

Re: partial time stamp query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

Perhaps date_trunk only works for the timestamp? 



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

WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'::date

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…

If I wanted all records from july 7th 2010, I would expect 4 x 24 = 96 records per day.

I have tried the '=' operator, like this

WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*'

but that grabs nothing, and using the '~' operator grabs everything with a 2010 or 07 in it… in other words all days from July of 2010.

Any suggestions would be much appreciated.

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux