[MySQL 5] Trigger formatant la date

Bonjour,

j'essaie d'importer des dates au format "DD/MM/YYYY H:i" dans MySQL ("YYYY-MM-DD H:i:s") sans modifier le fichier d'import (csv). J'essaie de faire un trigger pour modifier la date avant l'insert mais je n'obtient que des dates nulles ("0000-00-00 00:00:00").

Code:

---

1 2 3 4

CREATE TRIGGER oreDateIns BEFORE INSERT ON employe FOR EACH ROW SET NEW.employe_debut = DATE_FORMAT(NEW.employe_debut, '%d/%m/%Y %H:%i'), NEW.employe_fin = DATE_FORMAT(NEW.employe_fin, '%d/%m/%Y %H:%i');

---

Pouvez-vous me dire ce qui ne va pas dans mon trigger ?

Si je me souviens bien, MySQL vérifie le type avant d'exécuter le trigger, donc toute tentative de reformater des date invalides en dates valides par un trigger est vouée à l'échec :(

Merci pour l'info. Je crois que je vais devoir passer les dates en texte. :(

Provisoirement j'espère !

Il faut que l'utilisateur passe par Access pour faire l'import (pas le choix malheureusement), donc ça ne sera probablement pas du provisoire à moins que je trouve une autre solution de contournement (faire l'import via un petit site web si j'arrive à les faire abandonner Access pour ça).

Ce que tu peux faire, c'est une colonne texte avec un trigger alimentant une colonne date.

J'ai créé le trigger suivant pour mettre à jour mes dates à partir du texte (txt_emp_debut et txt_emp_fin) comme vous me l'avez conseillé, mais je ne comprends pas pourquoi il met la date du jour dans employe_debut et employe_fin.

Code:

---

1 2 3 4

CREATE TRIGGER oreDateIns BEFORE INSERT ON employe FOR EACH ROW SET NEW.employe_debut = DATE_FORMAT(NEW.txt_emp_debut, '%d/%m/%Y %H:%i'), NEW.employe_fin = DATE_FORMAT(NEW.txt_emp_fin, '%d/%m/%Y %H:%i');

---

Les champs txt_emp_debut et txt_emp_fin sont bien remplis avec les bonnes dates.

DATE_FORMAT est la fonction qui convertit les dates en texte. Si j'ai bien compris, tu veux faire le contraire, donc c'est STR_TO_DATE.

Merci, c'est effectivement mieux.
J'ai un problème bizarre, la date de début reste toujours à celle du jour. En faisant "show columns" j'obtient :

Code:

---

1 2 3 4 5

+---------------------+-------------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-------------+------+-----+---------------------+-----------------------------+ | EMPLOYE_DEBUT | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | EMPLOYE_FIN | timestamp | NO | | 0000-00-00 00:00:00 | |

---

alors que pour le create j'ai fait la même chose pour les deux dates :

Code:

---

1 2 3 4 5 6 7 8

create table employe( ... EMPLOYE_DEBUT timestamp, EMPLOYE_FIN timestamp, TXT_EMP_DEBUT varchar(20), TXT_EMP_FIN varchar(20), ... ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

---

J'ai fais un drop de la base, redémarré MySQL et tout recréé mais ça n'a rien changé. J'ai constaté que dans les autres tables, tous les premiers timestamp sont pareils (avec "on update CURRENT_TIMESTAMP") alors que tous ont été déclaré en tant que timestamp simple (sans "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"). Il n'y a pas le problème sur les champs de type date.
Je n'ai pas fait de paramétrage particulier sur mon MySQL 5.6.25. Je ne comprends pas du tout ce qui se passe.

Salut Christophe.

Au lieu de faire un trigger, cela ne serait-il pas plus souple de faire une fonction ?

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- ================== -- Function `chgdate` -- ==================   DELIMITER $$   CREATE FUNCTION chgdate(date_in text) RETURNS datetime DETERMINISTIC LANGUAGE SQL BEGIN RETURN (str_to_date(date_in, '%d/%m/%Y %H:%i')); END$$ DELIMITER ;   COMMIT;   -- ====================== -- Changement format date -- ======================   select chgdate('05/03/2015 23:30') as 'date';

---

@+

Mon problème est lors de l'import de fichiers CSV, qui doit être fait via Access. Il faut donc un trigger "before insert", une fonction ne suffit pas, Access se charge de l'affichage.

Je viens de voir dans la doc que MySQL initialise automatiquement le premier timestamp. Je vais essayer de contourner ça.

Salut christophe.

Et comment faites-vous pour passer du fichier .csv à la table mysql ? Est-ce 'access' qui se charge de votre import ?
Si vous faites un "load data local" alors vous pouvez utiliser la fonction sans problème.

Si en faisant un "show columns from employe", vous voyez le "default curren_timestamp", c'est qu'il est dans la déclarative de votre table mysql.
Mais a priori, si vous ne renseignez pas la date enploye_debut, il est normal qu'elle soit renseignée avec sa valeur par défaut.
Sinon y-a-t-il un trigger ou une procédure stockée qui viendrait renseignée cette colonne ?

@+

Salut christophe.

Citation:

---

Je viens de voir dans la doc que MySQL initialise automatiquement le premier timestamp. Je vais essayer de contourner ça.

---

J'ai fait le test et si vous ne mettez rien en tant que default, il ne va pas vous écraser avec la date current.

Etes-vous certain que vous affichez la bonne table ?
Un 'use nom de votre base de données' a-t-il été fait au préalable ?

@+

Citation:

---

Envoyé par Christophe P.

Merci, c'est effectivement mieux.
J'ai un problème bizarre, la date de début reste toujours à celle du jour. En faisant "show columns" j'obtient :

Code:

---

1 2 3 4 5

+---------------------+-------------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-------------+------+-----+---------------------+-----------------------------+ | EMPLOYE_DEBUT | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | EMPLOYE_FIN | timestamp | NO | | 0000-00-00 00:00:00 | |

---

alors que pour le create j'ai fait la même chose pour les deux dates :

---

En fait c'est le comportement par défaut du type TIMESTAMP. MySQL considère que si tu utilises ce type c'est que tu veux forcément une date de màj auto, donc la première colonne TIMESTAMP passe automatiquement en ON UPDATE CURRENT_TIMESTAMP. Il faut que tu utilises DATETIME à la place.

MySQL modifie automatiquement le premier timestamp : http://dev.mysql.com/doc/refman/5.5/...alization.html
Comme indiqué précédemment le premier timestamp déclaré simplement "timestamp" est visible, via "show columns" comme étant non nul et initialisé à "CURRENT_TIMESTAMP" (cf. message de 11h54).
La table est la bonne.

Access se charge des imports. Il y a une connexion ODBC vers MySQL et l'import se fait via l'interface d'Access.
Je peux faire un "load data infile" pour l'import mais il m'est demandé de passer par Access.
L'import via Access ne fonctionne pas à cause du format de date (c'est ce que j'ai identifié, en passant les dates en texte ça fonctionne). J'essaie donc de contourner le problème en utilisant un trigger et des champs texte dans lesquels j'importe la date au format français :

Code:

---

1 2 3 4

CREATE TRIGGER oreDateIns BEFORE INSERT ON employe FOR EACH ROW SET NEW.employe_debut = STR_TO_DATE(NEW.txt_emp_debut, '%d/%m/%Y %H:%i'), NEW.employe_fin = STR_TO_DATE(NEW.txt_emp_fin, '%d/%m/%Y %H:%i');

---

On en est où ? Tu as passé tes colonnes à DATETIME pour éviter le ON UPDATE ?

Je viens de finir mes tests (faits plusieurs fois car résultats bizarres). Avec datetime les deux colonnes de fin restent vides ! En ajoutant "null" il y a aussi des colonnes vides. Ce qui fonctionne c'est de déclarer tous les timestamp comme suit :

Code:

---

1 2	EMPLOYE_DEBUT timestamp default 0, EMPLOYE_FIN timestamp default 0,

---

Etape 1 faite... je vais maintenant passer à l'étape 2 : exécuter des requêtes Access sur une base MySQL ! :aie:

Merci Antoun.

Salut Christophe.

Je me suis demandé pourquoi chez moi, je n'avais pas ce genre de problème.
Et bien, j'ai un paramétrage dans le fichier "my.ini" qui permet d'éviter que mysql me force à sa convenance le type "timestamp".

Code:

---

explicit_defaults_for_timestamp = on

---

Il suffit d'ajouter cela dans "my.ini" et de ne pas oublier de "redémarrer les services" de wampserver.
Et du coup, inutile d'ajouter "default 0" derrière le type "timestamp".

Pour revenir à l'import via access, est-ce que tu passes par l'API "CONNECTOR/ODBC 3.51 for MySQL" ?

@+

Merci pour l'info Artemus24. Je n'ai pas encore accès à la base définitive mais je vais regarder le my.ini.
J'utilise le connecteur ODBC 5.3.4 (mysql-connector-odbc-5.3.4-win32.msi).

En modifiant le my.ini et supprimant le "default 0" j'ai le même truc bizarre qu'avec datetime : je n'ai aucune des deux colonnes de fin. :weird:

1) j'ai testé avec des DATETIME, je n'ai aucun problème. Je pense que soit tes valeurs Access ne correspondent pas exactement à ton format, soit il y a un problème dans tes tests. Tu es sûr de ne pas avoir de secondes ?

Voici mon test :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

create table employe( EMPLOYE_DEBUT DATETIME, EMPLOYE_FIN DATETIME, TXT_EMP_DEBUT varchar(20), TXT_EMP_FIN varchar(20) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;   CREATE TRIGGER oreDateIns BEFORE INSERT ON employe FOR EACH ROW SET NEW.employe_debut = STR_TO_DATE(NEW.txt_emp_debut, '%d/%m/%Y %H:%i'), NEW.employe_fin = STR_TO_DATE(NEW.txt_emp_fin, '%d/%m/%Y %H:%i');   INSERT INTO employe(txt_emp_debut, txt_emp_fin) VALUES ('01/07/2015 11:22', '07/01/2016 22:11') ;   SELECT * FROM employe ;

---

Citation:

---

+---------------------+---------------------+------------------+------------------+
| EMPLOYE_DEBUT | EMPLOYE_FIN | TXT_EMP_DEBUT | TXT_EMP_FIN |
+---------------------+---------------------+------------------+------------------+
| 2015-07-01 11:22:00 | 2016-01-07 22:11:00 | 01/07/2015 11:22 | 07/01/2016 22:11 |
+---------------------+---------------------+------------------+------------------+
1 row in set (0.00 sec)

---

2) après avoir modifié le my.ini, il faut redémarrer MySQL.

1) Il n'y a pas de secondes, du moins dans les 100 premières lignes ; je n'ai pas regardé toutes les lignes.
2) J'ai bien redémarré le serveur.

