Convertir une date format "texte" en format "date" pour comparer avec une tranche de dates

Version imprimable

22/08/2016, 15h08
maglott

2 pièce(s) jointe(s)

Convertir une date format "texte" en format "date" pour comparer avec une tranche de dates

Bonjour,

Je n'ai malheureusement pas réussi à m'inspirer des autres posts sur le sujet.
Il s'agit d'un script PHP7 visant à réaliser un export sn csv.

Je souhaite convertir une valeur date (en format "texte" dans la bdd) en format "date" pour pouvoir la comparer avec une tranche de dates. (donc extraire tous les enregistrements entre date_from et date_to)

Ma table "submissions" comporte les champs suivants (cf fichier champs_bdd.jpg joint):
Column Type Valeur
FieldName text Jour
FieldValue text 01/07/2016

Voici le code qui ne sort aucun résultat (cf fichier joint "resultat requete.jpg"):

Code:

$reponse = $bdd->query('SELECT * FROM vdvyt_rsform_submission_values a JOIN vdvyt_rsform_submissions b ON a.SubmissionId=b.SubmissionId WHERE ( FieldName="Jour" AND STR_TO_DATE(FieldValue, "%d/%m/%Y") BETWEEN "'.$from.'" AND "'.$to.'" ) ORDER BY FieldValue ASC ');

J'ai essayé '%d/%m/%Y' au lieu de "%d/%m/%Y" sans succès.

Pièce jointe 218438

Je ne sais pas s'il faut d'autres infos (je ne suis pas développeur pro).

Un grand merci par avance à toute bonne âme et esprit éclairé.

Xavier

Salut Maglott.

Attention de ne pas confondre "format" avec "type" !

Le format, c'est "DD/MM/YYYY" ou encore celui utilisé par MySql "YYYY-MM-DD". C'est ce que tu obtiens à l'affichage de ta date.
Le type, c'est le stockage de cette donnée dans la table MySql. L'amplitude va de '1001-01-01' jusqu'à '9999-12-31'.
--> http://dev.mysql.com/doc/refman/5.7/en/datetime.html

