SQL Campi data e ora

[23 Giu  2004]
Lavorare con i campi data e ora è una fra le operazioni più delicate per la consultazione di un database. Vediamo come comportarsi per non avere problemi in fase di consultazione.

Lavorare con i campi data e ora è una fra le operazioni più delicate per la consultazione di un database. Vediamo come comportarsi per non avere problemi in fase di consultazione. Prima di tutto i campi definiti all'interno del database devono avere precise caratteristiche di formattazione.

Rivediamo la definizione del database statistiche.mdb e della tabella "visitatori" che permette di estrarre la data e la durata in cui l'utente ha frequentato il sito.

Campo	Descrizione	Note
id_utente	contatore	Chiave primaria, definisce un utente
cod_sessione	testo	contiene il codice univoco di sessione
periodo	Data in cifre	giorno di navigazione (gg/mm/aa)
inizio	Ora estesa	ora di inizio navigazione (hh.mm.ss)
durata	Ora estesa	fine navigazione (hh.mm.ss)
num_pagine	Numerico	definisce il numero di pagine visitate
elenco	memo	definisce l'elenco delle pagine visitate.

I campi data sono una via di mezzo fra i campi testo ed i campi numerici ed è per questo che il filtraggio dei dati deve avvenire con molta cautela.

Vediamo ad esempio i dati contenuti nel database e proviamo a definire i codici necessari a filtrare i dati.

cod_utente	IP	periodo	inizio	durata	n_pag
458012718	127.0.0.1	10/04/03	20.39.59	0.00.36	24
458012719	127.0.0.1	23/04/03	20.58.35	0.00.03	3
458012720	127.0.0.1	11/05/03	21.29.27	0.15.22	17
458012721	127.0.0.1	07/06/03	21.56.59	0.39.16	1
482519782	127.0.0.1	15/06/03	22.02.36	1.45.29	3

Se cerchiamo una data precisa è possibile trattare il campo data alla stessa stregua di un campo testuale attraverso la parola riservata like. SELECT * FROM visitatori WHERE periodo like '10/04/03' Se invece cerchiamo un periodo ad esempio tutti i collegamenti fatti nel mese di aprile 2003 allora è possibile utilizzare il carattere jolly "%". SELECT * FROM visitatori WHERE periodo like '%04/03' Il risultato ottenuto per questa stringa di ricerca è:
SELECT * FROM visitatori WHERE periodo like '%04/03'

cod_utente	IP	periodo	inizio	durata	n_pag
458012718	127.0.0.1	10/04/03	20.39.59	0.00.36	24
458012719	127.0.0.1	23/04/03	20.58.35	0.00.03	3

Per parametrizzare quindi la ricerca in funzione della data occorre creare una variabile tempo concatenando le richieste dell'utente. mese=request("mese")
anno=request(anno)
tempo="%" & mese & "/" & anno
stringa_SQL="SELECT * FROM visitatori WHERE periodo like '" & tempo &"'" Un forte limite di questo codice è che dobbiamo controllare che la sintassi della data sia proprio del tipo %mm/aa.

Un'altra difficoltà? a questa sintassi è limitare il filtraggio ad un periodo ben definito per esempio dal 15/04/03 al 14/05/03 in quanto i comandi minore <, maggiore > o l'equivalente between non sono utilizzabili.

Vedremo di seguito una sintassi più elegante e pratica...

Un metodo più elegante per filtrare le date è quello di utilizzare alcune funzioni direttamente nel linguaggio SQL.

Il vantaggio più diretto che possiamo ottenere è quello di poter confrontare direttamente il mese il giorno o l'anno in formato numerico. SELECT * FROM visitatori WHERE Month(periodo)<6 AND Year(periodo)=2003 Con questa stringa di ricerca possiamo isolare tutti i record registrati prima del mese di giugno 2003 (Month(periodo)<6 AND Year(periodo)=2003); difatti il risultato ottenuto è:

cod_utente	IP	periodo	inizio	durata	n_pagine
458012718	127.0.0.1	10/04/03	20.39.59	0.00.36	24
458012719	127.0.0.1	23/04/03	20.58.35	0.00.03	3
458012720	127.0.0.1	11/05/03	21.29.27	0.15.22	17

Gli elementi chiave di questa ricerca sono le funzioni Day(periodo), Month(periodo) ed Year(periodo) dove periodo è il nome del campo del database in cui filtrare i dati.

In seguito a queste considerazioni la ricerca di un periodo ben preciso è solo una questione di definizione delle condizioni. SELECT * FROM visitatori WHERE Day(periodo)>14 AND Month(periodo)=4 AND Year(periodo)=2003 OR Day(periodo)<15 AND Month(periodo)=5 AND Year(periodo)=2003 La prima parte richiede che la data sia compresa fra il 15 ed il 30 aprile 2003 (Day(periodo)>14 AND Month(periodo)=4 AND Year(periodo)=2003) oppure (OR) fra il primo e il 14 maggio (Day(periodo)<15 AND Month(periodo)=5 AND Year(periodo)=2003).

Osservazione: il controllo dell'anno è fatto utilizzando tutte le 4 cifre.

Il risultato ottenuto è quindi il seguente.

cod_utente	IP	periodo	inizio	durata	n_pagine
458012719	127.0.0.1	23/04/03	20.58.35	0.00.03	3
458012720	127.0.0.1	11/05/03	21.29.27	0.15.22	17

Anche in questo caso la parametrizzazione della stringa permette di dare la possibilità? di scelta all'utente con un form.

In modo del tutto analogo possiamo filtrare i dati in funzione della data corrente Date(), abbiamo.... stringa_SQL = "SELECT * FROM visitatori WHERE Month(periodo)=" & Month(date())-1 & " AND Year(periodo)=" & Year(date()) Il confronto avviene fra Month(periodo) e Month(date())-1 e fra Year(periodo) e Year(date()).

Campi orario (hh.mm.ss)

Le considerazioni appena viste per i campi data valgono anche per i campi orario come inizio e durata.

Ad esempio se vogliamo sapere in quali ore c'è un maggiore flusso di utenti. La stringa SELECT * FROM visitatori WHERE Hour(inizio)=20 restituisce tutte le visite svolte alle ore 20.

cod_utente	IP	periodo	inizio	durata	n_pagine
458012718	127.0.0.1	10/04/03	20.39.59	0.00.36	24
458012719	127.0.0.1	23/04/03	20.58.35	0.00.03	3

Se associamo alla richiesta la parola riservata Count(*). SELECT Count(*) AS numero FROM visitatori WHERE Hour(inizio)=20 ed inseriamo nel codice della pagina la stampa del valore Numero contatti: , il risultato ottenuto è
Numero contatti: 2.

Per isolare tutte le navigazioni durate comprese fra i 30 minuti e minori di un'ora il codice è il seguente SELECT * FROM visitatori WHERE Hour(durata)<1 AND Minute(durata)>29 Il risultato è il seguente:

cod_utente	IP	periodo	inizio	durata	n_pagine
458012721	127.0.0.1	07/06/03	21.56.59	0.39.16	1

Inserimento valori in campo data/orario

Al contrario della consultazione l'inserimento e l'aggiornamento di valori data in un database devono avvenire come nel formato testuale (con apici singoli), come mostrato nei due esempi di inserimento e di aggiornamento.

Ricordiamo che la funzione date() (nella versione italiana) restituisce una data nel formato gg/mm/aa, mentre la funzione time() restituisce un orario nel formato hh.mm.ss.