[mysql]dati su file di testo da importare

[RaouL_BennetH]

Tempo fa mi cimentai nella realizzazione di un programmino in C che era servito prima di tutto a scopo didattico, ma ha anche avuto uno scopo pratico per alcune cose.In particolare si trattava di un lettore di badge che memorizzava su file di testo l'orario di ingresso e di uscita.

Ora, la stessa applicazione, la feci anche con visual basic e access ma rimase giusto in fase di concetto.Volevo ora fare in modo che quello che scrivo sul file di testo,venga memorizzato su un database mysql.Con la premessa che so come importare i dati da un file di testo in un db mysql,volevo un aiuto per dare una giusta impronta al database,in questo senso:

quando il badge viene passato, sul file di testo viene memorizzata questa cosa:


CODICE LETTO -> 0001 <- ; Thu Aug 18 14:05:17 2005


quindi,la prima cosa che vorrei compiere,è di importare su mysql solo:

0001 + la data e l'orario di ingresso/uscita e far calcolare in un altro campo della tabella la durata tra ingresso e uscita.In visual basic ci ero riuscito,in mysql non ho idea di come fare.Es.:



0001 ; Thu Aug 18 14:05:17 2005

0001 ; Thu Aug 18 20:05:23 2005

/*quando interrogo il db relativo al codice 0001 che sarà di pippo,dovrebbe restituirmi: */

pippo: IN = 14:05 - OUT = 20:05 TOT = 6 - Data = Thu Aug 18 2005


Poi, queste ore devono essere divise in:

Notturne - Festive - Feriali

Considerando che le notturne sono divise così:

Dalle ore 00:00 del Lunedi alle ore 06:00 del Lunedi
Dalle ore 22:00 del Lunedi alle ore 06:00 del Martedi

Fino alle 24:00 del Sabato.

Mentre invece
dalle ore 00:01 di Domenica alle 06:00 e
dalle ore 22:00 di Domenica alle 24:00

diventano notturne Festive.

Unitamente a questo, dovrei crearmi una sorta di calendario con i giorni della settimana.

Attendo con ansia i vostri aiuti :)

RaouL.

[BlueDragon]

Io su MySQL ci salverei solo i dati che hai:
codice badge e data rilevazione (ingresso/uscita).
Tutte le altre operazioni di conteggio ore sarebbe meglio le facesse il programma esterno dopo aver chiesto a MySQL i dati.

Comunque, contestualmente all'estrazione dati, se c'è un solo ingresso ed una sola uscita per un singolo badge/giorno, potresti fare così:

SELECT codiceBadge, MIN(Data_rilevazione) AS Ingresso,
MAX (Data_rilevazione) AS Uscita, (MAX(Data_rilevazione)-MIN(Data_Rilevazione)) AS Ore_Lavorate
FROM tabella
GROUP BY codiceBadge
WHERE codiceBadge= '0001'
AND date = '2005-08-18'

[RaouL_BennetH]

allora,sono riuscito ad adattare le prove fatte con "strtok" al mio programmino :)

Ora,mi ero semplicemente creato un db mysql in questo modo:



tabella_rilievi:

id int(10) - not null auto_increment
codice varchar(15) - not null
marcatura varchar(28) not null
PRIMARY KEY (id), INDEX (codice)

/*con load data local infile carico i dati presenti sul file di testo
e ottengo: */

+----+--------+---------------------------+
| id | codice | marcatura |
+----+--------+---------------------------+
| 18 | 0010 | Fri Aug 19 15:32:38 2005 |
| 20 | 0010 | Fri Aug 19 15:32:43 2005 |
| 22 | 0010 | Fri Aug 19 15:32:48 2005 |
+----+--------+---------------------------+



Il dubbio che mi assale,è che la vedo dura fargli fare dei calcoli prendendo tutta la data :help:

[BlueDragon]

Il problema principale secondo me è un altro...non avendo distinzioni tra ingresso ed uscita, nel caso tu abbia 3 rilevazioni per un singolo giorno...che fai? Hai pensato alla politica da adottare in un caso del genere?:)

Fare calcoli con le date non è un problema....ma sarebbe meglio se tu le memorizzassi come Date appunto :)
Ci sono numerose funzioni su MySQL per farsi fare la differenze o la somma tra due date o per inserirle/convertirle un po' in tutti i formati...
Oppure le memorizzi come testo e ti scrivi la tua funzioncina da usare quando le recuperi per tradurle in qualcosa di decente con cui fare i calcoli (magari il classico: "secondi dal 1970").

Qui trovi la documentazione su MySQL:
http://mirror.tomato.it/mysql/Downloads/Manual/manual-a4.pdf (mirror italiano, PDF in inglese da circa 8mb)

[RaouL_BennetH]

Il problema principale secondo me è un altro...non avendo distinzioni tra ingresso ed uscita, nel caso tu abbia 3 rilevazioni per un singolo giorno...che fai? Hai pensato alla politica da adottare in un caso del genere?:)


