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


1 answer


Something like this should



SELECT 
  EXTRACT(HOUR FROM mytimestamp) AS hour,
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER by temperature) AS median 
FROM mytable GROUP BY EXTRACT hour;

      

0


source







All Articles