Calculatng median for each hourly slice in a temporary database in PostgreSQL
I am working on a temporary database. I need to perform aggregation on time series data. I had to find instant hours, which could be hours: 00, hour: 01, or hour -1: 59, and retrieving the corresponding observations refers to 5 minutes after and before the instant clock. Hence, extracting the median value from the above 5 candidates.
Sample data1
Time&Date(timestamps) Surface_Temprature
2012-11-02 00:45:09+02 -1.770
2012-11-02 00:47:09+02 -1.780
2012-11-02 00:49:09+02 -1.500
2012-11-02 00:51:09+02 -1.460
2012-11-02 00:53:09+02 -1.720
2012-11-02 00:55:09+02 -1.670
2012-11-02 00:57:09+02 -1.560
2012-11-02 00:59:09+02 -1.690
2012-11-02 01:01:09+02 -1.970
2012-11-02 01:03:09+02 -1.790
2012-11-02 01:05:09+02 -1.790
2012-11-02 01:07:09+02 -1.840
2012-11-02 01:09:09+02 -1.910
2012-11-02 01:11:09+02 -1.870
Sample Data2:
Date&Time (Timestamps) SurfaceTemprature
2007-09-28 23:46:14+02 -1.320
2007-09-28 23:48:14+02 -1.460
2007-09-28 23:50:14+02 -1.620
2007-09-28 23:52:14+02 -1.670
2007-09-28 23:54:14+02 -1.640
2007-09-28 23:56:14+02 -1.700
2007-09-28 23:58:14+02 -1.810
2012-11-03 00:00:14+02 -1.890
2012-11-03 00:02:14+02 -1.790
2012-11-03 00:04:14+02 -1.780
2012-11-03 00:06:14+02 -1.660
2012-11-03 00:08:14+02 -1.680
2012-11-03 00:10:14+02 -1.900
2012-11-03 00:12:14+02 -1.820
2012-11-03 00:14:14+02 -1.780
2012-11-03 00:16:14+02 -1.940
2012-11-03 00:18:14+02 -1.900
Is there anyone who can help me?
+3
source to share