AIM: The aim of this IPython notebook is to find the start and end times of collected data for different sensor streams.
from IPython.display import HTML
import requests
import pandas as pd
import MySQLdb
import pandas.io.sql as psql
import datetime
import time
import pytz
import time
import matplotlib.dates as mdates
import matplotlib.pyplot as plt
from collections import OrderedDict
mysql_conn=OrderedDict()
dbs=["multisensor","smart_meter","water_meter","sys_info","jplug"]
tables=["light_temp","smart_meter_data","water_data","sys_info_data","jplug_data"]
for db in dbs:
mysql_conn[db]=MySQLdb.connect(user='root',passwd='password',db=db)
Setting up the styles!
styles = requests.get("https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/custom.css")
HTML(styles.text)
import json
s = requests.get("https://raw.github.com/CamDavidsonPilon/Probabilistic-Programming-and-Bayesian-Methods-for-Hackers/master/styles/bmh_matplotlibrc.json").json()
matplotlib.rcParams.update(s)
figsize(10,7)
result={}
for i in range(len(tables)):
if tables[i]!="sys_info_data":
query='select MIN(timestamp) as min,MAX(timestamp) as max from %s where timestamp between 1357277453 and 1388813453;' %tables[i]
else:
query='select MIN(server_time) as min,MAX(server_time) as max from %s where server_time between 1357277453 and 1388813453;' %tables[i]
result[dbs[i]]=psql.frame_query(query,mysql_conn[dbs[i]])
result
{'jplug': min max 0 1368980586 1379473855, 'multisensor': min max 0 1369387845 1375716189, 'smart_meter': min max 0 1368696574 1375716796, 'sys_info': min max 0 1370251254 1375720317, 'water_meter': min max 0 1.369903e+09 1.375429e+09}
for db in dbs:
print "%s : Starts at: %s; Ends at: %s\n" %(db,datetime.datetime.fromtimestamp(result[db]['min']).strftime('%Y-%m-%d'),datetime.datetime.fromtimestamp(result[db]['max']).strftime('%Y-%m-%d'))
multisensor : Starts at: 2013-05-24; Ends at: 2013-08-05 smart_meter : Starts at: 2013-05-16; Ends at: 2013-08-05 water_meter : Starts at: 2013-05-30; Ends at: 2013-08-02 sys_info : Starts at: 2013-06-03; Ends at: 2013-08-05 jplug : Starts at: 2013-05-19; Ends at: 2013-09-18