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

[Ciro Spataro]

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

--
Questa è la nuova mailing list di OpenDataSicilia. Un archivio della vecchia è disponibile qui http://opendatasicilia.65952.x6.nabble.com/
---
Hai ricevuto questo messaggio perché sei iscritto al gruppo "opendatasicilia" di Google Gruppi.
Per annullare l'iscrizione a questo gruppo e non ricevere più le sue email, invia un'email a [hidden email].
Per postare in questo gruppo, invia un'email a [hidden email].
Visita questo gruppo all'indirizzo https://groups.google.com/group/opendatasicilia.
Per visualizzare questa discussione sul Web, visita https://groups.google.com/d/msgid/opendatasicilia/831a0644-ea58-482f-b88b-0660a80fd748%40googlegroups.com.
Per altre opzioni visita https://groups.google.com/d/optout.

[Andrea Borruso]

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

​

--
Questa è la nuova mailing list di OpenDataSicilia. Un archivio della vecchia è disponibile qui http://opendatasicilia.65952.x6.nabble.com/
---
Hai ricevuto questo messaggio perché sei iscritto al gruppo "opendatasicilia" di Google Gruppi.
Per annullare l'iscrizione a questo gruppo e non ricevere più le sue email, invia un'email a [hidden email].
Per postare in questo gruppo, invia un'email a [hidden email].
Visita questo gruppo all'indirizzo https://groups.google.com/group/opendatasicilia.
Per visualizzare questa discussione sul Web, visita https://groups.google.com/d/msgid/opendatasicilia/CAHEdGZN_qYmXGsr8uDoid%2B-zGDT8hC_Y04d18MjsUnZot1XkHA%40mail.gmail.com.
Per altre opzioni visita https://groups.google.com/d/optout.

[Andrea Borruso]

--
Questa è la nuova mailing list di OpenDataSicilia. Un archivio della vecchia è disponibile qui http://opendatasicilia.65952.x6.nabble.com/
---
Hai ricevuto questo messaggio perché sei iscritto al gruppo "opendatasicilia" di Google Gruppi.
Per annullare l'iscrizione a questo gruppo e non ricevere più le sue email, invia un'email a [hidden email].
Per postare in questo gruppo, invia un'email a [hidden email].
Visita questo gruppo all'indirizzo https://groups.google.com/group/opendatasicilia.
Per visualizzare questa discussione sul Web, visita https://groups.google.com/d/msgid/opendatasicilia/CAHEdGZNPf-R_ffzSxvEbgeKbb-ZnM3mh55R6DpThpfGnJck-hg%40mail.gmail.com.
Per altre opzioni visita https://groups.google.com/d/optout.

[Andrea Borruso]

Ciro,

2016-06-17 11:47 GMT+02:00 cirospat <[hidden email]>:

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

​

--
Questa è la nuova mailing list di OpenDataSicilia. Un archivio della vecchia è disponibile qui http://opendatasicilia.65952.x6.nabble.com/
---
Hai ricevuto questo messaggio perché sei iscritto al gruppo "opendatasicilia" di Google Gruppi.
Per annullare l'iscrizione a questo gruppo e non ricevere più le sue email, invia un'email a [hidden email].
Per postare in questo gruppo, invia un'email a [hidden email].
Visita questo gruppo all'indirizzo https://groups.google.com/group/opendatasicilia.
Per visualizzare questa discussione sul Web, visita https://groups.google.com/d/msgid/opendatasicilia/CAHEdGZMs9TsxJtHv6igG5jnH30Riqdm%3DgsMEaaA1RZBQvd5m3A%40mail.gmail.com.
Per altre opzioni visita https://groups.google.com/d/optout.

[Ciro Spataro]

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

______

Il giorno domenica 19 giugno 2016 21:20:12 UTC+2, Andrea Borruso ha scritto:

Ciro,

2016-06-17 11:47 GMT+02:00 cirospat <[hidden email]>:

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:

<a href="https://docs.google.com/spreadsheets/d/1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o/gviz/tq?tqx=out:html&amp;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+&amp;gid=636418541" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://docs.google.com/spreadsheets/d/1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o/gviz/tq?tqx\x3dout:html\x26tq\x3dselect+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+\x26gid\x3d636418541&#39;;return true;" onclick="this.href=&#39;https://docs.google.com/spreadsheets/d/1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o/gviz/tq?tqx\x3dout:html\x26tq\x3dselect+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+\x26gid\x3d636418541&#39;;return true;">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

<a href="http://spreadsheets.google.com/tq?tqx=out:csv&amp;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&amp;key=1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o&amp;gid=636418541" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://spreadsheets.google.com/tq?tqx\x3dout:csv\x26tq\x3dselect%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\x26key\x3d1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o\x26gid\x3d636418541&#39;;return true;" onclick="this.href=&#39;http://spreadsheets.google.com/tq?tqx\x3dout:csv\x26tq\x3dselect%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\x26key\x3d1Iu2RdguEWpQE4kmzs7l18OnZfYtiFNS_FmCx-zcIb5o\x26gid\x3d636418541&#39;;return true;">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

​

--
Questa è la nuova mailing list di OpenDataSicilia. Un archivio della vecchia è disponibile qui http://opendatasicilia.65952.x6.nabble.com/
---
Hai ricevuto questo messaggio perché sei iscritto al gruppo "opendatasicilia" di Google Gruppi.
Per annullare l'iscrizione a questo gruppo e non ricevere più le sue email, invia un'email a [hidden email].
Per postare in questo gruppo, invia un'email a [hidden email].
Visita questo gruppo all'indirizzo https://groups.google.com/group/opendatasicilia.
Per visualizzare questa discussione sul Web, visita https://groups.google.com/d/msgid/opendatasicilia/463505b8-38d6-4809-b1ce-0f31608af054%40googlegroups.com.
Per altre opzioni visita https://groups.google.com/d/optout.