Si,è un caso al quale sto lavorando in modo tale da prevenirlo,cioè:

1) devo evitare che il codice venga memorizzato se passato a distanza di un tempo non ragionevole

2) evitare che in un turno di lavoro possa essere passato più di due volte


Fare calcoli con le date non è un problema....ma sarebbe meglio se tu le memorizzassi come Date appunto :)

Il fatto è che devo necessariamente memorizzarle prima in un file di testo.Il lettore non si trova sul pc dove poi verranno elaborati i dati e questo significa che non c'è un pc con mysql che gira dove c'è il lettore :(


Ci sono numerose funzioni su MySQL per farsi fare la differenze o la somma tra due date o per inserirle/convertirle un po' in tutti i formati...
Oppure le memorizzi come testo e ti scrivi la tua funzioncina da usare quando le recuperi per tradurle in qualcosa di decente con cui fare i calcoli (magari il classico: "secondi dal 1970").

ecco,qui mi servirebbe qualche esempio se possibile.Non sono riuscito a cavarne nulla di funzionante dalle prove che ho fatto sin'ora,a meno di non lasciare soltanto le ore nel file di testo senza il giorno,il mese e l'anno :(


Qui trovi la documentazione su MySQL:
http://mirror.tomato.it/mysql/Downloads/Manual/manual-a4.pdf (mirror italiano, PDF in inglese da circa 8mb)

E' identico a quello che sta sul sito ufficiale di mysql? (da li sto seguendo tutte le guide).

Thx.

RaouL.

[BlueDragon]

Il fatto è che devo necessariamente memorizzarle prima in un file di testo.Il lettore non si trova sul pc dove poi verranno elaborati i dati e questo significa che non c'è un pc con mysql che gira dove c'è il lettore :(

Nessun problema...comunque puoi fare in modo che siano messe come date.
Tu usi LOAD DATA per caricare il file su MySQL no?
Prova a vedere se semplicemente ricreando la tabella con il campo messo a data, capisce che il valore del file di testo va bene. Se per caso non lo accetta, allora usa i comandi di MySQL per aggiustarlo mentre lo inserisci.
ES:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(codice, @var1)
SET marcatura= CONVERTIINDATA("mioformato",@var1);

codice,@var1 in pratica gli dice: le due colonne del file, assegnale in questo modo: la prima alla colonna codice della tabella t1, la seconda colonna invece sulla variabile @var1.
Subito dopo il set gli dice: metti su marcatura il valore di CONVERTIINDATA("mioformato",@var1), dove CONVERTIINDATA è una funzione da cercare su MySQL in cui tu ti fai restituire una data a partire dalla variabile @var1 e dal formato "mioformato" che sarà probabilmente tipo "%g %MM %hh %QWZ" cioé una descrizione del formato di data che tu hai usato scrivendo sul file.



E' identico a quello che sta sul sito ufficiale di mysql? (da li sto seguendo tutte le guide).

Beh ho preso il link da lì, quindi direi di sì :)

[RaouL_BennetH]

Nessun problema...comunque puoi fare in modo che siano messe come date.
Tu usi LOAD DATA per caricare il file su MySQL no?
Prova a vedere se semplicemente ricreando la tabella con il campo messo a data, capisce che il valore del file di testo va bene. Se per caso non lo accetta, allora usa i comandi di MySQL per aggiustarlo mentre lo inserisci.
ES:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(codice, @var1)
SET marcatura= CONVERTIINDATA("mioformato",@var1);

codice,@var1 in pratica gli dice: le due colonne del file, assegnale in questo modo: la prima alla colonna codice della tabella t1, la seconda colonna invece sulla variabile @var1.
Subito dopo il set gli dice: metti su marcatura il valore di CONVERTIINDATA("mioformato",@var1), dove CONVERTIINDATA è una funzione da cercare su MySQL in cui tu ti fai restituire una data a partire dalla variabile @var1 e dal formato "mioformato" che sarà probabilmente tipo "%g %MM %hh %QWZ" cioé una descrizione del formato di data che tu hai usato scrivendo sul file.



Beh ho preso il link da lì, quindi direi di sì :)


Al solito,grazie mille per i preziosissimi suggerimenti :ave:
Però,anzichè far fare a mysql una conversione,non sarebbe possibile formattare la data in modo corretto già nel programma?
Stavo provando a vedere tutte le funzioni di ctime,ma non si parla di formattazione,è possibile?

[BlueDragon]

Però,anzichè far fare a mysql una conversione,non sarebbe possibile formattare la data in modo corretto già nel programma?
Stavo provando a vedere tutte le funzioni di ctime,ma non si parla di formattazione,è possibile?
Si, certo, puoi anche fare tu la conversione della data da programma.
Però MySQL sono sicuro che abbia le funzioni adatte per la conversione, c'è tutto un capitolo apposta (12.5 Date and Time Functions), mentre invece in C ho dato uno sguardo rapido alla libreria time.h (sempre su http://www.cplusplus.com/ref/) e non mi è parso di vedere ciò che serve, anche se sicuramente cercando in giro c'è chi ha già scritto la funzione che ti serve.

[RaouL_BennetH]

Si, certo, puoi anche fare tu la conversione della data da programma.
Però MySQL sono sicuro che abbia le funzioni adatte per la conversione, c'è tutto un capitolo apposta (12.5 Date and Time Functions), mentre invece in C ho dato uno sguardo rapido alla libreria time.h (sempre su http://www.cplusplus.com/ref/) e non mi è parso di vedere ciò che serve, anche se sicuramente cercando in giro c'è chi ha già scritto la funzione che ti serve.

Finito adesso di leggere tutto il capitolo relativo a date and time function :(
Non c'è nulla che trasformi questo formato:


Fri Aug 19 15:32:38 2005


in uno su cui sia possibile compiere operazioni matematiche :(

[RaouL_BennetH]

diciamo che dovrei dire a ctime o ad asctime di mettermi la data così:

19/08/2005 03:08:56

[BlueDragon]

STR_TO_DATE(str,format)
This is the reverse function of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.
Questa fa al caso tuo :)

Il formato per Fri Aug 19 15:32:38 2005 dovrebbe essere...
%a %b %e %k:%i:%s %Y

%k è 0-23...forse hai bisogno di %H (00-23). Controlla anche minuti e secondi per vedere se anche loro possono avere questo problema.
Controlla a pagina 659 della documentazione, dove c'è la megatabellona per i formati di DATE_FORMAT (valgono anche per STR_TO_DATE).
:)



Per quanto riguarda ctime e asctime:
asctime Convert tm structure to string
ctime Convert time_t value to string
Secondo la descrizione breve di quel sito non vanno bene...fanno la conversione nel verso sbagliato :)

