Date e ore: conversione tra tipi dati diversi

Last Updated on 5 Aprile 2020 by Roberto De Pedrini

Perché usare il tipo dati Data e Ora

Il tipo dati Date è stato introdotto nella V2R3 (ovvero all’incirca nel 1994), ma ancora oggi purtroppo sembra un tipo dati sconosciuto e “pericoloso” da usare.

Un po’ per tradizione e un po’ per pigrizia le date nelle tabelle del DB2 non sono altro che numeri scritti nei formati più svariati gestiti interamente dalla logica applicativa nei programmi.
Ma essendo per l’appunto nient’altro che campi numerici il DB2 ignora del tutto che il contenuto del campo sia una data. Il campo è un surrogato di una data. Quindi tutta la fatica di gestire correttamente le date è demandata alle capacità del programmatore per nulla sfruttando le potenzialità del DB2.

Conversione da data a altri tipi dati

Esistono numerosi metodi diversi per operare con le date: codici operativi e funzioni RPG, funzioni SQL, API, comandi di sistemi operativo: almeno 98 metodi!

Il requisito per poter usare questo tesoro di codici operativi e funzioni è che le variabili o i campi siano di tipo Data e Ora e non dei surrogati.

E’ importante quindi saper convertire una variabile o campo dal tipo dati alfanumerico o numerico al tipo dati Data e Ora e viceversa.

Da tipo data a tipo alfanumerico

La funzione %char converte una data, ora o timestamp in un campo alfanumerico
%char(date|time|timestamp {: format})
Se il primo parametro è una costante, la conversione viene eseguita a tempo di compilazione.
Il secondo parametro rappresenta il formato della data, ora o timestamp restituito. Nel risultato sono inclusi i separatori a meno che si aggiunga 0 al formato (p.es. *iso0).

Per esempio:

 dcl-s DataAlfa char(10);
 dcl-s MiaData date inz('2019-08-29');

 DataAlfa = %char(MiaData: *ISO0); // 20190829 

*iso0 indica che il formato sarà YYYYMMDD senza separatori. Invece *iso prevederebbe l’uso dei separatori.

ATTENZIONE: nel manuale ILE RPG reference nel capitolo della funzione %char è scritto un esempio per convertire un campo timestamp in alfanumerico senza separatori utilizzando nel secondo parametro la keyword *iso&; in realtà bisogna utilizzare la keyword *iso0.

Da tipo data a tipo numerico

La funzione %dec converte una data in campo numerico.

%dec(date time or timestamp expression {:format})

Il parametro format è disponibile da V5R3.

Per esempio:

dcl-s yyyymmdd packed(8);
dcl-s ddmmyy packed(6);
dcl-s MiaData date(*USA) inz(d'2019-10-20');

yyyymmdd = %dec(MiaData : *iso);
ddmmyy = %dec(MiaData : *dmy); 

Si può convertire una data in campo numerico anche utilizzando le funzioni %char e %uns. Per esempio:

 yyyymmdd = %uns(%char(MiaData : *iso0)); 

Può tornare comodo anche la funzione SQL varchar_format. P.es.:

  -- da data a campo numerico di 8 cifre
dec(varchar_format(current date), 'YYYYMMDD'), 8)
-- da data a campo numerico di 7 cifre
dec(varchar_format(current date), 'YYYYMMDD'), 8) - 19000000 

Anche la funzione SQL dec consente di effettuare la conversione da data/ora a campo numerico. P.es.:

dec(MyDate)
dec(MyTime)
dec(MyTimestamp)
dec(MyTimestamp, 26, 12)
dec(date(MyTimestamp))
dec(time(MyTimestamp), 6, 0) 

Conversione da altro tipo a data/ora/timestamp

Le funzioni %date, %time, %timestamp convertono una variabile in data, ora o timestamp. Introdotte nella V5R1 sostituiscono i codici operativi MOVE, MOVEL .

%DATE

 %date{(expression{:date-format})} 

Se non viene specificato il primo parametro, viene restituita la data corrente di sistema (N.B. NON la data del job come restituirebbe *DATE).
Il secondo parametro specifica il formato della data in input; se non specificato si assume sia *iso. Se il primo parametro è un timestamp, oppure *DATE o UDATE il secondo parametro non è necessario.
La data restituita è sempre in formato *iso.

%TIME

 %time{(expression{:time-format})} 

Se non viene specificato il primo parametro, viene restituita l’ora corrente di sistema.
Il secondo parametro specifica il formato dell’ora in input; se non specificato si assume sia *iso. Se il primo parametro è un timestamp, il secondo parametro non è necessario.
L’ora restituita è sempre in formato *iso.

%TIMESTAMP

%timestamp{(char-num-expression { : *ISO|*ISO0 : {fractional-seconds}})}
%timestamp{(date-timestamp-expression { : fractional-seconds})} 

