Databases
StackOverflow2013
Postsdbo
POAverage function in PostgreSQL with 5 minutes tolerance
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAverage function in PostgreSQL with 5 minutes tolerance
    primarykey
    data
    text
    <p>I have a temporal database and I want to aggregate by the average function every HH instantaneous values with 5 minute before and 5 minute after. Frequency of data samplying is 2 minutes and the data includes timestamps and temperature columns. First of all, the code should extract the instantaneous values from 00, 01, 02, 03, ... 24 and then apply 2 observation of before and 2 observations of after the instantaneous hours. Moreover, in db sometimes minutes are odd and sometimes they are even. So, we have both HH-1:59 and HH:00 . The average value obtains from mean of 5 values. The format of the date_time column is timestamps. Moreover the code should can handle multiple days data.</p> <p>Sample data:</p> <pre><code> dt ambtemp "2007-09-29 00:11:09+02";-1.87 "2007-09-29 00:13:09+02";-1.87 "2007-09-29 00:15:09+02";-1.8 "2007-09-29 00:17:09+02";-1.64 "2007-09-29 00:19:09+02";-1.6 "2007-09-29 00:21:09+02";-1.9 "2007-09-29 00:23:09+02";-2.08 "2007-09-29 00:25:09+02";-1.94 "2007-09-29 00:27:09+02";-2.12 "2007-09-29 00:29:09+02";-1.87 "2007-09-29 00:31:09+02";-2.18 "2007-09-29 00:33:09+02";-1.98 "2007-09-29 00:35:09+02";-1.73 "2007-09-29 00:37:09+02";-1.84 "2007-09-29 00:39:09+02";-2.04 "2007-09-29 00:41:09+02";-1.86 "2007-09-29 00:43:09+02";-1.94 "2007-09-29 00:45:09+02";-1.77 "2007-09-29 00:47:09+02";-1.78 "2007-09-29 00:49:09+02";-1.5 "2007-09-29 00:51:09+02";-1.46 "2007-09-29 00:53:09+02";-1.72 "2007-09-29 00:55:09+02";-1.67 "2007-09-29 00:57:09+02";-1.56 "2007-09-29 00:59:09+02";-1.69 "2007-09-29 01:01:09+02";-1.97 "2007-09-29 01:03:09+02";-1.79 "2007-09-29 01:05:09+02";-1.79 "2007-09-29 01:07:09+02";-1.84 "2007-09-29 01:09:09+02";-1.91 "2007-09-29 01:11:09+02";-1.87 "2007-09-29 01:13:09+02";-1.98 "2007-09-29 01:15:09+02";-1.83 "2007-09-29 01:17:09+02";-1.88 "2007-09-29 01:19:09+02";-1.88 "2007-09-29 01:21:09+02";-1.78 "2007-09-29 01:23:09+02";-1.78 "2007-09-29 01:25:09+02";-1.66 "2007-09-29 01:27:09+02";-1.7 "2007-09-29 01:29:09+02";-1.46 "2007-09-29 01:31:09+02";-1.36 "2007-09-29 01:33:09+02";-1.4 "2007-09-29 01:35:09+02";-1.34 "2007-09-29 01:37:09+02";-1.34 "2007-09-29 01:39:09+02";-1.3 "2007-09-29 01:41:09+02";-1.36 "2007-09-29 01:43:09+02";-1.4 "2007-09-29 01:45:09+02";-1.43 "2007-09-29 01:47:09+02";-1.38 "2007-09-29 01:49:09+02";-1.4 "2007-09-29 01:51:09+02";-1.42 "2007-09-29 01:53:09+02";-1.47 "2007-09-29 01:55:09+02";-1.66 "2007-09-29 01:57:09+02";-1.84 "2007-09-29 01:59:09+02";-1.92 "2007-09-29 02:01:09+02";-1.88 "2007-09-29 02:03:09+02";-2.11 "2007-09-29 02:05:09+02";-1.91 "2007-09-29 02:07:09+02";-2.04 "2007-09-29 02:09:09+02";-1.94 "2007-09-29 02:11:09+02";-1.92 "2007-09-29 02:13:09+02";-1.8 "2007-09-29 02:15:09+02";-1.74 "2007-09-29 02:17:09+02";-1.74 "2007-09-29 02:19:09+02";-1.76 "2007-09-29 02:21:09+02";-1.74 "2007-09-29 02:23:09+02";-1.8 "2007-09-29 02:25:09+02";-1.8 "2007-09-29 02:27:09+02";-1.8 "2007-09-29 02:29:09+02";-1.82 "2007-09-29 02:31:09+02";-1.9 "2007-09-29 02:33:09+02";-1.93 "2007-09-29 02:35:09+02";-2.06 "2007-09-29 02:37:09+02";-2.08 "2007-09-29 02:39:09+02";-1.95 "2007-09-29 02:41:09+02";-1.98 "2007-09-29 02:43:09+02";-2.32 "2007-09-29 02:45:09+02";-1.86 "2007-09-29 02:47:09+02";-1.97 "2007-09-29 02:49:09+02";-1.64 "2007-09-29 02:51:09+02";-2 "2007-09-29 02:53:09+02";-1.48 "2007-09-29 02:55:09+02";-1.74 "2007-09-29 02:57:09+02";-1.85 "2007-09-29 02:59:09+02";-1.82 "2007-09-29 03:01:09+02";-1.82 "2007-09-29 03:03:09+02";-1.92 "2007-09-29 03:05:09+02";-1.8 "2007-09-29 03:07:09+02";-1.54 "2007-09-29 03:09:09+02";-1.36 "2007-09-29 03:11:09+02";-1.5 "2007-09-29 03:13:09+02";-1.59 "2007-09-29 03:15:09+02";-1.6 "2007-09-29 03:17:09+02";-1.58 "2007-09-29 03:19:09+02";-1.81 "2007-09-29 03:21:09+02";-2.16 "2007-09-29 03:23:09+02";-1.97 "2007-09-29 03:25:09+02";-1.94 "2007-09-29 03:27:09+02";-2.29 "2007-09-29 03:29:09+02";-2.46 "2007-09-29 03:31:09+02";-2.42 "2007-09-29 03:33:09+02";-2.34 "2007-09-29 03:35:09+02";-2.38 "2007-09-29 03:37:09+02";-2.44 "2007-09-29 03:39:09+02";-2.28 "2007-09-29 03:41:09+02";-2.24 "2007-09-29 03:43:09+02";-2.26 "2007-09-29 03:45:09+02";-2.05 "2007-09-29 03:47:09+02";-2.38 "2007-09-29 03:49:09+02";-2.24 "2007-09-29 03:51:09+02";-2.46 "2007-09-29 03:53:09+02";-2.22 "2007-09-29 03:55:09+02";-2.24 "2007-09-29 03:57:09+02";-2.2 "2007-09-29 03:59:09+02";-2.28 "2007-09-29 04:01:09+02";-1.86 "2007-09-29 04:03:09+02";-1.9 "2007-09-29 04:05:09+02";-1.98 "2007-09-29 04:07:09+02";-1.99 "2007-09-29 04:09:09+02";-2 "2007-09-29 04:11:09+02";-1.98 "2007-09-29 04:13:09+02";-2.13 "2007-09-29 04:15:09+02";-2.11 "2007-09-29 04:17:09+02";-2.1 "2007-09-29 04:19:09+02";-2.28 "2007-09-29 04:21:09+02";-2.28 "2007-09-29 04:23:09+02";-2.44 "2007-09-29 04:25:09+02";-2.44 "2007-09-29 04:27:08+02";-2.2 "2007-09-29 04:29:08+02";-2.2 "2007-09-29 04:31:08+02";-2.11 "2007-09-29 04:31:09+02";-2.11 "2007-09-29 04:33:09+02";-2.3 "2007-09-29 04:35:09+02";-2 "2007-09-29 04:37:08+02";-1.7 "2007-09-29 04:39:08+02";-1.43 "2007-09-29 04:41:08+02";-1.55 "2007-09-29 04:43:09+02";-1.43 "2007-09-29 04:45:09+02";-1.59 "2007-09-29 04:47:08+02";-1.58 "2007-09-29 04:49:08+02";-1.52 "2007-09-29 04:51:08+02";-1.5 "2007-09-29 04:53:09+02";-1.4 "2007-09-29 04:55:08+02";-1.46 "2007-09-29 04:55:09+02";-1.46 "2007-09-29 04:57:08+02";-1.5 "2007-09-29 04:59:08+02";-1.52 "2007-09-29 05:01:08+02";-1.64 "2007-09-29 05:03:08+02";-1.7 "2007-09-29 05:05:08+02";-1.7 "2007-09-29 05:07:08+02";-1.71 "2007-09-29 05:09:08+02";-1.96 "2007-09-29 05:11:08+02";-2.03 "2007-09-29 05:13:08+02";-2.18 "2007-09-29 05:15:08+02";-2.2 "2007-09-29 05:17:08+02";-1.91 "2007-09-29 05:19:08+02";-2.06 "2007-09-29 05:21:08+02";-2 "2007-09-29 05:23:08+02";-1.92 "2007-09-29 05:25:08+02";-2 "2007-09-29 05:27:08+02";-1.86 "2007-09-29 05:29:08+02";-2.08 "2007-09-29 05:31:08+02";-2.03 "2007-09-29 05:33:08+02";-1.83 "2007-09-29 05:35:08+02";-1.7 "2007-09-29 05:37:08+02";-1.7 "2007-09-29 05:39:08+02";-1.6 "2007-09-29 05:41:08+02";-1.64 "2007-09-29 05:43:08+02";-1.65 "2007-09-29 05:45:08+02";-1.67 "2007-09-29 05:47:08+02";-1.8 "2007-09-29 05:49:08+02";-1.82 "2007-09-29 05:51:08+02";-1.88 "2007-09-29 05:53:08+02";-1.91 "2007-09-29 05:55:08+02";-1.92 "2007-09-29 05:57:08+02";-1.93 "2007-09-29 05:59:08+02";-1.9 "2007-09-29 06:01:08+02";-2.04 "2007-09-29 06:03:08+02";-1.98 "2007-09-29 06:05:08+02";-1.96 "2007-09-29 06:07:08+02";-1.98 "2007-09-29 06:09:08+02";-1.93 "2007-09-29 06:11:08+02";-1.78 "2007-09-29 06:13:08+02";-1.73 "2007-09-29 06:15:08+02";-1.76 "2007-09-29 06:17:08+02";-1.67 "2007-09-29 06:19:08+02";-1.68 "2007-09-29 06:21:08+02";-1.38 "2007-09-29 06:23:08+02";-1.33 "2007-09-29 06:25:07+02";-1.31 "2007-09-29 06:27:08+02";-1.34 "2007-09-29 06:29:08+02";-1.28 "2007-09-29 06:31:08+02";-1.28 "2007-09-29 06:33:07+02";-1.51 "2007-09-29 06:35:08+02";-1.76 "2007-09-29 06:37:08+02";-1.84 "2007-09-29 06:39:08+02";-1.89 "2007-09-29 06:41:08+02";-1.98 "2007-09-29 06:43:08+02";-2 "2007-09-29 06:45:08+02";-2 "2007-09-29 06:47:08+02";-1.96 "2007-09-29 06:49:08+02";-1.92 "2007-09-29 06:51:08+02";-1.88 "2007-09-29 06:53:07+02";-1.79 "2007-09-29 06:55:07+02";-1.73 "2007-09-29 06:57:08+02";-1.7 "2007-09-29 06:59:07+02";-1.72 "2007-09-29 07:01:08+02";-1.74 "2007-09-29 07:03:08+02";-1.8 "2007-09-29 07:05:08+02";-1.8 "2007-09-29 07:07:08+02";-1.84 "2007-09-29 07:09:08+02";-1.88 "2007-09-29 07:11:08+02";-1.89 "2007-09-29 07:13:08+02";-1.92 "2007-09-29 07:15:08+02";-1.92 "2007-09-29 07:17:08+02";-1.96 "2007-09-29 07:19:07+02";-1.98 "2007-09-29 07:21:08+02";-2 "2007-09-29 07:23:07+02";-1.96 "2007-09-29 07:25:08+02";-1.94 "2007-09-29 07:27:08+02";-1.92 "2007-09-29 07:29:07+02";-1.92 "2007-09-29 07:31:08+02";-1.92 "2007-09-29 07:33:08+02";-1.92 "2007-09-29 07:35:08+02";-1.94 "2007-09-29 07:37:08+02";-1.9 "2007-09-29 07:39:08+02";-1.92 "2007-09-29 07:41:08+02";-1.9 "2007-09-29 07:43:07+02";-1.9 "2007-09-29 07:45:08+02";-1.88 "2007-09-29 07:47:08+02";-1.85 "2007-09-29 07:49:08+02";-1.86 "2007-09-29 07:51:07+02";-1.87 "2007-09-29 07:53:08+02";-1.72 "2007-09-29 07:55:08+02";-1.55 "2007-09-29 07:57:08+02";-1.52 "2007-09-29 07:59:08+02";-1.53 "2007-09-29 08:01:08+02";-1.54 "2007-09-29 08:03:08+02";-1.6 "2007-09-29 08:05:08+02";-1.77 "2007-09-29 08:07:08+02";-1.8 "2007-09-29 08:09:08+02";-1.8 "2007-09-29 08:11:08+02";-1.8 "2007-09-29 08:13:08+02";-1.96 "2007-09-29 08:15:09+02";-1.88 "2007-09-29 08:17:08+02";-1.44 "2007-09-29 08:19:08+02";-1.34 "2007-09-29 08:21:08+02";-1.22 "2007-09-29 08:23:08+02";-1.34 "2007-09-29 08:25:09+02";-1.24 "2007-09-29 08:27:09+02";-0.66 "2007-09-29 08:29:09+02";-0.98 "2007-09-29 08:31:09+02";-1.1 "2007-09-29 08:33:08+02";0.1 "2007-09-29 08:35:09+02";0.27 "2007-09-29 08:37:09+02";-0.14 "2007-09-29 08:39:08+02";-0.52 "2007-09-29 08:41:08+02";-1 "2007-09-29 08:43:08+02";-0.58 "2007-09-29 08:45:08+02";-0.7 "2007-09-29 08:47:08+02";-0.6 "2007-09-29 08:49:08+02";-0.87 "2007-09-29 08:51:08+02";-0.44 "2007-09-29 08:53:08+02";-0.69 "2007-09-29 08:55:08+02";-0.14 "2007-09-29 08:57:08+02";0.12 "2007-09-29 08:59:08+02";-0.37 "2007-09-29 09:01:08+02";0.32 "2007-09-29 09:03:07+02";0.04 "2007-09-29 09:05:07+02";0.3 "2007-09-29 09:07:07+02";0.32 "2007-09-29 09:09:07+02";-0.2 "2007-09-29 09:11:07+02";-0.07 "2007-09-29 09:13:07+02";0.11 "2007-09-29 09:15:07+02";0.34 "2007-09-29 09:17:07+02";0.58 "2007-09-29 09:19:07+02";0.46 "2007-09-29 09:21:07+02";-0.27 "2007-09-29 09:23:07+02";-0.57 "2007-09-29 09:25:07+02";-0.2 "2007-09-29 09:27:07+02";0 "2007-09-29 09:29:07+02";-0.04 "2007-09-29 09:31:07+02";-0.77 "2007-09-29 09:33:07+02";-0.3 "2007-09-29 09:35:07+02";0.34 "2007-09-29 09:37:07+02";0.1 "2007-09-29 09:39:07+02";0.05 "2007-09-29 09:41:07+02";0.18 "2007-09-29 09:43:07+02";1.2 "2007-09-29 09:45:07+02";1.84 "2007-09-29 09:47:07+02";0.93 "2007-09-29 09:49:07+02";0.58 "2007-09-29 09:51:07+02";-0.22 "2007-09-29 09:53:07+02";0.09 "2007-09-29 09:55:07+02";0.28 "2007-09-29 09:57:07+02";1.4 "2007-09-29 09:59:07+02";1.38 "2007-09-29 10:01:07+02";2 "2007-09-29 10:03:07+02";2.16 "2007-09-29 10:05:07+02";2.48 "2007-09-29 10:07:07+02";2.8 "2007-09-29 10:09:07+02";1.66 "2007-09-29 10:11:07+02";1.56 "2007-09-29 10:13:07+02";1.49 "2007-09-29 10:15:07+02";0.86 "2007-09-29 10:17:07+02";1.37 "2007-09-29 10:19:07+02";1.16 "2007-09-29 10:21:07+02";1.16 "2007-09-29 10:23:07+02";1.6 "2007-09-29 10:25:07+02";0.84 "2007-09-29 10:27:07+02";2.14 "2007-09-29 10:29:07+02";2.25 "2007-09-29 10:31:07+02";2.25 "2007-09-29 10:33:07+02";2.12 "2007-09-29 10:35:07+02";1.68 "2007-09-29 10:37:07+02";2.14 "2007-09-29 10:39:07+02";1.1 "2007-09-29 10:41:07+02";1.23 "2007-09-29 10:43:07+02";1.4 "2007-09-29 10:45:07+02";1.78 "2007-09-29 10:47:07+02";1.36 "2007-09-29 10:49:07+02";2.26 "2007-09-29 10:51:07+02";1.8 "2007-09-29 10:53:07+02";2.08 "2007-09-29 10:55:07+02";1.96 "2007-09-29 10:57:07+02";2.46 "2007-09-29 10:59:07+02";2.69 "2007-09-29 11:01:07+02";2.24 "2007-09-29 11:03:07+02";1.59 "2007-09-29 11:05:07+02";1.74 "2007-09-29 11:07:07+02";1.38 "2007-09-29 11:09:07+02";1.97 "2007-09-29 11:11:07+02";2.06 "2007-09-29 11:13:07+02";1.64 "2007-09-29 11:15:07+02";2.22 "2007-09-29 11:17:07+02";3.07 "2007-09-29 11:19:07+02";1.98 "2007-09-29 11:21:07+02";2.88 "2007-09-29 11:23:07+02";2.9 "2007-09-29 11:25:07+02";1.62 "2007-09-29 11:27:07+02";1.79 "2007-09-29 11:29:07+02";2.7 "2007-09-29 11:31:07+02";1.96 "2007-09-29 11:33:07+02";2.9 "2007-09-29 11:35:07+02";2.76 "2007-09-29 11:37:07+02";2.08 "2007-09-29 11:39:07+02";2.88 "2007-09-29 11:41:07+02";2.55 "2007-09-29 11:43:07+02";2.49 "2007-09-29 11:45:07+02";2.52 "2007-09-29 11:47:07+02";2.68 "2007-09-29 11:49:07+02";3.1 "2007-09-29 11:51:07+02";3.72 "2007-09-29 11:53:07+02";2.94 "2007-09-29 11:55:07+02";3.86 "2007-09-29 11:57:07+02";3.76 "2007-09-29 11:59:07+02";2.8 "2007-09-29 12:01:07+02";2.28 "2007-09-29 12:03:07+02";2.8 "2007-09-29 12:05:07+02";3.1 "2007-09-29 12:07:07+02";2.63 "2007-09-29 12:09:07+02";2.76 "2007-09-29 12:11:07+02";4.02 "2007-09-29 12:13:07+02";3.47 "2007-09-29 12:15:07+02";3.2 "2007-09-29 12:17:07+02";2.65 "2007-09-29 12:19:07+02";4.11 "2007-09-29 12:21:07+02";2.66 "2007-09-29 12:23:07+02";4.34 "2007-09-29 12:25:07+02";4.1 "2007-09-29 12:27:07+02";4.17 "2007-09-29 12:29:07+02";3.16 "2007-09-29 12:31:07+02";3.98 "2007-09-29 12:33:07+02";3.72 "2007-09-29 12:35:07+02";3.66 "2007-09-29 12:37:07+02";3.26 "2007-09-29 12:39:07+02";4.5 "2007-09-29 12:41:07+02";3.94 "2007-09-29 12:43:07+02";4.11 "2007-09-29 12:45:07+02";3.89 "2007-09-29 12:47:06+02";3.22 "2007-09-29 12:49:06+02";4.26 "2007-09-29 12:51:06+02";4.98 "2007-09-29 12:53:06+02";4.46 "2007-09-29 12:55:06+02";4.86 "2007-09-29 12:57:07+02";3.91 "2007-09-29 12:59:07+02";3.56 "2007-09-29 13:01:07+02";5.27 "2007-09-29 13:03:07+02";4.12 "2007-09-29 13:05:07+02";5.07 "2007-09-29 13:07:06+02";4.16 "2007-09-29 13:09:06+02";4.56 "2007-09-29 13:11:06+02";4.24 "2007-09-29 13:13:06+02";4.34 "2007-09-29 13:15:06+02";4.63 "2007-09-29 13:17:06+02";4.68 "2007-09-29 13:19:06+02";3.71 "2007-09-29 13:21:06+02";3.79 "2007-09-29 13:23:06+02";4.61 "2007-09-29 13:25:06+02";4.95 "2007-09-29 13:27:06+02";4.66 "2007-09-29 13:29:06+02";4.32 "2007-09-29 13:31:06+02";3.98 "2007-09-29 13:33:06+02";5.43 "2007-09-29 13:35:06+02";4.38 "2007-09-29 13:37:06+02";4.39 "2007-09-29 13:39:06+02";4.7 "2007-09-29 13:41:06+02";4.34 "2007-09-29 13:43:06+02";5.56 "2007-09-29 13:45:06+02";4.61 "2007-09-29 13:47:06+02";4.58 "2007-09-29 13:49:06+02";4.81 "2007-09-29 13:51:06+02";5.04 "2007-09-29 13:53:06+02";5.74 "2007-09-29 13:55:06+02";4.92 "2007-09-29 13:57:06+02";4.65 "2007-09-29 13:59:06+02";5.08 "2007-09-29 14:01:06+02";4.9 "2007-09-29 14:03:06+02";4.34 "2007-09-29 14:05:06+02";5.42 "2007-09-29 14:07:06+02";5.58 "2007-09-29 14:09:06+02";4.98 "2007-09-29 14:11:06+02";4.91 "2007-09-29 14:13:06+02";5.44 "2007-09-29 14:15:06+02";4.62 "2007-09-29 14:17:06+02";5.36 "2007-09-29 14:19:06+02";4.56 "2007-09-29 14:21:06+02";4.77 "2007-09-29 14:23:06+02";4.88 "2007-09-29 14:25:06+02";4.29 "2007-09-29 14:27:06+02";4.43 "2007-09-29 14:29:06+02";4.78 "2007-09-29 14:31:06+02";5.06 "2007-09-29 14:33:06+02";4.83 "2007-09-29 14:35:06+02";4.44 "2007-09-29 14:37:06+02";5.48 "2007-09-29 14:39:06+02";4.9 "2007-09-29 14:41:06+02";4.42 "2007-09-29 14:43:06+02";4.82 "2007-09-29 14:45:06+02";4.9 "2007-09-29 14:47:06+02";4.76 "2007-09-29 14:49:06+02";5.48 "2007-09-29 14:51:06+02";5.48 "2007-09-29 14:53:06+02";5.46 "2007-09-29 14:55:06+02";5.54 "2007-09-29 14:57:06+02";5.53 "2007-09-29 14:59:06+02";5.5 "2007-09-29 15:01:06+02";5.33 "2007-09-29 15:03:06+02";6.18 "2007-09-29 15:05:06+02";4.91 "2007-09-29 15:07:06+02";5.08 "2007-09-29 15:09:06+02";4.71 "2007-09-29 15:11:06+02";4.49 "2007-09-29 15:13:06+02";5.47 "2007-09-29 15:15:06+02";4.58 "2007-09-29 15:17:06+02";4.75 "2007-09-29 15:19:06+02";5.46 "2007-09-29 15:21:06+02";5.39 "2007-09-29 15:23:06+02";5.59 "2007-09-29 15:25:06+02";5.76 "2007-09-29 15:27:06+02";5.08 "2007-09-29 15:29:06+02";5.96 "2007-09-29 15:31:06+02";4.9 "2007-09-29 15:33:06+02";5.36 "2007-09-29 15:35:06+02";5.08 "2007-09-29 15:37:06+02";5.93 "2007-09-29 15:39:06+02";5.34 "2007-09-29 15:41:06+02";5.24 "2007-09-29 15:43:06+02";5.78 "2007-09-29 15:45:06+02";6.58 "2007-09-29 15:47:06+02";6.09 "2007-09-29 15:49:06+02";5.4 "2007-09-29 15:51:06+02";6.18 "2007-09-29 15:53:06+02";5.79 "2007-09-29 15:55:06+02";5.36 "2007-09-29 15:57:06+02";5.8 "2007-09-29 15:59:07+02";6.06 "2007-09-29 16:01:06+02";5.7 "2007-09-29 16:03:07+02";5.58 "2007-09-29 16:05:07+02";6.5 "2007-09-29 16:07:07+02";5.83 "2007-09-29 16:09:07+02";5.54 "2007-09-29 16:11:06+02";5.8 "2007-09-29 16:13:06+02";6.03 "2007-09-29 16:15:06+02";5.54 "2007-09-29 16:17:07+02";5.56 "2007-09-29 16:19:07+02";6.04 "2007-09-29 16:21:07+02";5.74 "2007-09-29 16:23:06+02";5.4 "2007-09-29 16:25:07+02";6.39 "2007-09-29 16:27:07+02";6.64 "2007-09-29 16:29:07+02";6.3 "2007-09-29 16:31:07+02";5.86 "2007-09-29 16:33:07+02";5.62 "2007-09-29 16:35:07+02";6.29 "2007-09-29 16:37:07+02";5.24 "2007-09-29 16:39:07+02";5.92 "2007-09-29 16:41:07+02";5.68 "2007-09-29 16:43:07+02";6.2 "2007-09-29 16:45:07+02";6.53 "2007-09-29 16:47:06+02";5.44 "2007-09-29 16:49:06+02";5.58 "2007-09-29 16:51:07+02";4.68 "2007-09-29 16:53:07+02";6.36 "2007-09-29 16:55:07+02";5.46 "2007-09-29 16:57:06+02";5.4 "2007-09-29 16:59:06+02";5.84 "2007-09-29 17:01:06+02";6.12 "2007-09-29 17:03:06+02";6.28 "2007-09-29 17:05:06+02";5.48 "2007-09-29 17:07:06+02";4.67 "2007-09-29 17:09:06+02";4.69 "2007-09-29 17:11:06+02";4.36 "2007-09-29 17:13:06+02";4.6 "2007-09-29 17:15:06+02";4.86 "2007-09-29 17:17:06+02";4.52 "2007-09-29 17:19:06+02";4.04 "2007-09-29 17:21:06+02";3.98 "2007-09-29 17:23:06+02";3.8 "2007-09-29 17:25:06+02";3.76 "2007-09-29 17:27:06+02";3.44 "2007-09-29 17:29:06+02";3.44 "2007-09-29 17:31:06+02";3.45 "2007-09-29 17:33:06+02";3.64 "2007-09-29 17:35:06+02";3.62 "2007-09-29 17:37:06+02";3.43 "2007-09-29 17:39:06+02";3.52 "2007-09-29 17:41:06+02";3.31 "2007-09-29 17:43:06+02";3.13 "2007-09-29 17:45:06+02";3.21 "2007-09-29 17:47:06+02";3.26 "2007-09-29 17:49:06+02";3.17 "2007-09-29 17:51:06+02";3.05 "2007-09-29 17:53:06+02";2.86 "2007-09-29 17:55:06+02";2.84 "2007-09-29 17:57:06+02";2.79 "2007-09-29 17:59:06+02";2.6 "2007-09-29 18:01:06+02";2.54 "2007-09-29 18:03:06+02";2.51 "2007-09-29 18:05:06+02";2.54 "2007-09-29 18:07:06+02";2.6 "2007-09-29 18:09:06+02";2.5 "2007-09-29 18:11:06+02";2.25 "2007-09-29 18:13:06+02";2.26 "2007-09-29 18:15:06+02";2.28 "2007-09-29 18:17:06+02";1.95 "2007-09-29 18:19:06+02";1.9 "2007-09-29 18:21:06+02";1.8 "2007-09-29 18:23:06+02";1.78 "2007-09-29 18:25:06+02";1.56 "2007-09-29 18:27:06+02";1.59 "2007-09-29 18:29:06+02";1.55 "2007-09-29 18:31:06+02";1.52 "2007-09-29 18:33:06+02";1.48 "2007-09-29 18:35:06+02";1.5 "2007-09-29 18:37:06+02";1.44 "2007-09-29 18:39:06+02";1.36 "2007-09-29 18:41:06+02";1.34 "2007-09-29 18:43:06+02";1.26 "2007-09-29 18:45:06+02";1.2 "2007-09-29 18:47:06+02";1.28 "2007-09-29 18:49:06+02";1.34 "2007-09-29 18:51:06+02";1.29 "2007-09-29 18:53:06+02";1.36 "2007-09-29 18:55:06+02";1.44 "2007-09-29 18:57:06+02";1.43 "2007-09-29 18:59:06+02";1.73 "2007-09-29 19:01:06+02";1.94 "2007-09-29 19:03:06+02";1.86 "2007-09-29 19:05:06+02";1.88 "2007-09-29 19:07:06+02";1.78 "2007-09-29 19:09:06+02";1.52 "2007-09-29 19:11:06+02";1.36 "2007-09-29 19:13:06+02";1.28 "2007-09-29 19:15:06+02";1.32 "2007-09-29 19:17:06+02";1.24 "2007-09-29 19:19:06+02";1.17 "2007-09-29 19:21:06+02";1.16 "2007-09-29 19:23:06+02";1.22 "2007-09-29 19:25:06+02";1.18 "2007-09-29 19:27:06+02";1.13 "2007-09-29 19:29:06+02";1.06 "2007-09-29 19:31:06+02";1.12 "2007-09-29 19:33:06+02";1.16 "2007-09-29 19:35:06+02";1.04 "2007-09-29 19:37:06+02";0.98 "2007-09-29 19:39:06+02";0.85 "2007-09-29 19:41:06+02";0.85 "2007-09-29 19:43:06+02";1.04 "2007-09-29 19:45:06+02";0.97 "2007-09-29 19:47:06+02";1.08 "2007-09-29 19:49:06+02";1.2 "2007-09-29 19:51:06+02";0.99 "2007-09-29 19:53:06+02";0.86 "2007-09-29 19:55:06+02";0.88 "2007-09-29 19:57:06+02";0.86 "2007-09-29 19:59:06+02";0.82 "2007-09-29 20:01:06+02";0.89 "2007-09-29 20:03:06+02";0.86 "2007-09-29 20:05:06+02";0.94 "2007-09-29 20:07:06+02";0.96 "2007-09-29 20:09:06+02";1 "2007-09-29 20:11:06+02";1.11 "2007-09-29 20:13:06+02";1.06 "2007-09-29 20:15:06+02";0.94 "2007-09-29 20:17:06+02";1.18 "2007-09-29 20:19:06+02";1.2 "2007-09-29 20:21:06+02";1.05 "2007-09-29 20:23:06+02";0.94 "2007-09-29 20:25:06+02";1.02 "2007-09-29 20:27:06+02";0.96 "2007-09-29 20:29:06+02";0.85 "2007-09-29 20:31:06+02";0.79 "2007-09-29 20:33:06+02";0.92 "2007-09-29 20:35:06+02";0.7 "2007-09-29 20:37:06+02";0.78 "2007-09-29 20:39:06+02";0.64 "2007-09-29 20:41:06+02";0.54 "2007-09-29 20:43:06+02";0.68 "2007-09-29 20:45:06+02";0.58 "2007-09-29 20:47:06+02";0.48 "2007-09-29 20:49:06+02";0.46 "2007-09-29 20:51:06+02";0.4 "2007-09-29 20:53:06+02";0.5 "2007-09-29 20:55:06+02";0.4 "2007-09-29 20:57:06+02";0.49 "2007-09-29 20:59:06+02";0.38 "2007-09-29 21:01:06+02";0.32 "2007-09-29 21:03:06+02";0.31 "2007-09-29 21:05:06+02";0.22 "2007-09-29 21:07:05+02";0.2 "2007-09-29 21:09:05+02";0.17 "2007-09-29 21:11:05+02";0.12 "2007-09-29 21:13:05+02";0.22 "2007-09-29 21:15:05+02";0.15 "2007-09-29 21:17:05+02";0.18 "2007-09-29 21:19:05+02";0.09 "2007-09-29 21:21:05+02";-0.01 "2007-09-29 21:23:05+02";-0.05 "2007-09-29 21:25:05+02";0 "2007-09-29 21:27:05+02";0.12 "2007-09-29 21:29:05+02";0.09 "2007-09-29 21:31:05+02";0.18 "2007-09-29 21:33:05+02";0.04 "2007-09-29 21:35:05+02";0.02 "2007-09-29 21:37:05+02";-0.04 "2007-09-29 21:39:05+02";-0.06 "2007-09-29 21:41:05+02";0.06 "2007-09-29 21:43:05+02";0.04 "2007-09-29 21:45:05+02";0.04 "2007-09-29 21:47:05+02";0.15 "2007-09-29 21:49:05+02";0.22 "2007-09-29 21:51:05+02";0.24 "2007-09-29 21:53:05+02";0.22 "2007-09-29 21:55:05+02";0.1 "2007-09-29 21:57:05+02";0.11 "2007-09-29 21:59:05+02";0.06 "2007-09-29 22:01:05+02";0.09 "2007-09-29 22:03:05+02";0.2 "2007-09-29 22:05:05+02";0.11 "2007-09-29 22:07:05+02";0.08 "2007-09-29 22:09:05+02";0.08 "2007-09-29 22:11:05+02";0.07 "2007-09-29 22:13:05+02";0.06 "2007-09-29 22:15:05+02";0.11 "2007-09-29 22:17:05+02";0.12 "2007-09-29 22:19:05+02";0.1 "2007-09-29 22:21:05+02";0.19 "2007-09-29 22:23:05+02";0.24 "2007-09-29 22:25:05+02";0.24 "2007-09-29 22:27:05+02";0.24 "2007-09-29 22:29:05+02";0.22 "2007-09-29 22:31:05+02";0.24 "2007-09-29 22:33:05+02";0.28 "2007-09-29 22:35:05+02";0.28 "2007-09-29 22:37:05+02";0.26 "2007-09-29 22:39:05+02";0.28 "2007-09-29 22:41:05+02";0.28 "2007-09-29 22:43:05+02";0.26 "2007-09-29 22:45:05+02";0.27 "2007-09-29 22:47:05+02";0.31 "2007-09-29 22:49:05+02";0.29 "2007-09-29 22:51:05+02";0.27 "2007-09-29 22:53:05+02";0.3 "2007-09-29 22:55:05+02";0.26 "2007-09-29 22:57:05+02";0.26 "2007-09-29 22:59:05+02";0.3 "2007-09-29 23:01:05+02";0.3 "2007-09-29 23:03:05+02";0.28 "2007-09-29 23:05:05+02";0.22 "2007-09-29 23:07:05+02";0.2 "2007-09-29 23:09:05+02";0.16 "2007-09-29 23:11:05+02";0.2 "2007-09-29 23:13:05+02";0.2 "2007-09-29 23:15:05+02";0.12 "2007-09-29 23:17:05+02";0.08 "2007-09-29 23:19:05+02";0.1 "2007-09-29 23:21:05+02";0.14 "2007-09-29 23:23:05+02";0.16 "2007-09-29 23:25:05+02";0.27 "2007-09-29 23:27:05+02";0.32 "2007-09-29 23:29:05+02";0.34 "2007-09-29 23:31:05+02";0.4 "2007-09-29 23:33:05+02";0.37 "2007-09-29 23:35:05+02";0.35 "2007-09-29 23:37:05+02";0.32 "2007-09-29 23:39:05+02";0.23 "2007-09-29 23:41:05+02";0.26 "2007-09-29 23:43:05+02";0.35 "2007-09-29 23:45:05+02";0.31 "2007-09-29 23:47:05+02";0.32 "2007-09-29 23:49:05+02";0.3 "2007-09-29 23:51:05+02";0.36 "2007-09-29 23:53:05+02";0.33 "2007-09-29 23:55:05+02";0.27 "2007-09-29 23:57:05+02";0.27 "2007-09-29 23:59:05+02";0.2 </code></pre> <p>Expected results:</p> <p>Hourly mean for 16 o'clock is : 5.7=5.36+5.80+6.06+5.70+5.58</p> <pre><code>2007-09-29 16:00 5.7 </code></pre> <p>I have tried this code but it doesn't work properly.</p> <pre><code>select date_trunc('hour', newest_time) as average_time, (oldest_temp + middle_temp + newest_temp) / 3 as average_temp from ( select date_trunc('hour', dt) as average_time, lag(dt, 2) over w as oldest_time, lag(dt, 1) over w as middle_time, dt as newest_time, lag(ambtemp, 2) over w as oldest_temp, lag(ambtemp, 1) over w as middle_temp, ambtemp as newest_temp from n32 window w as (order by dt) ) as s where oldest_time = newest_time - '4 minutes'::interval and middle_time = newest_time - '2 minutes'::interval and extract(minute from newest_time) in (2, 3); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USA.Amidi
    1. USA.Amidi
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAverage function in PostgreSQL with 5 minutes tolerance
      1. USA.Amidi
      1. VTFavorite
    2. VO
      singulars
      1. POAverage function in PostgreSQL with 5 minutes tolerance
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POAverage function in PostgreSQL with 5 minutes tolerance
      1. This table or related slice is empty.
      1. VTDownMod
    1. COTry using `lag()` and `lead()` function from [`this`](http://www.postgresql.org/docs/current/static/functions-window.html) page to get 2 values before and 2 values after.
      singulars
      1. POAverage function in PostgreSQL with 5 minutes tolerance
      1. USIgor Romanchenko
    2. CO@IgorRomanchenko and question is updated
      singulars
      1. POAverage function in PostgreSQL with 5 minutes tolerance
      1. USA.Amidi
    3. CO@Catcall, question is updated
      singulars
      1. POAverage function in PostgreSQL with 5 minutes tolerance
      1. USA.Amidi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload