def static_sensor_data(sensor_id, start_time, end_time): maxVal = db.query_dataframe(f"""SELECT max FROM sensor_data where sensor_id = '{sensor_id}' AND time BETWEEN '{start_time}' AND '{end_time}' ORDER BY time ASC""") #sampling_rate = 359.754 sampling_rate = 1 start_time = pd.Timestamp(start_time) end_time = pd.Timestamp(end_time) diff = end_time - start_time num = diff.total_seconds() time_np = np.linspace(0,int(num),int(num*sampling_rate) +1) time = pd.DataFrame(data=time_np) return time, maxVal
def fetch_sensor_db_data(sensor_id, start,end): assert end > start db.init() static_data = db.query_dataframe(""" SELECT time,min,max,average,variance FROM monitoring.sensor_data WHERE sensor_id = %s AND time >= %s AND time <= %s ORDER BY time ASC """, (sensor_id, start,end)) return static_data
def fetch_sensor_db_data_gapfill(sensor_id, start, end): assert end > start db.init() data = db.query_dataframe(""" SELECT time_bucket_gapfill('1 second', time) as time, COALESCE(avg(min), 'NaN') as min, COALESCE(avg(max), 'NaN') as max, COALESCE(avg(average), 'NaN') as average, COALESCE(avg(variance), 'NaN') as variance FROM monitoring.sensor_data WHERE sensor_id = %s AND time > %s AND time < %s GROUP BY 1 ORDER BY 1 ASC""", (sensor_id, start, end)) return data