How to query the SISPI Database¶
Note (Sept. 6, 2013 - HTD, KH):
Technically this is okay but we don't like to have the observers access the DB directly even though it's a readonly connection. If something happens to the DB we are done for the night. It is also no longer required. These queries can be just as easily submitted through the Query page of the standard Telemetry viewer.
So maybe just don't do it this way.
Connect to the database:¶
To connect to the DB and execute this query you can use (inside CTIO VPN)
psql -U decam_reader -h server2 decam_prd
the password is "reader".
To see the tables:
decam_prd> \d
To see the columns of a table, e.g. environmental_data:
decam_prd> \d environmental_data
Sample query¶
Sample query to retrieve information from the environmental_data table
SELECT
time_recorded,pmas_temp,pme_temp,pmn_temp,pmw_temp,pms_temp,lowdome_temp,outside_temp,domefloor_temp
FROM environmental_data
WHERE time_recorded BETWEEN '2013-02-18 00:00:00' AND '2013-02-18 10:00:00'
to connect to the DB and execute this query you can use (inside CTIO VPN)
psql -U decam_reader -h server2 decam_prd -f <sql query>
the password is "reader".
Result¶
The result is a table like this:
time_recorded | pmas_temp | pme_temp | pmn_temp | pmw_temp | pms_temp | lowdome_temp | outside_temp | domefloor_temp
------------------------------- ----------- ---------- ---------- ---------- ---------- -------------- -------------- ----------------
2013-02-18 00:00:00.599738 00 | 12.9 | 13.2 | 12.5 | 12.9 | 12.6 | 13.7 | 12.7 | 14.8
2013-02-18 00:00:02.612772 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.7 | 12.7 | 14.8
2013-02-18 00:00:04.625428 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:06.636863 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:08.64925 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:10.677318 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:12.690656 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:14.703956 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:16.716561 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:18.727435 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
2013-02-18 00:00:20.737928 00 | 12.9 | 13.3 | 12.5 | 12.9 | 12.6 | 13.68 | 12.8 | 14.8
Dealing with time¶
This works:
% python
>>> import ephem
>>> mjdNow = ephem.julian_date()-2400000.5; mjdNow
56342.234745370224
>>> mjdThen = ephem.julian_date("2013-02-18 00:00:20.737928 00")-2400000.5; mjdThen
56341.00024002232
>>> desdayZero = 56282; # 12-21-2012, the end of the last bak tun, the end of the world
>>> desday = mjdNow-desdayZero; desday
60.23474537022412
Meaning of the environmental_data columns¶
OUTSIDE_TEMP: outside wall dome temperature OUTSIDE_HUMIDITY: outside humidity (weather station) OUTSIDE_PRESSURE: outside pressure (weather station) WIND_DIR: wind direction (weather station) WIND_SPEED: wind speed (weather station) DIMM_SEEING: dimm seeing (seeing monitor) INSIDE_HUMIDITY: inside dome humidity PMN_TEMP: primary mirror north air temperature PMS_TEMP: primary mirror south air temperature PME_TEMP: primary mirror east air temperature PMW_TEMP: primary mirror west air temperature PMAS_TEMP: primary mirror above surface temperature PMOS_TEMP: primary mirror on surface temperature CHIMNEY_TEMP: chimney air temperature UTN_TEMP: upper truss north air temperature UTS_TEMP: upper truss south air temperature UTE_TEMP: upper truss east air temperature UTW_TEMP: upper truss west air temperature LOWDOME_TEMP: near to stairs air temperature HIGHDOME_TEMP: high dome air temperature DOMEFLOOR_TEMP: below stairs floor temperature