Se non viene specificato il primo parametro o viene impostato a *sys, viene restituito il timestamp corrente di sistema (con solo le prime 3 cifre della porzione frazionale dei secondi).
Se il primo parametro è alfanumerico, si può specificare il formato nel secondo parametro.
Se il primo parametro è numerico l’unico formato ammesso è *iso.
Il timestamp restituito è sempre in formato *iso.

SQL way

Le funzioni precedenti sono ottime in un programma RPG, ma se si avesse bisogno di effettuare la conversione in un’istruzione SQL?
Nulla vieta di creare una funzione scalare SQL che con linguaggio SQL/PL o chiamando un programma RPG effettui la conversione.
Vediamo un esempio di conversione dal formato AAAAMMGG (campo packed di 8 cifre) a un tipo dati data. L’esempio poi può essere poi adattato ad effettuare la conversione con formati diversi o migliorato per gestire con un’unica funzione tutti i possibili casi.
La funzione che creeremo si basa sulla funzione SQL date che restituisce un campo di tipo data da un valore (numerico o alfanumerico) che rappresenta una data o un timestamp.

Istruzione SQL per creare la funzione cvtDate:

create or replace function CVTDATE (InpData dec(8, 0) )
 returns date
 language sql
 contains sql
 no external action
 global deterministic
 return null on null input
 not fenced
 return date(left(char(InpData), 4) concat '-' concat substr(char(InpData), 5, 2) concat '-' concat substr(char(InpData), 7, 2)); 

Altre funzioni SQL utili per effettuare la conversione del tipo data/ora/timestamp: time, timestamp, timestamp_iso, timestamp_format.

La funzione time restituisce un tipo dati time di un valore che rappresenta un’ora o un timestamp. Se il valore è una data time restituisce come ora la mezzanotte.

La funzione timestamp restituisce un tipo dati timestamp di un valore che rappresenta una data o un timestamp. Si può specificare solo il primo parametro come rappresentazione di un valore data o timestamp ed eventualmente nel secondo parametro si specifica un intero per indicare la precisione della porzione frazionale del timestamp. Altrimenti si può specificare nel primo parametro un valore che rappresenta una data e nel secondo parametro un valore che rappresenta un’ora.

Simile alla precedente è la funzione timestamp_iso che però può ricevere solo un parametro che rappresenta una data, un’ora o un timestamp. Se si specifica un valore che rappresenta un’ora, la porzione data viene impostata in base al registro CURRENT DATE.

La funzione timestamp_format (o le equivalenti to_date, to_timestamp) converte una stringa string_expression con formato format_string in un timestamp:

 timestamp_format(string_expression, format_string, precision-constant) 

I separatori consentiti in format_string sono: -, ., /, ,, ', ;, :, blank.
Gli elementi del formato specificabile in format_string sono riassunti nella tab. 53 (p. 619) del manuale SQL reference 7.3. Alcuni esempi:

values timestamp_format('1999-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
values timestamp_format('15/12/98 13:48', 'DD/MM/RRRR HH24:MI');
values timestamp_format('9-3-2004 8:02', 'DD/MM/RRRR HH24:MI');
values timestamp_format('190718', 'YYMMDD');
values timestamp_format('2015-09-04 13:00:01', 'YYYY-MM-DD HH24:MI:SS');
values timestamp_format('Settembre:2015:12 01:00:13', 'Month:YYYY:DD SS:MI:HH24');
values timestamp_format('Nov:15:03 15', 'Mon:RR:DD HH24');
...where timestamp_format(char(DataPacked8), 'YYYYMMDD') between current date and (current date + 30 days)
...where timestamp_format(char(DataPacked7+19000000), 'YYYYMMDD') between current date and (current date + 30 days) 

Tabella di conversione delle date

Un altro metodo di “conversione” di una data memorizzata nel DB2 in un campo con tipo dati non appropriato consiste nell’utilizzare una tabella di conversione delle date.
Si tratta di una tabella “calendario” che contiene un record per ogni giorno in uno specifico range di date.
Ogni record contiene la stessa data rappresentata in diversi modi (data giuliana, data “vera”, giorno della settimana, data YYYYMMDD, data DDMMYY, excel…).
E’ documentata nel redbook SG24-7214-01 “Getting Started with DB2 Web Query for i” (appendix B p. 542-552).

La logica di utilizzo di questa tabella di conversione è metterla in join con la tabella contenente la data “legacy” tramite il campo più appropriato (p.es. per una data legacy memorizzata in un campo packed 8,0 in formato YYMMDD si utilizza DC_YYMD_DEC) e quindi utilizzare nel resto della query il campo data “vero” DC_DATE.

Garantito al 100% che questa tecnica offre prestazioni migliori rispetto a qualsiasi formula di conversione, soprattutto se il campo è utilizzato nella clausola where di una istruzione SQL.

Leggi l’intero articolo e molti altri sul sito https://www.markonetools.it