Intervalle de type datetime avec colonnes date et time [Résolu]

[Madfrix]

Bonjour,

j'ai dans une table 1 colonne de type date nommée jour et une colonne nommée heure de type time.
Je souhaite via ces 2 colonnes récupérer tous les enregistrements (pour faire simple) qui sont compris dans un intervalle de type datetime comme par exemple ['2011-03-01 15:28:36' - '2011-03-05 11:23:10']

J'ai commencé par faire ceci :

Code sql :

1
2
3
4
 
...
WHERE CAST(CONCAT(jour, ' ', heure) AS DATETIME) BETWEEN 'un datetime...' AND 'un autre datetime...'
...

Cela marchait mais c'était extremement long et pas difficile à comprendre que c'était le cast qui faisait ramer. Cependant, pour reprendre l'intervalle évoqué plus haut, je ne peux faire ceci :

Code sql :

1
2
3
4
5
 
...
WHERE (jour BETWEEN '2011-03-01' AND '2011-03-05')
AND (heure BETWEEN '15:28:36' AND '11:23:10')
...

pour des raisons évidentes...Ainsi, mon alternative actuelle est de "mixer" un peu les 2 méthodes à savoir jointer 2 tables en filtrant d'emblée les jours puis sur le regroupement des 2 tables, filtrer les jours et les heures via une conversion de type ou en utilisant la fonction TIMESTAMP.

Ainsi, au début, j'avais une structure telle que celle ci :

Code sql :

1
2
3
4
5
6
 
SELECT ....
FROM ...
INNER JOIN...
ON ...
WHERE CAST(CONCAT(jour, ' ', heure) AS DATETIME) BETWEEN ... AND ...

Et maintenant :

Code sql :

1
2
3
4
5
6
7
8
9
10
 
SELECT ...
FROM (
   SELECT...
   FROM ....
   WHERE jour BETWEEN ... AND ...
)
INNER JOIN ...
ON ...
WHERE TIMESTAMP(CONCAT(h.jour, ' ', h.heure)) BETWEEN ... AND ...

ce qui me permet de passer d'un temps de traitement de 2,5s à 1,5s car la conversion s'effectue sur les jours déjà filtrés par le regroupement de jointure et pas sur toutes les lignes de la tables.

Mais comment améliorer encore ce type de requête à savoir récupérer un intervalle de type datetime avec 2 champs de type date et time ?

Merci de vos idées

[Antoun]

L'idéal serait évidemment de remplacer tes deux colonnes DATE et TIME par une seule colonne DATETIME, mais bon, j'imagine que tu ne peux pas pour une raison ou pour une autre...

Première piste, utiliser un row constructor :

Code :

1
2
3
 
WHERE (jour, heure) >= ('2011-03-01', '15:28:36') 
  AND (jour, heure) <= ( '2011-03-05', '11:23:10')

Seconde piste, passer tout en numérique :

Code :

1
2
 
WHERE jour * 1000000 + heure BETWEEN 20110301152836 AND 20110305112310

[Madfrix]

Joli !

merci pour ces "astuces" je ne les connaissais pas. Le row constructor passe en 1,15s et le "tout numérique" en 0,75s c'est donc largement mieux. Par contre je conserve mon filtre initial sur les jours sinon c'est plus long.

Effectivement, je conserve le type date + time au lieu de datetime déjà par facilité du au mode d'insertion mais surtout parce que je fais beaucoup de regroupement par jour, heure, 1/4h, min etc et que je pense que multiplier les EXTRACT me ferait ralentir le temps moyen de mes requêtes.

Un grand merci en tout cas je considère mon problème résolu

[Antoun]

Envoyé par Madfrix

Effectivement, je conserve le type date + time au lieu de datetime déjà par facilité du au mode d'insertion mais surtout parce que je fais beaucoup de regroupement par jour, heure, 1/4h, min etc et que je pense que multiplier les EXTRACT me ferait ralentir le temps moyen de mes requêtes.

Premature optimization is the root of all evil...

[Madfrix]

Justement, je me suis dis et je le pense toujours d'ailleurs qu'il valait mieux avoir une atomicité des colonnes (date + time) plutot qu'un agrégation en 1 colonne de type datetime

[Antoun]

ça revient au même que si je disais : pour une meilleure atomicité des pseudos, il faut une colonne avec "Mad" et une seconde avec "frix"

[Madfrix]

Ah ok je croyais que tu voulais dire que j'avais eu tords de choisir 2 colonnes au lieu d'une...

Nous sommes sur la même longueur d'onde

[Madfrix]

De plus après vérif, on gagne 2 octets à choisir date+time au lieu de datetime. Par contre on en perd 2 à choisir date+time au lieu de timestamp

[Antoun]

Tu as tort de choisir deux colonnes au lieu d'une. L'atome, c'est la partie que tu ne peut plus couper sans la casser. Quand je coupe "Albert Einstein" en "Albert" et "Einstein", je gagne de l'information parce que cela me permet de discriminer deux informations différentes ; par exemple, ça me permet de trier par nom de famille tout en affichant le prénom en premier.

Si par contre je coupe "Albert" en "Al" et "bert", je casse mon atome et je perds de l'info, parce que "bert" ne veut plus rien dire et "Al" ne veut plus dire grand-chose. Je n'ai aucune utilisation possible de "bert" sans "Al", et je m'oblige donc à reconstituer l'info par une concaténation coûteuse.

C'est la même chose pour le jour et l'heure, même si je reconnais que le cas est moins tranché. En les séparant, tu perds de l'info, parce qu'ils sont beaucoup plus significatifs ensemble que séparés. Pour t'en servir ensuite, tu es obligé de reconstituer la véritable info par une concaténation ou autres astuces, qui te font de toute façon perdre l'index.

A l'inverse, tu pourrais placer un index sur une colonne DATETIME, qui te donnerait des performances bien meilleures. Quant à tes GROUP BY par heure ou par quart d'heure, il vont nécessiter des opérations de toute façon, donc tu n'auras pas d'index pour eux, et rien ne dit qu'un EXTRACT entraînerait une perte de performance significative. A mon avis, elle sera négligeable par rapport au gain de perf sur le BETWEEN.

[Antoun]

Envoyé par Madfrix

De plus après vérif, on gagne 2 octets à choisir date+time au lieu de datetime. Par contre on en perd 2 à choisir date+time au lieu de timestamp

Envoyé par Donald Knuth

Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact

la suite ici : http://c2.com/cgi/wiki?PrematureOptimization