Topic: [EXCEL] Estrazione date in formato UNIX

[Ripley]

[EXCEL] Estrazione date in formato UNIX

Capita (per fortuna non così spesso!) di dover convertire date prese da un database SQL. A me è appena capitato, e ora vi dico cosa ho fatto...

In SQL le date sono salvate come numero intero, secondo il formato tempo UNIX (detta anche rappresentazione POSIX), cioè:

Nei sistemi operativi Unix e Unix-like il tempo viene rappresentato come offset in secondi rispetto alla mezzanotte (UTC) del 1º gennaio 1970 (detta epoca).

Per approfondire, vedi anche
https://it.wikipedia.org/wiki/Epoca_(calendario)
https://it.wikipedia.org/wiki/Anno_0

Tanto per fare un esempio a caso, una data del DB mi si presentava così:
1396384393820

Il problema originale era questo: ho usato per qualche tempo un programma sul cellulare in cui memorizzavo i rifornimenti di benzina, così da tenere d'occhio i costi. Il programma si chiama(va) Fuelstat.
Sabato scorso ho fatto il reset del mio cellulare (che stava sfaciolando di brutto), e poi mi sono reso conto con orrore che il suddetto software non esisteva più sul Play Store 
Occorreva trovare un sostituto, e per ora ho scaricato un certo Fuelio, che mi pare carino ^®™.

Naturalmente prima del reset avevo fatto un backup dei dati
Da questo sito http://sqlitebrowser.org/ ho scaricato "DB Browser for SQLite" (un editor di DB SQL) e ho estratto tabelle e dati in formato CSV, quindi ho aperto il file in Excel.

A me piace molto giocare con Excel, e questo è il risultato.

Questi sono i primi 8 record dei dati in CSV:

Codice: [Seleziona]

_id,distance,liters,cost,cost_liters,note,date,full,vehicle_key
1,53250,29.7000007629394,50.0,1.68350160121918,,1396384393820,0,1
2,53463,39.0299987792969,66.0,1.69100701808929,,1396816515327,1,1
3,53841,43.9500007629395,75.0,1.70648455619812,,1398717407693,1,1
4,53952,16.7399997711182,30.0,1.79211473464966,,1398976686412,1,1
5,54535,42.3800010681152,72.0,1.69891452789307,,1399667962585,1,1
6,54899,16.6499996185303,30.0,1.80180180072784,,1400704859848,0,1
7,55006,38.2700004577637,65.0,1.69845831394196,,1401223323732,1,1
8,55436,43.1100006103516,80.0,1.85571789741516,,1401741761848,1,1

Se usavo l'importazione guidata di Excel per importare il CSV, non so perchè ma alcuni campi venivano convertiti in modo strano, quindi ho fatto un'importazione..."a mano". Ho aperto direttamente il file in Excel, e questo mi ha messo tutti i dati nella colonna A, una riga per cella.

Quindi in A1 avevo tutte le intestazioni, con i nomi dei campi

Codice: [Seleziona]

_id,distance,liters,cost,cost_liters,note,date,full,vehicle_key
In A2 la prima riga di dati

Codice: [Seleziona]

1,53250,29.7000007629394,50.0,1.68350160121918,,1396384393820,0,1
...e così via

Senza starla a fare troppo lunga, con il TROVA e la "virgola" come parametro, ho inserito nelle celle adiacenti (B1, C1, ecc) tutte le posizioni in cui compariva la virgola:

B	C	D	E
=TROVA(",";$A1;1)	=TROVA(",";$A$1;B1+1)	=TROVA(",";$A$1;C1+1)	...e così via...

Ho copiato questa riga di formule giù fino in fondo, poi nel foglio seguente, usando quegli indici insieme a STRINGA.ESTRAI, ho tirato fuori le singole stringhe.

Alla fine del gioco (che comunque allego qui sotto), la formula per convertire la data in formato leggibile è:

Codice: [Seleziona]