Je vais proposer une solution par FTP + shell faisant l'import. Cela me parait plus simple.

Si tu refais mon test, ça donne quoi ?

Antoun, je ne pense pas pouvoir faire ton test aujourd'hui. Avec les Insert directs je ne me rappelle pas avoir eu de problème.

Citation:

---

Envoyé par Christophe P.

Antoun, je ne pense pas pouvoir faire ton test aujourd'hui. Avec les Insert directs je ne me rappelle pas avoir eu de problème.

---

:koi: Tu fais quoi, si ce n'est pas des "Insert directs" ?

Si c'est du LOAD DATA INFILE, je crois me rappeler qu'il zappe les triggers.

Je passe par Access pour les imports ! :aie:

Mais maintenant (après la réunion de cet après-midi) nous sommes d'accord pour abandonner cette solution et utiliser le FTP pour envoyer les fichiers qui seront chargés par "LOAD DATA INFILE". :D
Et là ça va beaucoup mieux.

Salut Christophe.

Citation:

---

En modifiant le my.ini et supprimant le "default 0" j'ai le même truc bizarre qu'avec datetime : je n'ai aucune des deux colonnes de fin.

---

J'ai pas bien compris.

Code:

---

1 2	[wampmysqld] explicit_defaults_for_timestamp = on

