Project

General

Profile

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