=TESTO(((((G2/1000)/60)/60)/24)+DATA(1970;1;1)+(2/24);"gg/mm/aaaa")La cella G2 contiene la data da convertire.

In pratica:
- si convertono i secondi espressi dalla data Unix in numero di giorni
- a questo numero, aggiungiamo il valore della data del 1 gennaio 1970
- si corregge per il nostro fuso orario, aggiungendo 2 ore
- alla fine la formula TESTO esterna converte e formatta il tutto

Rispetto alla formula indicata dal sito spreadsheetpage.com, ho dovuto aggiungere una divisione per 1000, in quanto la mia data SQL conteneva anche i millesimi di secondo.


Fonti: Timestamp - UnixTime.it - Spreadsheetpage.com

Codice: [Seleziona]

Copyright Immagine di Chlor on da.Wikipédia Antaya on fr.Wikipédia (update on Commons) http://en.wikipedia.org/wiki/Image:1000000000seconds.jpg CC BY-SA 3.0, https://commons.wikimedia.org/w/index.php?curid=3297884

[LG965]

  sempre utile! Hai fatto bene a riportare tutto qui.

[Red Deadly]

Ciao Ugo,

non uso Excel, ma non bastava cambiare il tipo di dato delle celle?
Prova ad aprire, se ce l'hai ancora, il file csv con Open Office (o Libre), secondo me non dovrebbe darti problemi.

Ciao!

[Ripley]

Magari fosse così semplice: se consideriamo una qualsiasi data fissa, questa presenta due valori diversi tra Unix ed Excel, in quanto usano due sistemi di calcolo diversi.

Unix, come già scritto più su, parte dalla mezzanotte del 1° gennaio 1970 UTC, Excel usa due sistemi (diversi tra Windows e Macintosh). In Windows usa il "sistema 1900", cioè parte a contare dal 1° gennaio 1900.

Unix, contando i secondi, genera numeri enormi. Matematicamente è un numero intero.
Excel invece conta il numero di giorni nella parte intera, e l'ora nella parte decimale.

Comunque ho allegato il CSV (zippato) nel primo post se gli vuoi dare un'occhiata, anche se già ne avevo incollato il contenuto.

Per approfondire sulla data di Excel
https://support.microsoft.com/it-it/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel

[Red Deadly]

Hai ragione, anche con Open Office c'è lo stesso problema di conversione e ci vuole per forza una formula in più per convertire il timestamp, a meno che tu non cambi la select che ti esporta i dati dal db.

Comunque ti confermo che con Open Office Calc il csv si apre correttamente.
Infatti ho scaricato il file, aperto con Calc, ho aggiunto nella colonna J la formula =((((G2/1000)/60)/60)/24)+DATA(1970;1;1)+(2/24) ho trascinato la formula per tutte le righe e ho cambiato a tutta la colonna il formato della cella impostandola a tipo Data.

Quindi grazie della formula di conversione! 

P.S. A titolo informativo, per Firefox c'è un plugin che si chiama SQLite Manager per la gestione dei db SQLite

[Ripley]

...P.S. A titolo informativo, per Firefox c'è un plugin che si chiama SQLite Manager per la gestione dei db SQLite

Non sapevo, grazie!

Per continuare (anche se parecchio off-topic):

...ho usato per qualche tempo un programma sul cellulare in cui memorizzavo i rifornimenti di benzina...Il programma si chiama(va) Fuelstat...Sabato scorso ho fatto il reset del mio cellulare...e poi mi sono reso conto con orrore che il suddetto software non esisteva più sul Play Store...Occorreva trovare un sostituto, e per ora ho scaricato un certo Fuelio, che mi pare carino ^®™...

Direi molto più che carino!
Tiene traccia di tutti i costi di gestione di una macchina, e non dei soli consumi di carburante. Inoltre indica i distributori più economici in zona, e per dirne solo un'altra, fa il backup su Dropbox (o Google Drive) direttamente in CSV!