Traitement de dates de différents formats

**nicoreims** · 24/05/2016, 22h01

Bonsoir,

Chaque nuit je dois traiter un champ de garantie d'une table afin d'afficher, le jour J, si le matériel est encore sous-garantie ou non.

Pour simplifier il y a plusieurs champs, mais les plus importants sont le champ date_fingarantie qui est sous ce format "22/05/2016", et le champ etat_garantie en varchar pour afficher SG ou HG.

J'essaye de créer mon traitement ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

update materiel set etat_garantie="SG" where date_fingarantie > now()

mais je ne sais pas comment insérer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

STR_TO_DATE('date_fingarantie', '%d/%m/%Y')

pour formater la date dans un format valide pour la comparaison.

Merci d'avance à vous pour votre aide
nicolas

**al1_24** · 25/05/2016, 10h01

Une date en SQL n'a pas de format. Elle doit être enregistrée dans une colonne de type DATE.
Pour ce type de calcul, il n'est pas nécessaire, et encore moins conseillé, d'utiliser une colonne pour enregistrer le résultat d'un calcul. Utilisez plutôt une vue (ou une colonne calculée, mais MySQL ne prend pas en charge cette fonctionnalité).

**nicoreims** · 25/05/2016, 10h43

Bonjour,

Merci , en fait pour le champ date, je n'ai pas le choix, c'est un fichier CSV que j'insère dans ma table avec LOAD DATA et les dates sont sous ce format.
J'ai avancé un peu :

Mise au format SQL de la date :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UPDATE materiel_test SET date_fingarantie = DATE(STR_TO_DATE(date_fingarantie, '%d/%m/%Y'))

Modification du type de champ, de varchar à date

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE materiel_test CHANGE `date_fingarantie` `date_fingarantie` DATE DEFAULT NULL

Quand je créé une vue , elle est visible tout le temps ? Ou uniquement durant le temps de la requête ?

merci

**Artemus24** · 25/05/2016, 14h02

Salut nicoreims.

Envoyé par nicoreims

en fait pour le champ date, je n'ai pas le choix, c'est un fichier CSV que j'insère dans ma table avec LOAD DATA et les dates sont sous ce format.

C'est absurde ce que vous dites !

1) une date sous mysql doit être stocké dans le type "date".
Si vous mettez votre date dans le type "varchar()" vous aurez des problèmes de conversions.

2) Vous avez le choix lors du chargement de votre fichier Excel, de faire la conversion.
C'est lors du load que vous devez faire cette conversion.
Sinon après vous aurez des tas de problèmes, ce qui va devenir une usine à gaz et des problèmes de performances.
Donc travaillez proprement !

3) Voici un exemple de conversion de la date et de l'heure dans le type datetime de mysql :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS trav
--------------
 
--------------
CREATE TABLE trav (
  col_num   integer      null default null,
  col_date  datetime     null default null,
  lib       varchar(255) null default null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3)
     set col_num  = trim(@F1),
         col_date = str_to_date(trim(@F2), '%d/%c/%Y %H:%i:%s'),
         lib      = trim(@F3)
--------------
 
--------------
select * from trav
--------------
 
+---------+---------------------+--------------------+
| col_num | col_date            | lib                |
+---------+---------------------+--------------------+
|    NULL | 2014-05-10 15:33:17 | web                |
|     125 | 2014-05-15 23:53:59 | alpha; beta; gamma |
|    -247 | NULL                | zéro               |
+---------+---------------------+--------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
 
Appuyez sur une touche pour continuer...

4) voici le fichier à charger :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
\N;"10/5/2014 15:33:17";"web"
125;"15/5/2014 23:53:59";"alpha\; beta\; gamma"
-247;\N;"zéro"

5) si j'ai bien compris, chaque nuit (votre nuit commence quand ?) vous devez lancer votre requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

update materiel set etat_garantie="SG" where date_fingarantie > now()

Normalement cela ne doit poser aucun problème si la colonne "date_fingarantie" est dans le type "date".