---

La directive à mettre dans le fichier "my.ini" doit se placer dans la section "wampmysqld" qui est le nom du service mysql.

De plus, cette directive ne fonctionne que pour le type "timestamp" et non le type "datetime".
Si tu veux conserver la totalité du timestamp, à l'identique de ce qui existe sous DB2, tu dois mettre "timestamp(6)" pour avoir les millionièmes de secondes.

Il faut savoir, à l'inverse de DB2, que Mysql met la même valeur du timestamp, même si le chargement dure plusieurs minutes.

Et comme l'indique Antoun, ne pas oublier de "redémarrer les services", en cliquant sur cette option dans l'icône wampserver.

Citation:

---

Mais maintenant (après la réunion de cet après-midi) nous sommes d'accord pour abandonner cette solution et utiliser le FTP pour envoyer les fichiers qui seront chargés par "LOAD DATA INFILE".

---

Comment vas-tu résoudre ton problème de format de la date ?
Soit tu utilises ma fonction que tu peux introduire dans le 'LOAD DATA INFILE'.
Soit tu fais en sorte de ne pas avoir de date sous le mauvais format avant de charger ton fichier dans ta table mysql.

Si tu as un problème pour construire ton "LOAD DATA INFILE", il te suffit de nous indiquer ce que tu ne sais pas faire.

