map freely

Posto alcuni comandi pgsql che ho avuto modo di usare di recente dovendo maneggiare i formati date di Postgresql. Li metto cosi’ in ordine sparso e premetto che fanno riferimento ad un database usato per la gestione dei consumi energetici di impianti di illuminazione; pertanto alcuni commenti (personali) fanno riferimento a questo tipo di lavoro. Tuttavia vanno benissimo per qualsiasi altro db.

Sia data una tabella “tbl01″ che contiene un campo “anno” come stringa, “data01″, “data02″, “data03″, ”data04″ campi in formato date, “consumo_anno” come integer :

# comando SELECT per recuperare il valore del campo “anno” sul quale faremo le nostre operazioni:

mydb=# SELECT anno FROM tbl01 WHERE id=4;
 anno
——
 2010
(1 row)

Come si puo’ vedere si tratta di un campo varchar di 4 caratteri.

# comando SELECT che converte in numero (attualmente in formato testo) il campo “anno” della tabella “tbl01″:

mydb=# SELECT to_number(anno, ’9999′) FROM tbl01 WHERE id=4;
 to_number
———–
      2010
(1 row)

# comando SELECT per convertire in numero il campo “anno” e togliere 1:

mydb=# SELECT to_number(anno, ’9999′)-1 FROM tbl01;
 ?column?
———-
     2009
     2009
     2008
     2009
(4 rows)

# comando per convertire in data nel formato YYYYMMDD il valore del campo “anno”:

mydb=# SELECT to_date(anno,’YYYYMMDD’) FROM tbl01 WHERE id=4;
  to_date   
————
 2010-01-01
(1 row)

# calcolo del 31 dicembre dell’anno precedente a quello in oggetto (con riferimento al campo “anno”):

mydb=# SELECT to_date(anno,’YYYYMMDD’)-1 FROM tbl01;
  ?column?  
————
 2009-12-31
 2009-12-31
 2008-12-31
 2009-12-31
(4 rows)

# comando per calcolare la differenza tra una data chiamata “data1″ (inizio anno) e il 31 dicembre dell’anno precedente (giorni in eccesso inizio anno): siano data0=2008-12-29, data1=2009-01-12, data2=2009-12-31, data3=2009-12-31.

mydb=# SELECT data1-(to_date(anno,’YYYYMMDD’)-1) FROM tbl01 WHERE id=4;
 ?column?
———-
       12
(1 row)

# comando per calcolare la differenza tra il 31 dicembre dell’anno precedente e la prima data “data0″ (giorni in difetto inizio anno):

mydb=# SELECT (to_date(anno,’YYYYMMDD’)-1)-data0 FROM tbl01 WHERE id=4;
 ?column?
———-
        2
(1 row)

# comando per calcolare la differenza tra “data3″ e il 31 dicembre dell’anno in corso (giorni in eccesso fine anno):

mydb=# SELECT data3-(to_date(anno,’YYYYMMDD’)+364) FROM tbl01 WHERE id=4;
 ?column?
———-
        0
(1 row)

# comando per calcolare la differenza tra il 31 dicembre dell’anno in corso e la “data2″ (giorni in difetto fine anno):

mydb=# SELECT (to_date(anno,’YYYYMMDD’)+364)-data2 FROM tbl01 WHERE id=4;
 ?column?
———-
        0
(1 row)

===========================================================================

Supponiamo di avere due tabelle in relazione 1 a molti (tabella “a” e tabella “b”) con campo di collegamento (“id” della tabella “a” si collega a a_id della tabella “b”). Se vogliamo recuperare valori da entrambe le tabelle:

mydb=# SELECT a.campo1, b.campo2 FROM a,b WHERE a.id=b.a_id AND campo3=valore ORDER BY id;

===========================================================================

Quella che segue e’ una query su una tabella cosi’ strutturata:

# query per aggiornare la tabella delle letture (in un db che contiene le letture dei consumi di contatori elettrici) calcolando il consumo_anno nel caso in cui data_lettura0!=data_lettura1 per l’anno=’2009′:

energia_django=> UPDATE manager_letturailluminazione SET consumo_anno=(totale_energia_attiva_kwh_l3-totale_energia_attiva_kwh_l1)+((totale_energia_attiva_kwh_l1-totale_energia_attiva_kwh_l0)/(data_lettura1-data_lettura0)*(data_lettura1-(to_date(anno,’YYYYMMDD’)-1)))-((totale_energia_attiva_kwh_l1-totale_energia_attiva_kwh_l0)/(data_lettura1-data_lettura0)*(data_lettura3-(to_date(anno,’YYYYMMDD’)+364)))  WHERE anno=’2009′ AND data_lettura0!=data_lettura1;