PARTE TERZA Formattazione delle date e delle ore. DATE_FORMAT(date,format) - TIME_FORMAT(time,format) - DATE_FORMAT() e' una importante funzione per l'estrazione di uno qualsiasi dei valori descritti nella prossima tabella. L'argomento "date" puo' essere una funzione, un campo contenente una data valida, una data/ora esplicita sia in formato stringa che in formato numerico. L'argomento "format" va inserito tra apici e puo' contenere un numero a piacere di opzioni. Se il valore fornito nel primo argomento non contiene tutti i dati richiesti dall'argomento "format", i dati mancanti saranno resi con un valore di default oppure si ricevera' un NULL. Per estrarre l'ora esiste una funzione specifica: TIME_FORMAT(time,format). Quindi se vogliamo ricavare un formato solo orario dovremo utilizzare quest'ultima funzione. Torniamo a DATE_FORMAT(). DATE_FORMAT() puo' essere utilizzato per l'inserimento dei dati nella tabella, ma se tenteremo di memorizzare un formato data italiano in un campo formato date/time, l'operazione verra' eseguita se il campo e' vuoto (cioe' 0000-00-00) ma inserira' la data nel formato standard. Se invece il campo gia' conteneva una data valida, questa non verra' sovrascritta. In cambio DATE_FORMAT() puo' essere profiquamente utilizzato per memorizzare in un campo formato VARCHAR una data col formato italiano. Potremo utilizzare la data come se fosse una stringa qualunque ma non avremo la possibilita' di fare operazioni date/time su di essa. Esempio: Abbiamo un campo data in formato VARCHAR(12): UPDATE tabella SET data= date_format(NOW(),"%d-%m-%Y") WHERE id = $id Verra' memorizzata la data odierna nel formato gg-mm-aaaa come stringa. Potra' essere estratta ed utilizzata come', ma non potra' essere argomento di funzioni data MySQL. Quindi la funzione DATE_FORMAT() estrae da un formato valido di DATE/TIME uno dei valori elencati nella seguente tabella utilizzando la chiave '%'. code: ---------------------------------------------------------------- | %M | Nome del Mese (in inglese) (January..December) | %W | Nome del giorno (idem) (Sunday..Saturday) | %D | Giorno del mese con suffisso all'inglese (1st, 2nd, 3rd, etc.) | %Y | Anno, numerico, 4 digits | %y | Anno, numerico, 2 digits | %X | Settimana dell'anno, Domenica primo giorno della settimana, numerico, | | 4 digits, usato con '%V' | %x | Settimana dell'anno, Lunedi' primo giorno della settimana, numerico, | | 4 digits, usato con '%v' | %a | Nome del giorno abbreviato (Sun..Sat) | %d | Giorno del mese, numerico (00..31) ( ma il giorno 00 non esiste) | %e | Giorno del mese, numerico (0..31) ( idem) | %m | Mese, numerico (01..12) | %c | Mese, numerico (1..12) | %b | Nome del mese abbreviato (Jan..Dec) | %j | Giorno dell'anno (001..366) | %H | Ora (00..23) | %k | Ora (0..23) | %h | Ora (01..12) | %I | Ora (01..12) | %l | Ora (1..12) | %i | Minuti, numerico (00..59) | %r | Orario, Ora-12 (hh:mm:ss [AM/PM] | %T | Orario, Ora-24 (hh:mm:ss) | %S | Secondi (00..59) | %s | Secondi (00..59) | %p | AM or PM | %w | Giorno della Settimana, numerico (0-6) (0=Domenica 1=Luned́..6=Sabato) | %U | Settimana (00..53), dove Domenica e' il primo giorno Settimana | %u | Settimana (00..53), dove Luned́ e' il primo giorno Settimana | %V | Settimana (01..53), dove Domenica e' il primo giorno Settimana.Usato con '%X' | %v | Settimana (01..53), dove Luned́ e' il primo giorno Settimana. Usato con '%x' | %% | Carattere wilcard. Stampera' qualunque carattere non in elenco. ---------------------------------------------------------------------- Potete vedere molti esempi dell'uso di DATE_FORMAT() in varie parti del documento. SELECT DATE_FORMAT(NOW(), "%d-%m-%Y %H:%i:%s") as data => 26-06-2003 14:29:33 EXTRACT(type FROM date) - Estrae da una data il tipo di valore descritto in type. Le opzioni a disposizione sono quelle della tabella associata alle operazioni possibili sulle date (vedi sotto) di cui condivide la tipologia. SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03") AS DATA => 199907 SELECT EXTRACT(DAY_MINUTE FROM NOW()) AS DATA => 230215 ddmmss Somma e sottrazione del tempo. Ci sono alcuni metodi per la somma e sottrazione di tempo in MySQL. Sono disponibili le seguenti funzioni a partire da MySQL 3.22. A partire da MySQL 3.23 e' possibile usare + e - invece delle funzioni DATA_ADD() e DATE_SUB. Anche EXTRACT e' disponibile solo dalla ver.3.23. Vediamo insieme. DATE_ADD(data, INTERVAL espressione tipo) funzione identica a: ADDDATE(data, INTERVAL espressione tipo) DATE_SUB(data, INTERVAL espressione tipo) funzione identica a: SUBDATE(data, INTERVAL espressione tipo) I valori dell'argomento "data" ammessi sono quelli di date, datetime o espliciti. Il valore di espressione e' un INT per TIPO = YEAR, MONTH, DAY, MINUTE, SECOND mentre per i TIPI multipli (es: DAY_MINUTE) sara' una stringa composta da piu' INT separati da uno spazio: es.: SUBDATE(NOW(),INTERVAL "2 12 30" DAY_MINUTE) cioe', sottrae alla data corrente 2 giorni, 12 ore e 30 minuti I valori ammessi in TIPO sono i seguenti: code: +-------------------------------------------------+ | VALORE 'TIPO' | FORMATO ATTESO | |----------------+--------------------------------| | SECOND | SECONDS | | MINUTE | MINUTES | | HOUR | HOURS | | DAY | DAYS | | MONTH | MONTHS | | YEAR | YEARS | | MINUTE_SECOND | "MINUTES:SECONDS" | | HOUR_MINUTE | "HOURS:MINUTES" | | DAY_HOUR | "DAYS HOURS" | | YEAR_MONTH | "YEARS-MONTHS" | | HOUR_SECOND | "HOURS:MINUTES:SECONDS" | | DAY_MINUTE | "DAYS HOURS:MINUTES" | | DAY_SECOND | "DAYS HOURS:MINUTES:SECONDS" | +----------------+--------------------------------+ Come si potra' vedere usare le funzioni DATE_ADD(), DATE_SUB oppure + o - si otterra' lo stesso risultato (ripeto: a partire da MySQL 3.23). SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH) AS DATA => DATA = 2003-06-26 15:49:13 SELECT ADDDATE(NOW(),INTERVAL 1 MONTH) AS DATA => DATA = 2003-06-26 15:50:10 SELECT NOW() + INTERVAL 1 MONTH AS DATA => DATA = 2003-06-26 15:51:11 SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR) AS DATA => DATA = 2002-05-26 15:52:43 SELECT SUBDATE(NOW(),INTERVAL 1 DAY) AS DATA => DATA = 2003-05-25 15:54:13 SELECT NOW() - INTERVAL 1 MONTH AS DATA => DATA = 2003-04-26 15:56:31 Vediamo alcuni esempi di utilizzo: SELECT "2002-12-25" + INTERVAL 6 MONTH AS data => 2003-06-25 SELECT ADDDATE("2002-12-25",INTERVAL "4 3" YEAR_MONTH)AS DATA => 2007-03-25 SELECT "2002-12-25" + INTERVAL "4 3" YEAR_MONTH AS DATA => 2007-03-25 SELECT DATE_FORMAT(NOW() + INTERVAL "10 12" DAY_HOUR,"%d-%m-%Y %H:%i:%s") as data => 06-06-2003 04:27:35 Sommare 10 giorni e mezzo alla data di oggi SELECT * FROM tabella WHERE data >= NOW() - INTERVAL "2 12" DAY_HOUR; Rendera' i records con data da due giorni e mezzo fa fino al momento attuale. TO_DAYS(data) FROM_DAYS(data) L'utilizzo di queste due funzioni deve essere inteso per le date seguenti l'introduzione del calendario gregoriano, cioe' dopo il 1582. E' molto comodo per il calcolo del numero dei giorni tra due date. TO_DAYS() prende in considerazione una data come paramentro e rende il numero dei giorni a partire dall'anno I d.C. FROM_DAYS() fa l'opposto: da un numero di giorni rende la data attuale, con lo stesso riferimento. SELECT TO_DAYS(NOW()) AS giorni => 731726 N. dei giorni trascorsi dall'anno Id.C. SELECT FROM_DAYS(731726) AS data => 2003-05-26 SELECT TO_DAYS("2003-12-25") - TO_DAYS("2003-05-24") AS natale => 215 [i]N. dei giorni che mancano a Natale. SELECT FROM_DAYS(TO_DAYS(NOW() - INTERVAL 1000 DAY)) AS DATA => 2000-08-29 La data di 1000 giorni fa SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()- INTERVAL 1000 DAY)),"%d-%m-%Y") AS DATA => 29-08-2000 Ecco la stessa data in formato italiano SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()+ INTERVAL 1000 DAY)),"%d-%m-%Y") AS DATA => 20-02-2006 Ed ecco la data che avremo fra mille giorni UNIX_TIMESTAMP() UNIX_TIMESTAMP(date) FROM_UNIXTIME(unix_timestamp) FROM_UNIXTIME(unix_timestamp,format) Il TIMESTAMP UNIX e' il numero dei secondi intercorsi dal 1 gennaio 1970. Un controllo di base viene effettuato su anno (1970-2037), mese (01-12), giorno (01-31). Al di fuori di questi valori la funzione rende un valore nullo (zero). UNIX_TIMESTAMP() senza il parametro facoltativo "data" restituisce l'ora UNIX corrente. FROM_UNIXTIME() e l'opposto del primo. Da una ora UNIX rende una data. Anche per questa funzione possiamo ottenere il formato numerico della data sommando 0 oppure moltiplicando per 1 la data stessa. Con l'opzione format possiamo scegliere il formato di visualizzazione che piu' ci e' congeniale, utilizzando le stesse opzioni della funzione DATE_FORMAT. SELECT UNIX_TIMESTAMP(NOW()) AS DATA => 1053649881 SELECT FROM_UNIXTIME(946897200) AS DATA => 2000-01-03 12:00:00 SELECT FROM_UNIXTIME(946897200) * 1 AS DATA => 20000103120000 SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),"%d %m %Y %h:%i:%s") AS DATA => 23 05 2003 03:58:57 I seguenti due gruppi di funzioni non possono essere considerati vere funzioni di calcolo DATA/TIME. Il primo calcola esclusivamente mesi, il secondo gruppo somma o rende valori TIME senza tenere conto dei valori DATE. Le ore eccedenti alle 24 non verranno trasformate in giorni ma sommate/sottratte fino al range +/-838:59:59. PERIOD_ADD(P,N) - Somma al periodo P, N mesi. Accetta il formato YYMM o YYYYMM e ritorna un valore YYYYMM. Somma esclusivamente mesi. SELECT PERIOD_ADD(200211,2); => 200301 = YYYYMM PERIOD_DIFF(P1,P2) - Rende il numero dei mesi tra due date (P1 e P2). Accetta il formato YYMM o YYYYMM. SELECT PERIOD_DIFF(200305,197807); => 298 mesi Per entrambe le funzioni va annotato che non si tratta di un formato date valido, ma che hanno valenza INT. Sono funzioni di calcolo per mesi e nulla piu'. TIME_TO_SEC(ora) - SEC_TO_TIME(secondi) - Time_to_sec restituisce il numero dei secondi da un'ora indicata (inizio della conta ora 00:00:00). Sec_to_time fa l'opposto. Rende l'ora a partire da un numero di secondi. SELECT TIME_TO_SEC("01:00:00") => 3600 secondi SELECT TIME_TO_SEC(NOW()) => 39600 secondi ->(erano le ore 11:00:00) SELECT SEC_TO_TIME("39600") => 11:00:00 SELECT SEC_TO_TIME("39600") + 0 => 110000 HHMMSS in formato numerico SELECT TIME_TO_SEC("-838:59:59") => -3020399 Count-Down Non si e' riusciti a stabilire il valore max di ore o secondi convertibili. Non sono ufficializzati range di valori. Nelle prove si sono raggiunti valori elevatissimi validi, per crollare senza apparente ragione a risposte con valori fantasiosi. Sempre valido il risultato invece, non superando il range orario del formato TIME (-838:59:59 up to 838:59:59). Segue uno script php con l'esempio di vari possibili utilizzi delle funzioni descritte. Puo' essere utile come memorandum e per provare rapidamente quale funzione sia piu' congeniale ad una data esigenza. Il secondo script php contiene alcuni esempi di come sia possibile raggruppare in funzioni la traformazione dei dati nelle forme a noi piu' congeniali. Ciao a tutti, Piero Ps: Sono graditi feedback. Bibliografia: http://www.mysql.com/doc/en/index.html