[RaouL_BennetH]

Questa fa al caso tuo :)

Il formato per Fri Aug 19 15:32:38 2005 dovrebbe essere...
%a %b %e %k:%i:%s %Y

%k è 0-23...forse hai bisogno di %H (00-23). Controlla anche minuti e secondi per vedere se anche loro possono avere questo problema.
Controlla a pagina 659 della documentazione, dove c'è la megatabellona per i formati di DATE_FORMAT (valgono anche per STR_TO_DATE).
:)



Per quanto riguarda ctime e asctime:

Secondo la descrizione breve di quel sito non vanno bene...fanno la conversione nel verso sbagliato :)


Rieccomi :)

la funzione è quella giusta :D ma...
mi riesce di farla funzionare soltanto in interrogazione e non quando importo i dati:

cioè, se faccio così:


select str_to_date(marcatura, '%a %b %e %k:%i:%s %Y') AS NuovaData from rilievi;

//funziona ma limitatamente a quest'interrogazione


Per memorizzarla direttamente avevo provato così e in molti altri modi ma mi da sempre errore:


load data local infile '/home/raoul/dati.txt' into table rilievi
fields terminated by '-' (codice, @temp)
set orario = str_to_date(@temp, '%a %b %e %k:%i:%s %Y');

//non funziona

[RaouL_BennetH]

altra cosa,le variabili precedute da @ non me le accetta proprio :(

[BlueDragon]

Uhm..dunque..proviamo un po' di cose:
1) Ho visto che hai scritto fields terminated by '-'..hai cambiato stile nel file? (Mi pareva fosse ; prima)
2) Che version di MySQL hai? La funzione SET con LOAD DATA l'hanno implementata a partire dalla versione 5.0.3 mi pare..
Se hai una versione precedente, possiamo comunque fare un giretto per cui prima carichiamo tutto su una tabella temporanea in cui marcatura è stringa, poi usiamo str_to_date per passare dalla tabella temporanea a quella definitiva convertendo al volo da stringa a datetime :)
3) Nessuna variabile con @?
Hai provato qualche test semplice tipo:
SET @a='test';
SELECT @a;
e vedere se ti torna 'test'?
4) Oppure come avevamo detto precedentemente puoi mettere la data già formattata per bene sul file in modo che MySQL non abbia problemi a raccoglierla :)
Così dovrebbe andare bene:
0001 ; 2005-08-18 14:05:17
0001 ; 2005-08-18 20:05:23
(il campo orario di tipo datetime).

[BlueDragon]

Se non hai a disposizione SET, il giro con tabella temporanea lo puoi fare così:

CREATE TEMPORARY TABLE IF NOT EXISTS temp_rilievi
(
cod varchar(15) NOT NULL,
rilevazione varchar(30) not null
);

LOAD DATA LOCAL INFILE 'dati.txt' INTO TABLE temp_rilievi
FIELDS TERMINATED BY ';' (cod,rilevazione);

INSERT INTO tabella_rilievi
(codice,orario)
SELECT cod,str_to_date(rilevazione, '%a %b %e %k:%i:%s %Y')
FROM temp_rilievi;

DROP TABLE temp_rilievi;