mardi 3 avril 2012

Les dates et les heures dans la fonction Query

3 commentaires
La fonction Query de Google Spreadsheet est vraiment une des meilleures fonctions de l'application. On peut utiliser cette fonction pour faire des requêtes SQL dans les feuilles du classeur et même sur d'autres fichiers Google Spreadsheet grâce à la fonction ImoprtRange().
Dans ce billet je détaille un peu plus l'utilisation des dates dans la fonction Query().

Comment fonctionne les dates dans la fonction Query
Il y a deux choses à savoir, la manière de créer une date dans la requête SQL et la manière de récupérer la Date dans un champ Date Time.
  • Pour créer une date dans sa requête il faut écrire cette syntaxe Date '2012-03-31'
  • Pour récupérer la partie date d'une cellule il faut utiliser toDate, todate(A) ou A est la colonne de la requête contenant les dates.
Cas pratiques
Ce sera plus simple avec des cas pratique, ci dessous des exemple de requêtes fonctionnant ou non pour bien comprendre et voir les différences.
Ouvrez l'image dans une fenêtre à coté et voici les explications. 
  • Toutes les valeurs dans les colonnes A à C du tableau sont reconnues comme des formats Date ou Temps car ils sont alignés à droite. 
  • En face de Req* il y a la requête utilisée pour le résultat Res*.
  • Par commodité j'aime bien écrire la requête SQL dans une cellule externe à la fonction Query, cela apporte plus de souplesse pour la lecture je trouve et aussi pour éviter des soucis de concaténation. Donc toutes les formules Query() sont écrites comme cela :  =query(A2:D5;G1). A2:D5 = plage de données et G1 cellule contenant la requête. G1 est un exemple.

- La Req1 (Select D Where A='21/03/2012') ne fonctionne pas car la colonne A est un champ Date et quand on écrit '21/03/2012' cela est équivalent à du texte. Il faut donc créer une date pour la requête, ce qui donne Select D Where A = date '2012-03-21' (Req2).
Dans cet exemple on aurait pu écrire todate(A) pour ne prendre que la partie Date (de la colonne A) mais la colonne A contient uniquement des dates donc ça fonctionne, c'est moins propre mais ça marche. Cf. Req2bis en bas de l'image avec todate().

- Dans la Req3 on fait la même recherche que la Req2 mais sur la colonne B qui est au format Date + Temps. Select D Where B = date '2012-03-21' ça ne marche pas car il faut isoler la date dans la colonne B => Select D Where toDate(B) = date '2012-03-21' (Req4). Au contraire de l'exemple précédent il faut bien écrire la requête avec todate().

- Dans la REq5 et REq6 je prends un exemple en ayant la valeur de recherche dans une cellule la requête devient ="Select D Where A = date '"&J8&"'".
Il faut toujours écrire la date au format US (Req6) même si elle est dans une cellule qui la reconnait en tant que champ date (Req5). Si on écrit la date 2012-03-15 directement dans la cellule elle sera transformée au format FR par Google. Pour éviter cela écrire '2012-03-15 ou '2012-03-15' dans la cellule. 

- Req7 et Req8 l'analyse porte sur les mois, on recherche le mois 03. Cet exemple permet de montrer qu'une fois que l'on a créé une date avec la fonction date '2012-03-15' on peut exploiter les fonctions misent à disposition comme month() pour extraire le mois.

- Req9 à Req12 même travail que précédemment mais sur l'heure. Ca marche de la même manière que la fonction month() mais il y a deux moyens de générer une heure. On peut utiliser le format DateTime complet
Select D Where hour(B) = hour(datetime '2012-03-12 10:12:32'), (Req11) ou passer par la fonction timeofday Select D Where hour(B) = hour(timeofday '10:12:32'), (Req10).
La Req12 se base sur la colonne C de la plage de données contenant que des dates, cela marche aussi.

Pour aller plus loin
Toutes le informations sur les fonctions disponibles et formats nécessaires pour utiliser les dates et heures dans la fonction Query sont disponibles sur l'aide google. Voir les parties "Scalar Functions" et "Language Elements" section "Literals". Attention dans l'aide google, le texte est souvent encadré par des doubles quotes (") dans Spreadsheet il faut utiliser les simples ('). Il y a un décalage car l'aide concerne l'API mais tout ce qui est dit est valable.

3 Responses so far

  1. Bravo pour cet article très didactique !

  2. Très bon billet : claire et concis ! Bravo.

Leave a Reply