Examples of how to use SQL on the EGSO SEC Server

The EGSO SEC Server is implemented as a PostgreSQL database. There is a Tutorial available - you might find the List of PostgreSQL Commands useful for the examples below, particularly the page on using SELECT.

Time Specification

The following time specifications are know to work for SQL:
1994-01-01 00:00:00
1994-Jan-01 00:00:00
01-Jan-1994 00:00:00
There are two ways (more?) of specifying a time range:
WHERE time_start>='2002-02-12 00:00:00' AND time_start<='2002-02-16 23:59:59'
  and
WHERE time_start BETWEEN '2002-02-12 00:00:00' AND '2002-02-16 23:59:59'

Examples

Below we show some examples of how to use SQL in the EGSO SEC Server:
  1. The simplest search of a list for a specific time interval
    SELECT * FROM hessi_flare 
    WHERE time_start>='2002-02-12 00:00:00' AND time_start<='2002-02-16 23:59:59'
    ORDER BY time_start
    try here
    
  2. A simple search, but only including some of the parameters in the output table
    SELECT time_start,time_end,nar,latitude,longitude,xray_class FROM sgas_event
    WHERE time_start BETWEEN '1999-12-05 00:00:00' AND '2000-01-01 23:59:59'
    ORDER BY time_start
    try here
    
  3. A simple search looking for a particular NOAA Active Region No. (9393) and only reporting certain parameters. This example also demonstrates how to define and use of the a short name for the list (in this case "sg" for "sgas_event"):
    SELECT sg.time_start,sg.time_peak,sg.nar FROM sgas_event sg WHERE sg.nar=9393
    
  4. Select events for a range of NOAA Active Region No.; refine search to only include events that have an X-ray class > M1 and a location in the north-west quadrant of the solar disk. The results are orderered by X-ray class (just for fun):
    SELECT * FROM sgas_event WHERE nar>9500 AND nar<9700
    AND xray_class >'C6' AND latitude>0 AND longitude>0
    ORDER BY xray_class
    
  5. Find the times when the GOES X-ray background exceeded a specified level:
    SELECT * FROM dsd_list 
    WHERE time_start BETWEEN '1994-01-01 00:00:00' AND '2004-02-10 23:59:59' 
    AND xray_bkg > 'C2' 
    ORDER BY time_start
    

Comparing Lists

It is also fun to compare the contents of two lists:

  1. In the following two queries we are comparing the events in the SGAS event list and the Yohkoh Flare list for a time interval they have in common - the queries should be issued separately. Extra events in the SGAS list due to radio (245MHz) peaks have been eliminated by only selecting events where there are values in the xray_class or optical_class columns:
    SELECT * FROM sgas_event 
    WHERE time_start BETWEEN '1999-09-17 00:00:00' AND '1999-10-03 23:59:59' 
    AND (xray_class IS NOT NULL OR optical_class IS NOT NULL)
    ORDER BY time_start;
    
    SELECT * FROM yohkoh_flare_list 
    WHERE time_start BETWEEN '1999-09-17 00:00:00' AND '1999-10-03 23:59:59' 
    ORDER BY time_start;
    
  2. Here we compare the CME reported by LASCO with events recorded by other instruments. In both cases we are looking at the north-west quadrant of the solar disk; for the SGAS list we are only reporting a limited number of parameters. Again the queries should be issued separately.
    SELECT * FROM lasco_cme_list 
    WHERE time_start BETWEEN '1999-11-28 00:00:00' AND '1999-12-31 23:59:59' 
    AND pa_central BETWEEN 0 AND 90
    ORDER BY time_start
    
    SELECT  time_start,time_end,latitude,longitude,xray_class  FROM sgas_event
    WHERE time_start BETWEEN '1999-11-28 00:00:00' AND '1999-12-31 23:59:59' 
    AND latitude >0 AND longitude > 0
    ORDER BY time_start
    
  3. Here we try a UNION to compare the entries in two very similar lists - the Yohkoh flare list and the SGAS event list. The result is a single list with records interleaved - I've made the Yohkoh list record ID negative to help differentiate between the two sources.
    SELECT time_start,time_end, nar,latitude,longitude,xray_class, -yfc_id AS ID 
    FROM yohkoh_flare_list WHERE nar=8706
    UNION
    SELECT time_start,time_end, nar,latitude,longitude,xray_class, sgs_id
    FROM sgas_event WHERE nar=8706
    


R.D. Bentley
16 March 2004