Le mieux est de mettre ta colonne au type "date". Voici comment faire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE IF NOT EXISTS test
( `id`          integer unsigned not null auto_increment primary key,
  `date_string` varchar(255)     not null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `test` (`date_string`) values
  ('31/12/2015'), ('13/10/2015')
--------------
 
--------------
select * from test
--------------
 
+----+-------------+
| id | date_string |
+----+-------------+
|  1 | 31/12/2015  |
|  2 | 13/10/2015  |
+----+-------------+
--------------
describe `test`
--------------
 
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| date_string | varchar(255)     | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
--------------
alter table `test` add column `date_new` date not null
--------------
 
--------------
describe `test`
--------------
 
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| date_string | varchar(255)     | NO   |     | NULL    |                |
| date_new    | date             | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
--------------
update `test` set date_new = concat(substr(date_string,7,4), '-', substr(date_string, 4, 2), '-', substr(date_string, 1, 2))
--------------
 
--------------
select * from test
--------------
 
+----+-------------+------------+
| id | date_string | date_new   |
+----+-------------+------------+
|  1 | 31/12/2015  | 2015-12-31 |
|  2 | 13/10/2015  | 2015-10-13 |
+----+-------------+------------+
--------------
alter table `test` drop column date_string
--------------
 
--------------
alter table `test` change `date_new` `date` date not null
--------------
 
--------------
describe `test`
--------------
 
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| date  | date             | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
--------------
select * from test
--------------
 
+----+------------+
| id | date       |
+----+------------+
|  1 | 2015-12-31 |
|  2 | 2015-10-13 |
+----+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

22/08/2016, 19h01
maglott

Merci Artemus,

Mais je ne peux pas toucher à la structure de la bdd. Il s'agit d'un site Joomla et le sujet se rapporte à une table de soumissions à un formulaire, lui-même élaboré à partir d'un composant. A la première mise à jour du composant toute modification sera supprimée.

Salut Maglott.

Le format du type "date" sous MySql est "YYYY-MM-DD".
Donc tu dois le mettre sous ce format si tu veux faire des comparaisons.

Pour convertir une chaîne de caractères de type "char" contenant une date dans le type "date", il faut utiliser la fonction "str_to_date()" :
--> https://dev.mysql.com/doc/refman/5.5...on_str-to-date

Voici un exemple basée sur cette fonction :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`           integer unsigned NOT NULL auto_increment primary key,
  `date_string`     char(10)      NOT NULL,
  `lib`          varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`date_string`,`lib`) values
  ('21/07/2616','un'),('21/08/2016','deux'),('20/09/2016', 'trois'),('20/08/2016','quatre')
--------------
 
--------------
select * from test
--------------
 
+----+-------------+--------+
| id | date_string | lib    |
+----+-------------+--------+
|  1 | 21/07/2616  | un     |
|  2 | 21/08/2016  | deux   |
|  3 | 20/09/2016  | trois  |
|  4 | 20/08/2016  | quatre |
+----+-------------+--------+
--------------
select * from test where STR_TO_DATE(date_string,'%d/%m/%Y') between '2016-08-20' and '2016-08-21'
--------------
 
+----+-------------+--------+
| id | date_string | lib    |
+----+-------------+--------+
|  2 | 21/08/2016  | deux   |
|  4 | 20/08/2016  | quatre |
+----+-------------+--------+
--------------
select * from test where STR_TO_DATE(date_string,'%d/%m/%Y') between STR_TO_DATE('20/08/2016','%d/%m/%Y') and STR_TO_DATE('21/08/2016','%d/%m/%Y')
--------------
 
+----+-------------+--------+
| id | date_string | lib    |
+----+-------------+--------+
|  2 | 21/08/2016  | deux   |
|  4 | 20/08/2016  | quatre |
+----+-------------+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

En utilisant cette fonction sur la colonne date de type "char", la requête va devoir balayer toutes les lignes de la table, même avec un index.
C'est pourquoi, avec le type "date", et avec un index sur cette colonne, MySql ne prendra que les lignes appartenant à l'intervalle.
Et la performance sera bien meilleure qu'avec une chaîne de caractères.

C'est pourquoi, je te conseille de modifier la structure de ta table afin de résoudre ce problème.

@+

23/08/2016, 09h49
maglott

Merci Artemus, mais comme je l'indiquait précédement je ne peux pas toucher à la structure de la bdd.
Je ne comprends pas pourquoi mon utilisation de STR_TO_DATE ne fonctionne pas dans mon code.
23/08/2016, 16h47
Artemus24

Salut Maglott.

J'espère que vous êtes bien sous MySql !
Si vous utilisez un autre SGBDR, ll se peut que la fonction n'existe pas sous ce nom.

@+
23/08/2016, 16h53
maglott

Oui MySQL tout à fait.
23/08/2016, 18h44
Artemus24

Salut maglott.

Citation:

Envoyé par Maglott

Je ne comprends pas pourquoi mon utilisation de STR_TO_DATE ne fonctionne pas dans mon code.

Est-ce que cela fonctionne au moins une fois ?

1) soit vous avez dans votre chaîne de caractères quelque chose qui sera mal interprété.
Par exemple, un blanc, un NULL ...

Pour le NULL, il suffit de faire ceci : "COALESCE(str_to_date(date_string, '%d/%m/%Y'), now())".
Si la date existe, il vous la retourne. Si c'est NULL, il vous donne la date du jour.

Voici la fonction "COALESCE()" : http://dev.mysql.com/doc/refman/5.7/...ction_coalesce

3) le format de la date n'est pas conforme.
Par exemple, vous avez ceci '2016-08-23' ou cela '23/08/2016'.
Il suffit de tester le résultat de la conversion, comme ci-après : "COALESCE(str_to_date(date_string, '%d/%m/%Y'), str_to_date(date_string, '%m%d%Y'), date_string)".
Pour les deux premiers "date_string", vous indiquez le format.
Pour le dernier, il est nécessairement dans bon format, c'est-à-dire "YYYY-MM-DD".

Voire même, une date partiellement renseignée.

4) votre n'est pas conforme.
Par exemple, un "2016-02-30" qui n'existe pas ou encore un "2015-14-31" avec un mois à 14.

5) A vous de rechercher dans votre jeu d'essai tout ce qui n'est pas conforme à ce que vous attendez.

@+
23/08/2016, 22h23
maglott

Un grand merci Artemus, il y a avait effectivement un souci de conversion résolu par COALESCE(str_to_date(FieldValue, "%d/%m/%Y"), str_to_date(FieldValue, "%m%d%Y")
J'ai du mal à y croire, c'est vraiment super.