2
votes

Conversion de date et heure dans MySQL

J'ai importé un fichier CSV dans MySQL. Le fichier CSV contient la date / heure au format 'mm / jj / aaaa hh: mm' .

Il est importé dans MySQL uniquement sous forme de texte. Je l'ai donc importé sous forme de texte. J'ai ensuite créé un autre champ dans l'espoir de convertir cette valeur de texte en date et heure dans ce format 'mm-jj-aaaa hh: mm' OU dans ce format 'YYYY-mm-jj hh: mm '. Mais ça ne marche pas. Voici mon code.

ALTER TABLE table1 ADD COLUMN StartDateNEW DATETIME;
SET SQL_SAFE_UPDATES = 0;
UPDATE table1 SET StartDateNEW = STR_TO_DATE(StartDate, '%m/%e/Y %H:%i');
SET SQL_SAFE_UPDATES = 1;

Exemple de données:

Certains plus d'exemples de données:

J'essaie cela depuis plus d'une heure maintenant. Quelqu'un peut-il vous aider?

sql datetime date mysql sql-update

5 commentaires

Vous voulez probablement '% m /% d / Y% H:% i' (% d , pas % e )

Cela ne fonctionne pas non plus avec% d. J'ai utilisé% e car% e Jour du mois en tant que valeur numérique (0 à 31) et% d Jour du mois en tant que valeur numérique (01 à 31). De toute façon, cela ne fonctionne pas. J'ai essayé les deux.

Chargez-vous les données en utilisant la syntaxe LOAD DATA INFILE ?

Non. C'est l'erreur que j'obtiens même avec% c /% e /% Y% k:% I.

Code d'erreur: 1411. Valeur datetime incorrecte: «31/03/2012 23:54» pour la fonction str_to_date 0,000 sec

3 Réponses :

1
votes

Selon vos exemples de données, la chaîne de format correcte pour STR_TO_DATE est

%c/%e/%Y %k:%i

% c autorise les numéros de mois à un seul chiffre
% e autorise les numéros de jour à un seul chiffre
% Y année à quatre chiffres
% k autorise des heures à un seul chiffre
% i minutes à deux chiffres

Toutes les chaînes de format sont décrites dans le manuel de DATE_FORMAT .

Démo sur dbfiddle

7 commentaires

Obtention d'une erreur même avec% c /% e /% Y. Code d'erreur: 1411. Valeur datetime incorrecte: «31/03/2012 23:54» pour la fonction str_to_date 0,000 sec

@ stat77 c'est une valeur que j'ai dans ma démo ... y a-t-il des espaces supplémentaires autour des données?

C'est étrange. 31/03/2012 23:54:00. J'utilise la fonction LEN dans Excel LEN (31/03/2012 11:54:00) et cela me donne 16. Il y a certainement plus de 16 caractères.

@ stat77 et LEN ('3/31/2012 23:54') devraient donner 15 qui n'est pas non plus 16 ...

Il y a deux espaces entre la date et l'heure.

@ stat77 alors il vous suffit d'ajouter le deuxième espace à la chaîne de format, c'est-à-dire que % c /% e /% Y% k:% i car STR_TO_DATE a besoin de la chaîne pour correspondre exactement

Pouvez-vous copier et coller la chaîne exacte dans un commentaire?

2
votes

Si vous chargez des données à l'aide de le LOAD DATA INFILE syntaxe , il devrait être possible de gérer la conversion à la volée.

En supposant que votre fichier source csv ressemble à:

LOAD DATA INFILE '/path/to/my/file.csv' INTO TABLE mytable
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' -- or '\n'
    IGNORE 1 LINES
    (@StartDate, @EndDate, Value)
    SET 
        StartDate = STR_TO_DATE(@StartDate, '%c/%e/%Y %k:%i'),
        EndDate =   STR_TO_DATE(@EndDate,   '%c/%e/%Y %k:%i')
;

Vous pouvez définir les colonnes StartDate et EndDate comme type de données datetime , et simplement faire:

StartDate, EndDate, Value
"1/10/2012 10:05", "1/11/2012 11:51", abc
"1/13/2012 08:00", "1/15/2012 09:01", abc

NB: et si vous n'utilisez actuellement pas LOAD DATA INFILE ... je vous recommande tout de même de migrer votre code pour l'utiliser. C'est la bonne façon de faire cela dans MySQL, cela fonctionne sur tous les clients .... et c'est très rapide (voir ce lien ).

4 commentaires

J'utilise l'assistant d'importation de table dans MySQL Workbench.

@ stat77: Je recommanderais d'utiliser LOAD DATA INFILE en tout temps. C'est la bonne façon de faire cela dans MySQL, et cela fonctionne sur tous les clients.

J'ai un fichier Excel CSV. Quelle partie de votre code dois-je modifier pour l'adapter?

@ stat77: regardez l'exemple que j'ai donné dans ma réponse. Et il existe de nombreuses ressources disponibles sur SO ou ailleurs sur le Web qui documentent ou montrent des exemples du fonctionnement de cette belle fonctionnalité MySQL.

0
votes

Un hack rapide est le suivant:

Ouvrir un fichier CSV dans Excel
Sélectionnez la colonne de date
Changez le format de date en aaaa-mm-jj à l'aide de la fenêtre contextuelle de format de cellule
Enregistrer le fichier
Importer vers MySQL

Vous n'avez pas besoin d'importer sous forme de texte, puis d'essayer de convertir les données en DateHeure .

3 commentaires

Merci mais je veux garder le temps. Sinon, j'aurais fait exactement cela.

Je pense que le temps ne sera pas perdu

Vous avez raison. L'heure n'est pas affichée dans la cellule mais n'est pas perdue.