CREATE VIEW WH_HOURLY (
     ARRAY_INDEX, READING_TIMESTAMP, WATT_HOURS, SOURCE_ID) 
   AS
      SELECT ARRAY_INDEX, TIMESTAMP(DATE), VALUE, "_ID"
             FROM TWC_ENERGY_VALUES;

CREATE VIEW weather_observations (
           OBSERATION_TIME,
           EXPIRE_TIME,
           ARRAY_INDEX, CLASS, CLDS, DAY_IND, DEWPT, 
           FEELS_LIKE, GUST, HEAT_INDEX, ICON_EXTD, KEY, MAX_TEMP, MIN_TEMP,
           OBS_ID, OBS_NAME, PRECIP_HRLY, PRECIP_TOTAL, PRESSURE, PRESSURE_DESC,
           PRESSURE_TEND, RH, TEMP, UV_DESC, UV_INDEX, 
           VIS, WC, WDIR, WDIR_CARDINAL, WSPD, WX_ICON, WX_PHRASE, CLOUDANT_ID  )
AS 
   SELECT  (TIMESTAMP('1970-01-01', '00:00:00') + VALID_TIME_GMT SECONDS) - 7 HOURS,
            TIMESTAMP('1970-01-01', '00:00:00') + EXPIRE_TIME_GMT SECONDS - 7 HOURS,
           ARRAY_INDEX, CLASS, CLDS, DAY_IND, DEWPT, 
           FEELS_LIKE, GUST, HEAT_INDEX, ICON_EXTD, KEY, MAX_TEMP, MIN_TEMP,
           OBS_ID, OBS_NAME, PRECIP_HRLY, PRECIP_TOTAL, PRESSURE, PRESSURE_DESC,
           PRESSURE_TEND, RH, TEMP, UV_DESC, UV_INDEX, 
           VIS, WC, WDIR, WDIR_CARDINAL, WSPD, WX_ICON, WX_PHRASE, "_ID"
      FROM TWC_OBSERVATIONS;

  
CREATE VIEW "ENERGY_WEATHER" (
            "E_ARRAY_INDEX", "READING_TIMESTAMP", "WATT_HOURS", "OBSERVATION_TIME",           
            "EXPIRE_TIME", "W_ARRAY_INDEX", "CLASS", "CLDS", "DAY_IND", "DEWPT", 
            "FEELS_LIKE", "GUST", "HEAT_INDEX", "ICON_EXTD", "KEY", "MAX_TEMP", 
            "MIN_TEMP", "OBS_ID", "OBS_NAME", "PRECIP_HRLY", "PRECIP_TOTAL", "PRESSURE", 
            "PRESSURE_DESC", "PRESSURE_TEND", "RH", "TEMP", "UV_DESC", "UV_INDEX", "VIS", 
            "WC", "WDIR", "WDIR_CARDINAL", "WSPD", "WX_ICON", "WX_PHRASE") 
AS
     SELECT E.ARRAY_INDEX, READING_TIMESTAMP, WATT_HOURS,
            OBSERATION_TIME, EXPIRE_TIME, W.ARRAY_INDEX, CLASS, CLDS,
            DAY_IND, DEWPT, FEELS_LIKE, GUST, HEAT_INDEX, ICON_EXTD, KEY,
            MAX_TEMP, MIN_TEMP, OBS_ID, OBS_NAME, PRECIP_HRLY, PRECIP_TOTAL,
            PRESSURE, PRESSURE_DESC, PRESSURE_TEND, RH, TEMP, UV_DESC, UV_INDEX,
            VIS, WC, WDIR, WDIR_CARDINAL, WSPD, WX_ICON, WX_PHRASE
       FROM WEATHER_OBSERVATIONS W,
            WH_HOURLY E
      WHERE DATE(E.READING_TIMESTAMP) = DATE(W.OBSERATION_TIME)
        AND HOUR(E.READING_TIMESTAMP) = HOUR(W.OBSERATION_TIME);