Comment "bien" se débarrasser de du DATETIME de MySQL ?

[seboss666]

Je savais pas trop comment ni avec quel préfixe énoncer mon problème, alors décrivons-le en entier.

 

J'ai développé y'a quelques temps maintenant une petite base de données maison pour mes films en DVD/Bluray, avec gestion basique des prêts. C'était l'occasion de me coller sérieusement du PHP et du MySQL. Et ça rend de fiers services pour savoir si on a déjà à la maison un film devant lequel on bave au magasin.

 

Cette base contient notamment un champ dateAjout de type 'date' d'après MySQL. Je la manipule avec la fonction date() en PHP pour la formater histoire que MySQL rale pas trop, et strtotime() pour la décoder ensuite. Pas forcément le plus efficace, mais j'avoue qu'à l'époque, quand j'ai réussi à le faire marcher, j'y ai plus touché. C'était sans compter mes idées stupides.

 

J'ai récemment décidé de tenter de créer une petite API REST maison pour cette base de données, en Python avec Bottle. Je passe sur le fait qu'il m'a fallu une demi journée en lectures/try/retry pour avoir autre chose que des erreurs 500 (bienvenue dans mon cerveau et ses incohérences). Seulement, j'ai découvert l'horreur (pour moi évidemment) : malgré le fait que les fonctions de mysqldb permettent de créer un dictionnaire, que Bottle sait très bien sérialiser en JSON en temps normal, il bloque sur le date. Après une bonne heure de recherches diverses et variées, il s'avère qu'il n'y aurait pas de façon propre de contourner ce problème.

 

La méthode la plus propre serait donc de se séparer de ce type de champ date pour le remplacer par autre chose, histoire que JSON encoder ne râle plus. En pratique c'est pas un problème, je saurais le faire, bien que ça m'embistrouille un peu (comment remplacer un champ en SQL proprement, sans parler des modifs de l'appli PHP) mais quel serait le format le plus "universel" ? Vous gérez comment les dates dans vos applications/bases de données ?

[Nemasix]

Salut,

 

peut être l'utilisation d'un DATETIME serait suffisant, non?

 

Peut-être un TIMESTAMP, mais il faut faire attention à la conversion UTC que fait MySQL avec le TIMESTAMP.

 

Dans mon travail, je bosse avec des DATETIME (mais c'est en T-SQL )

[Aloyse57]

Toutes mes dates en SQL quel que soit le modèle de base (SQL-Server, MySQL, HyperFile,...) sont déclarées en VARCHAR() et rentrées sous forme AAAAMMJJ.

Jamais eu de problème à travers la myriade d'ODBC différents que j'utilise.

[theocrite]

Toutes mes dates en SQL quel que soit le modèle de base (SQL-Server, MySQL, HyperFile,...) sont déclarées en VARCHAR() et rentrées sous forme AAAAMMJJ.

C'est pas super propre. Et tu as de la chance de faire des choses simples qui ne demandent pas de faire des calculs de dates, sinon tu serais embêté.

 

 

Pour répondre à la question initiale, DATE me parrait approprié. Sinon DATETIME ou TIMESTAMP si tu as besoin de plus de précision.

 

Quand tu dis que json râle, c'est quoi le message d'erreur ?

[Nemasix]

Toutes mes dates en SQL quel que soit le modèle de base (SQL-Server, MySQL, HyperFile,...) sont déclarées en VARCHAR() et rentrées sous forme AAAAMMJJ.

Jamais eu de problème à travers la myriade d'ODBC différents que j'utilise.

 

Heureusement que tu ne fais pas de proc stockée avec calculs sur des dates  

[seboss666]

Voilà ce que json.dumps me répond :

TypeError: datetime.date(2014, 5, 16) is not JSON serializable

J'utilise MySQLdb pour faire mes requêtes MySQL, et que j'utilise DictCursor ou pas, c'est pareil.

 

De toute façon il me reste un ou deux problèmes derrière. La doc Bottle dit qu'il convertit de lui-même les dictionnaires (d'où l'utilisation de DictCursor) et les retourne avec le type JSON, et pourtant ça n'a pas l'air d'être le cas. Mais je rate probablement quelque chose de plus à cet endroit-là, parce qu'au final il dit que dict n'est pas un format supporté (gné ?). Sur l'autre table qui gère les types de films, je suis aussi obligé de jouer avec json.dumps, et là, ça passe (y'a pas de champ date). par contre, une fois passé json_decode (en PHP), voilà ce que ça donne :

array(2) {

  [0]=>

  object(stdClass)#1 (2) {

    ["id_type"]=>

    int(1)

    ["types"]=>

    string(3) "DVD"

  }

  [1]=>

  object(stdClass)#2 (2) {

    ["id_type"]=>

    int(2)

    ["types"]=>

    string(6) "Bluray"

  }

}