Mais vous faites un travail pour rien. Le mieux est d'utiliser une view :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
--------------
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 `materiel`
--------------
 
--------------
CREATE TABLE `materiel`
( `id`                integer  unsigned not null auto_increment primary key,
  `etat_garantie`     char(02)              null,
  `date_fingarantie`  date              not null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `materiel` (`etat_garantie`,`date_fingarantie`) value
  (null, '2017-12-31'),
  (null, '2016-05-25')
--------------
 
--------------
select * from materiel
--------------
 
+----+---------------+------------------+
| id | etat_garantie | date_fingarantie |
+----+---------------+------------------+
|  1 | NULL          | 2017-12-31       |
|  2 | NULL          | 2016-05-25       |
+----+---------------+------------------+
--------------
DROP VIEW IF EXISTS `garantie`
--------------
 
--------------
CREATE VIEW `garantie` as
select id,
       cast(case when date_fingarantie > now() then 'SG' else 'HG' end as char(02)) as etat_garantie,
       date_fingarantie
from materiel
--------------
 
--------------
select * from garantie
--------------
 
+----+---------------+------------------+
| id | etat_garantie | date_fingarantie |
+----+---------------+------------------+
|  1 | SG            | 2017-12-31       |
|  2 | HG            | 2016-05-25       |
+----+---------------+------------------+
--------------
commit
--------------
 
--------------
set autocommit = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Envoyé par nicoreims

Quand je créé une vue , elle est visible tout le temps ? Ou uniquement durant le temps de la requête ?

La view est disponible tout le temps !

@+

**nicoreims** · 25/05/2016, 15h12

Merci beaucoup Artemus24 pour toute cette aide !
Je vais regarder ça .

**nicoreims** · 25/05/2016, 21h50

Bonsoir,

Je viens de tester et effectivement j'arrive à insérer directement les dates au bon format lors du LOAD DATA, avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

set date_fingarantie = STR_TO_DATE(date_fingarantie, '%d/%m/%Y')

Par contre j'ai un bug que je n'arrive pas à trouver, le champ en format DATE, me met des NULL à l'insertion (car en default null).
Je l'ai donc passé en VARCHAR pour voir ce qu'il insérait vraiment, et ce sont bien des dates comme celle-ci 2016-05-25 sans aucun espace (oui j'ai supprimé le trim pour tester).

Si je repasse le champ en DATE, il conserve bien les dates.

Mais je ne comprends pas pourquoi il n'arrive pas à insérer les dates durant le load data avec le champ en :

`date_fingarantie` date DEFAULT NULL,

Merci d'avance

**al1_24** · 26/05/2016, 08h17

Si ta date est au format '2016-05-25', c'est un peu normal que STR_TO_DATE(date_fingarantie, '%d/%m/%Y') ne puisse pas la convertir...

**nicoreims** · 26/05/2016, 08h24

Non, elle est sous ce format 2016-05-26 une fois convertie, dans mon champ en varchar ça passe , mais elle ne s'insère pas sous le champ en DATE, alors que le format est correct.

**Artemus24** · 26/05/2016, 12h37

Salut Nicoreims.

Qu'est-ce que vous avez comme représentation de la date dans le fichier Excel ?

D'après votre premier message :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

STR_TO_DATE('date_fingarantie', '%d/%m/%Y')

votre date est sous le format suivant : "23/05/2016".
Ce qui signifie que le mois est bien sur deux chiffres, l'année sur quatre chiffres, et que l'ordre est jour, mois année.

Il va vous mettre NULL si ce format n'est pas respecté. Par exemple : "23/5/2016" car ici le mois est sur 1 chiffre.

A vous de nous dire le format de la date que vous avez dans votre fichier Excel.

Ne supprimez pas la fonction "trim()" car elle supprime les blancs inutiles qui peuvent encadrer votre valeur dans le fichier Excel.

@+

**nicoreims** · 26/05/2016, 16h28

Bonjour,

Non je suis bien sur 2 digits à chaque fois.

Quand je mets ce champ en varchar j'ai bien la date, même si je le remets en DATE avec phpmyadmin, il me garde bien la date.
Ca va être un problème tout bête à mon avis

Voici une ligne extraite de la table en varchar :

(3692, 'CC01202', '41DLHA0', 'ENCAISST', 'TPV0000045', '2010-11-01'),

Voici la table avec le champ en DATE,

CREATE TABLE IF NOT EXISTS `materiel_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(...)
`date_fingarantie` date DEFAULT NULL,
PRIMARY KEY (`id`)
)

Merci

**al1_24** · 26/05/2016, 16h38

Pour que les choses soient bien claires :
Quel est le format de la date dans le fichier csv ?

**nicoreims** · 26/05/2016, 16h43

01/05/2017
01/04/2020
01/03/2012
01/03/2017
01/04/2009
01/04/2009
01/04/2009
01/04/2009

Vérification faite aussi sous notepad

**Artemus24** · 26/05/2016, 18h37

Salut nicoreims.

Je crois qu'il y a un problème de communication !

Envoyé par nicoreims

Je viens de tester et effectivement j'arrive à insérer directement les dates au bon format lors du LOAD DATA, avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

set date_fingarantie = STR_TO_DATE(date_fingarantie, '%d/%m/%Y')

Comment avez-vous testé ces dates ?
Si vous nous dites que ces dates ont été effectivement insérées dans la table, c'est qu'en faisant un "select date_fingarantie from materiel" vous n'avez pas des "NULL" mais bien vos dates dans le bon format qui est "YYYY-MM-DD".

Or d'après votre post #12, la date est au format "dd/mm/yyyy".
Dans ce cas là, le "set date_fingarantie = STR_TO_DATE(date_fingarantie, '%d/%m/%Y')" de votre "load data local infile" fonctionne parfaitement.

Envoyé par nicoreims

Par contre j'ai un bug que je n'arrive pas à trouver, le champ en format DATE, me met des NULL à l'insertion (car en default null).

C'est là que je ne comprends plus très bien !

Votre colonne "date_fingarantie" a-t-elle bien été déclaré dans la table avec le type "date" ?
Si c'est oui, alors je ne comprends pas que dans la première partie de votre message, le chargement s'est bien passé et que vous avez que des "NULL" dans cette colonne.

Envoyé par nicoreims

Je l'ai donc passé en VARCHAR pour voir ce qu'il insérait vraiment, et ce sont bien des dates comme celle-ci 2016-05-25 sans aucun espace (oui j'ai supprimé le trim pour tester).

Quel est le charset de votre fichier Excel ? Et qu'est-ce que vous utilisez pour vos tables comme charset ?

Avez-vous géré correctement la fin de ligne sous windows avec votre "load data local infile" ?

Envoyé par nicoreims

Si je repasse le champ en DATE, il conserve bien les dates.

Je ne comprends cette affirmation.

Dans un premier temps, l'insertion dans la table avec le format "date" fonctionne correctement.
Puis ensuite vous mettez "varchar()" et vous constatez que la date est dans le bon format, donc plus de "NULL".
Vous remettez le type "date", et maintenant vous obtenez la date renseignée et au bon format.

Vous avez dit bizarre ? Comme c'est Bizarre !

Pouriez-vous donner le "load data local infile" en totalité, sans rien omettre ?
Ainsi qu'un extrait du contenu de votre fichier Excel.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(3692, 'CC01202', '41DLHA0', 'ENCAISST', 'TPV0000045', '2010-11-01'),

Il y a des parenthèses dans votre fichier Excel ?

Par contre, la date n'est pas au bon format. On attend ce format "01/11/2010" !

@+

**nicoreims** · 26/05/2016, 23h27

Je suis sur une piste !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

$pdo->exec("LOAD DATA LOCAL INFILE 'GLPI.csv' INTO TABLE materiel_test CHARACTER SET latin1 FIELDS TERMINATED BY ';' IGNORE 2 LINES  (@tarifsg, @tarifhg, @periode, ville, materiel, famille, sn, @dateinstall, @date, date_fingarantie, @model, @constructeur) set date_fingarantie = STR_TO_DATE(date_fingarantie, '%d/%m/%Y') "); ?>

J'ai testé en écrivant une date en dur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 set date_fingarantie = STR_TO_DATE('26/05/2016', '%d/%m/%Y')

Ca l'a bien convertie et enregistrée dans mon champs DATE.

Je crois que c'est lui qui pose souci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

set date_fingarantie = STR_TO_DATE(date_fingarantie, '%d/%m/%Y') "); ?>

**nicoreims** · 27/05/2016, 10h28

Bonjour tout le monde,

J'ai enfin trouvé ! Alors pourquoi ça passait en varchar (avec le bon format dans ma base 2016-05-27) et pas en date je ne le saurais jamais.

En fait , je pense ne pas avoir bien compris la fonction des @ dans le LOAD DATA. Je pensais que c'était les champs qu'on désirait ignorer.
J'ai donc ignoré ma date de fin de garantie @date_fingarantie (ici @date mais c'était pour tester), et ça passe.... et déclarant set date_fingarantie par une valeur ignorée avant.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
$pdo->exec("LOAD DATA LOCAL INFILE 'GLPI.csv' INTO TABLE materiel_test CHARACTER SET latin1 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 2 LINES  (@tarifsg, @tarifhg, @periode, pdv, ville, materiel, famille, sn, @dateinstall, @date, @date_fingarantie, @model, @constructeur) set date_fingarantie = STR_TO_DATE(@date, '%d/%m/%Y') ");
?>

**Artemus24** · 27/05/2016, 11h59

Salut nicoreims.

Envoyé par nicoreims

J'ai enfin trouvé ! Alors pourquoi ça passait en varchar (avec le bon format dans ma base 2016-05-27) et pas en date je ne le saurais jamais.

Vous aviez deux problèmes :

1) le saut de ligne sous windows n'est pas le même sous linux.
Sous windows, il faut mettre "LINES TERMINATED BY '\r\n'" tandis que sous linux, il faut mettre "LINES TERMINATED BY '\n'".

2) quand vous indiquez un nom de colonne dans la liste entre parenthèse, vous avez trois possibilités :
--> mettre le nom de votre colonne (la colonne sera prise),
--> mettre par exemple "@dummy" (la colonne sera ignorée). Vous pouvez aussi mettre le nom de votre colonne commençant par "@".
--> mettre un nom commençant par "@" (pour faire un calcul). Exemple : "SET toto = trim(@toto)".

Le nom de la colonne, c'est celle de votre table MySql

Il y a une quatrième possibilité, celle de ne rien extraire, mais faire un calcul particulier sur la colonne de votre table.
L'initialisation d'une date : "SET datime = CURRENT_TIMESTAMP".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
LOAD DATA LOCAL INFILE 'GLPI.csv'
     INTO TABLE `materiel_test`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 2 LINES
     (@tarifsg, @tarifhg, @periode, pdv, ville, materiel, famille, sn, @dateinstall, @date, @date_fingarantie, @model, @constructeur)
     set date_fingarantie = STR_TO_DATE(trim(@date), '%d/%m/%Y')
;

Que devient la view ?

@+

**nicoreims** · 27/05/2016, 23h16

Merci infiniment Artemus24 pour ce cours, je regarde ce week-end pour la view,
merci

**nicoreims** · 16/06/2016, 11h36

Bonjour,

Je suis de retour

j'essaye de mettre 2 SET à la suite mais cela ne fonctionne pas, j'ai testé les 2 séparemments il n'y a pas de souci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
$pdo->exec("LOAD DATA LOCAL INFILE 'parc.csv' INTO TABLE materiel_test CHARACTER SET latin1 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 2 LINES  (@dummy, @dummy, @dummy, pdv, enseigne, ville, materiel, famille, sn, @date_install, marche, ref, @dummy, @date_fingarantie, modele, constructeur)
     set date_install = STR_TO_DATE(trim(@date_install), '%d/%m/%Y')
     set date_fingarantie = STR_TO_DATE(trim(@date_fingarantie), '%d/%m/%Y') ");