@+

Je vais spécifier le format de date dans le LOAD :

Code:

---

1 2 3 4

LOAD DATA INFILE 'emp.csv' INTO TABLE employeFIELDS TERMINATED BY ';' IGNORE 1 LINES (Employe_CodeEmploye, Employe_Nom, Employe_Prenom, Employe_Statut, @Employe_Debut, @Employe_Fin) SET Employe_Debut = str_to_date(@Employe_Debut, '%d/%m/%Y %H:%i'), Employe_Fin = str_to_date(@Employe_Fin, '%d/%m/%Y %H:%i');

---

Salut Christophe.

Voici un exemple de chargement d'une table à partir d'un fichier de type "text".

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

LOAD DATA LOCAL INFILE 'Charge.txt' INTO TABLE `Cities` CHARACTER SET latin1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 0 LINES (@F1, @F2, @F3, @F4, Population, Latitude, Longitude, @F8) set Country = trim(@F1), City = trim(@F2), AccentCity = trim(@F3), Region = trim(@F4), Date = outils.chgdate(@F8);

---

C'est dans cet exemple que j'ai fait mon test de la fonction chgdate.

Attention à la syntaxe de ta ligne dans le fichier à charger.
Ici dans cet exemple, j'ai :
--> chaque champs est encadré par des guillemettes (").
Ce n'est pas obligatoire pour les champs numériques.
--> chaque champs se termine par une virgule (,) même sur le dernier champs de la ligne.
--> la ligne se termine par un '\n'. Il faut voir cela avec le format du fichier qui est du type windows.

Est-ce que tu as bien vérifié que tous les champs ont été chargés dans les bonnes colonnes ?
Si tu as un problème avec les espaces, utiliser la fonction "trim()" pour les supprimer.

Pour visualiser tes colonnes, tu peux faire un :

Code:

---

1 2 3

select concat('>', Country, '<') as 'Country', concat('>', City, '<') as 'City', ...

---

ainsi tu verras tout de suite si un blanc n'est pas venu s'insérer en début ou en fin de ta colonne de type 'text'.

Sinon, je ne voie rien de particulier dans ton exemple.

@+