Je doit certainement manquer un paquet de trucs, mais je désespère pas.

 

[Nemasix]

Il sort d'où ton datetime.date(2014, 5, 16) ?

 

Parce que pour moi en PHP (vieux souvenir toussa) c'est pas :

$date = new DateTime();
$date.setDate(2014, 5, 16);

[Aloyse57]

J'ai fait des programmes dans la distribution (30 magasins) avec des procédures stockées et des triggers et je ne me souviens pas avoir rencontré un problème avec les dates.

Mais c'est vrai que si vous cherchez un date+30 sur 20140101 qui vous retourne le dernier jour du mois de février, ça peut être cocasse 

[Arcy]

Sinon pourquoi pas un logiciel spécialisé ?

 

http://www.filmotech.fr/

 

Je l'utilise depuis quelques années et aucun soucis ...

[seboss666]

Sinon pourquoi pas un logiciel spécialisé ?

 

http://www.filmotech.fr/

 

Je l'utilise depuis quelques années et aucun soucis ...

 

C'est pour l'idée de coder moi-même. Et puis c'est pas dispo pour Linux.

 

Sinon c'est Python qui me sort cette erreur, pas PHP (probablement la conversion du 'date' MySQL au 'datetime' Python). J'ai d'abord développé mon truc en PHP, et à l'époque, je cherchais sur les dates, donc j'ai trouvé un truc sur les dates. Sauf que ça fait iech Python avec la méthode que j'utilise pour tenter le renvoi en json.

 

Même si je suis à peu près certain de mal m'y prendre, ce date-là risque de pas passer de toute façon. Donc l'idée est de m'en débarrasser (j'aurais probablement d'autres soucis après, mais c'est pour plus tard). Et là, je sais pas comment faire ça de manière propre, à part me palucher la base plus ou moins à la main. Surtout si je dois passer du 'date' au timestamp, même stockée en int, ça va me prendre du temps je pense.

 

Pour info, voilà la structure de la table :

`id` int(11) NOT NULL,
  `titre` varchar(128) NOT NULL,
  `annee` int(4) NOT NULL,
  `id_type` int(2) DEFAULT NULL,
  `pret` tinyint(1) DEFAULT '0',
  `nom_pret` varchar(128) NOT NULL,
  `dateAjout` date DEFAULT '2001-12-25'

Et voilà le bout de code que j'utilise pour tenter de renvoyer le tout :

db = mdb.connect('dbhost', 'dbname', 'dbuser', 'dbpass')

@route('/last', method='GET')
def get_last():
    cursor = db.cursor()
    query = """SELECT * FROM collect_liste_films ORDER BY id DESC LIMIT 0, 10"""
    lines = cursor.execute(query)
    data = cursor.fetchall()
    jdata = json.dumps(data)
    return jdata

[Nemasix]

Ok, alors je ne connais pas trop python, mais je viens de trouvé un lien StackOverflow : http://stackoverflow.com/a/11875813

 

Je pense que ça peut t'intéresser

[jrbleboss]

Ce que je comprends c'est que .fetchall() renvoie une liste de tuple. Chaque tuple représente une entrée de ta base. Ensuite, json.dump() sérialise ça de manière naïve : la liste Python devient une liste JSON et chaque tuple Python devient un "objet" JSON. Pour convertir un objet, là encore, approche naïve : il doit essayer de sérialiser chaque champ. Sérializer un int ou un char c'est trivial mais un datetime, il ne sait pas.

 

Donc la solution serait de convertir le champ de type datetime en string par exemple avant de sérialiser en JSON.

Dans l'idée (je ne sais pas trop la forme qu'a data...) :

output = []
for entry in data:
    entry["dateAjout"] = entry["dateAjout"].strftime("%x")
    output.append(entry)
return json.dumps(output)

Sur un exemple simple qui illustre le probleme :

>>> l = [datetime.date(2014, 01, 01), datetime.date(2015, 02, 02)]
>>> json.dumps(l)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/json/__init__.py", line 243, in dumps
    return _default_encoder.encode(obj)
  File "/usr/lib/python2.7/json/encoder.py", line 207, in encode
    chunks = self.iterencode(o, _one_shot=True)
  File "/usr/lib/python2.7/json/encoder.py", line 270, in iterencode
    return _iterencode(o, 0)
  File "/usr/lib/python2.7/json/encoder.py", line 184, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: datetime.date(2014, 1, 1) is not JSON serializable
>>> json.dumps([e.strftime("%x") for e in l])
'["01/01/14", "02/02/15"]'

[seboss666]

