costruzione di query x individuare fasce orarie ben precise in un db csv in cui c'è la colonna "ora"

[cirospat]

ho un db sugli incidenti a Palermo:

https://docs.google.com/spreadsheets/d/1rpOadyp3npcOfJfFJbGjFNnLpJHF8q5fnxde_kMneMs

derivante da un dataset del comune di Palermo appena pubblicato = http://www.comune.palermo.it/opendata_dld.php?id=375

nella colonna D c'è l'ora in formato  2.30

ma non è nel formato "datetime" 2:30:00

Io vorrei fare una query che mi tira fuori tutti gli incidenti dalle 0.00 alle 6.00 - dalle 6.01 alle 12.00 - dalle 12.01 alle 18.00 - dalle 18.01 alle 24.00

Mi chiedo:

1. per la Query da costruire se la sintassi di questo tipo è esatta: D IS FROM 0.00 TO 7.00 oppure altre sintassi
2. come trasformare gli orari della colonna D nella colonna E da 2.30 a 2:30:00

Tutto questo per rendere i dati usabili per infografiche o mappe.

Grazie per i contributi didattici

[andy]

Ciao Ciro,
ho fatto una copia del tuo foglio che trovi qui.

In questa ho aggiunto una colonna per ottenere la data formattata “YYYY-MM-DD” (anno in 4 cifre, mese e giorno in due cifre). Per la prima riga ad esempio:

=DATEVALUE(YEAR(C2)&"-"&MONTH(C2)&"-"&DAY(C2))

Poi ho creato una colonna in formato datetime. E’ necessario perché come trovi scritto nella documentazione i tipi di dati supportati per una query sono “string, number, boolean, date, datetime and timeofday”. Il datetime è espresso in questo modo yyyy-MM-dd hh:mm:ss, quindi ad esempio così: “2015-01-01 02:30:00”.

Nella colonna per il datetime:

=DATEVALUE(K2)+TIMEVALUE(I2)

Non ti resta che fare la query usando le funzioni legate al tempo. Se vuoi ad esempio tutti gli incidenti del 2015 avvenuti dopo le 2:30 del 19 giugno, la query sarà:

select A,B,C,D,E,F,G,H,I,J,K,L where L > datetime '2015-06-19 02:30:00'

Gli URL (con l’encoding dei caratteri applicato) per avere l’output rispettivamente in HTML e CSV saranno:

https://docs.google.com/spreadsheets/d/1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o/gviz/tq?tqx=out:html&tq=select+A,B,C,D,E,F,G,H,I,J,K,L+where+L+%3E+datetime+%272015-06-19+02:30:00%27&gid=636418541

http://spreadsheets.google.com/tq?tqx=out:csv&tq=select%20A%2CB%2CC%2CD%2CE%2CF%2CG%2CH%2CI%2CJ%2CK%2CL%20where%20L%20%3E%20datetime%20%272015-06-19%2002%3A30%3A00%27&key=1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o&gid=636418541

Fammi sapere

​

[andy]

Ciro,

mi sono reso conto di non avere risposto alla tua domanda, ma ad un'altra simile.

Per stasera mi fermo, ma la strada è quasi la stessa.

[andy]

Ciro,

2016-06-17 11:47 GMT+02:00 cirospat <ciro...@gmail.com>:

• per la Query da costruire se la sintassi di questo tipo è esatta: D IS FROM 0.00 TO 7.00 oppure altre sintassi

ho visto che hai creato la colonna in un formato standard, che può essere usate con la funzione timeofday.

La query per tutti gli incidenti tra le 00:00:00 e le 07:00:00 è:

select A,B,C,D,E,F,G,H,I,J,K,L where I > timeofday '00:00:00' AND I < timeofday '07:00:00' 

In HTML e CSV rispettivamente:

https://docs.google.com/spreadsheets/d/1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o/gviz/tq?tqx=out:html&tq=select+A,B,C,D,E,F,G,H,I,J,K,L+where+I+%3E+timeofday+%2700:00:00%27+AND+I+%3C+timeofday+%2707:00:00%27+&gid=636418541

http://spreadsheets.google.com/tq?tqx=out:csv&tq=select%20A%2CB%2CC%2CD%2CE%2CF%2CG%2CH%2CI%2CJ%2CK%2CL%20where%20I%20%3E%20timeofday%20%2700%3A00%3A00%27%20AND%20I%20%3C%20timeofday%20%2707%3A00%3A00%27%20&key=1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o&gid=636418541

Saluti

​

[cirospat]

grazie Andrea

hai più che chiarito i miei dubbi e soddisfatto il mio fabbisogno di conoscenza su questo argomento.

Ho capito come ottenere il YYYY-MM-DD 

=DATEVALUE(YEAR(C2)&"-"&MONTH(C2)&"-"&DAY(C2))

e il datetime=

=DATEVALUE(K2)+TIMEVALUE(I2)

Francesco Passantino aveva creato (lo stesso giorno che ho pubblicato il thread qui)  le 2 colonne sul dataset da me creato

orario_convertito

classe_oraria

e con la colonna "classe oraria" ha reso disponibile un dato riferito al quarto d'ora su cui poter effettuare query specifiche.

Mi interessava questo perchè è mia intenzione creare una mappa uMap con layer per diverse classi di orari di incidenti.

Il tuo intervento ha altresì chiarito la sintassi esatta per effettuare la query per ulteriori utilizzi (vedi es. mappe su uMap, o infografiche).

Grazie Andrea e grazie Francesco pe ril vostro tempo dedicato,... non appena riuso i dati di queste colonne del db pubblico qui il frutto del riuso (mappa uMap sicuramente)

Ciro

______