Project

General

Profile

How to query the SISPI Database

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