Bon, alors, vous allez me prendre pour une poire puissance 10, j'ai simplement changé le type du champ de MySQL de 'date' à 'varchar(11)' et... Ben apparemment d'un point de vue de PHP, ça change strictement rien. Et maintenant, Python ne râle plus trop directement. Bref, j'ai avancé un peu, par contre, j'ai droit à un étrange comportement en rapport avec UTF-8. Mais c'est un autre dossier, maintenant, au moins, je peux faire un peu plus de choses (comme éventuellement à terme passer aux timestamps). Pareil, alors qu'une fois json.dumps passé, en théorie un "return jdata" aurait du renvoyer un type application/json, mais le type renvoyé est html, donc ça passe comme une chaine de caractères. Je dois forcer le type de données pour qu'il me donne le bon truc.

[brice.wernet]

Ah, les dates. Un sujet sympa. Sutout quand on mélange avec json.

 

Json ne sait pas gérer les dates. Ce n'est pas défini dans la "norme" (pas sûr qu'il y en ait une).

 

Mais revenons aux dates. Il est toujours à priori très simple d'utiliser des dates car elles font maintenant partie des langages et de SQL. Mais personne ne code les dates de la même façon!

 

Allons-y, d'expérience:

• le codage unix à l'ancienne: une date est le nombre de secondes depuis le 1er Janvier 1970. Attention aux heures d'hiver/d'été et au déclage horaire
• le codage C#: la date est un entier sur 64bits, sa résolution est de l'ordre de la nanoseconde... ou presque! explication plus loin
• le codage ODBC/Excel/Microsoft SQL: la date est un nombre à virgule flottante, sur 32 ou 64 bits. La partie entière est le nombre de jour depuis le 1er Janvier 1753, la partie flottante une fraction de jours
• le codage Javascript: Javascript est une BOUZE qui ne connait que des nombres en virgules flottante sur 32 bits, les dates semblent être codées un peu à la microsoft.

Le résultat quand on mixe out cela:

• une date codé virgule flottante 32bits a au moins la résolution d'une seconde (1 seconde = 1/86400s donc n'atteint pas la 6ème décimale de précision). Mais c'est de la virgule flottante: les comparaisons ne sont pas toujours aussi simple que l'on croit. En tout cas, comme la partie entirèe est un nombre de jour, on peut faire des calculs simples de durée et des comparaisons de dates sans les heures.
• une date codée entier permet de faire des comparaisons avec précision.Malheureusement elles cohabitent parfois mal avec des dates en virgule flottante (conversion dans un sens puis dans l'autre qui ne redonne pas le même résultat)
• si vous ne maîtrisez pas TOUTE la chaîne, vous pouvez aller vers des bugs sympas. Exemple: ceux qui enlèvent 1 microseconde à une date pour faire une limite maxi de comparaison (limite inclusive) et qui s'aperçoive que leur requête renvoie parfois des résultats au lendemain (question d'arrondi)
• Stocker la date au format AAAAMMJJ est intéressant si on a besoin de faire un tri par date, mais empêche de faire des calculs sur les dates.

Pour passer des dates sur du web:

• je suis en environnement microsoft, donc ms a chois de transmettre les date du serveur vers le client sous forme de chaîne au format "/Date(unixtimestamp+utc)"
• Sur le coup, on les maudit, puis on se rend compte qu'on peut détecter facilement ces chaînes et les transformer en date dans la fonction "success" de l'appel .ajax.
• malheureusement, microsoft ne lit pas de format en retour!!! il faut à l'envoi du client au serveur retransformer chaque date en chaîne via un appel .toISOString()

Alors pour résumer:

• mes dates en BDD sont gérées en mode natif de la BDD afin de permettre de faire des calculs
• en C#, les dates sont au format natif C#
• je porte une attention particulière à toujours comparer par rapport à des date sans heures: pour aller du 01/10 au 10/10 23h59+, je compare avec 01/10 00h00 <= date < 11/10 00h00. Cela me garanti de toujours fonctionner quelque soit le codage
• je hais les échanges de date en Json. Pour autant, je colle au format Microsoft puisque c'est les outils dont je dispose
• j'ai une certaine expérience des manipulations de date, et sincèrement c'est un domaine dans lequel il faut essayer d'utiliser au maximum les formats natifs et de comprendre comment la date est codée quand on passe d'un système à l'autre (et de savoir à quel moment on passe d'un système à l'autre! Ceux qui en C# font des requêtes Linq pour Entity Framework savent peut-être de quoi je parle: on est en C# et pourtant selon le moment où la requête Linq sera exécutée (moment qui n'est pas toujours évident), les comparaisons de date seront soit effectuées en C# sous forme d'entier, soit en SQL sous forme de flottant)

[seboss666]

Ça, j'avais bien intégré le fait que chacun faisait ça à sa sauce (et Javascript est un des pires élèves, et pas que dans ce domaine).

 

Merci pour les explications et le retour d'expérience

[John Shaft]

Json ne sait pas gérer les dates. Ce n'est pas défini dans la "norme" (pas sûr qu'il y en ait une).

 

json est bien normalisé : RFC 4627, qui a été remplacée par la 7159