Discussion :

[aieeeuuuuu]

Bonjour,

Le problème de la solution des arrondis, est l'effet de seuil.

Prenons l'exemple pour une arrondi à 5s :
Une valeur pour X arrive à 17:00:02 donc arrondie à 17:00:00
Une valeur pour Y arrive à 17:00:06 donc arrondie à 17:00:05
Les deux valeurs n'ont que 4s d’écart, mais ne matcherons pas.

une autre approche serait de stocker les rattachement pour l'ecart le plus grand envisagé, par exemple 15s
Vous associez chaque Valeur de X avec toutes les valeurs de Y se trouvant dans l'intervalle [temps x - 15s; temps x + 15s]
Il reste a filtrer dans cette table le moment venu avec le seuil voulu.

Sous certains SGBDR, ce n'es pas compliqué avec une vue matérialisée/indexée.

Avec MySQL qui n'offre pas je crois cette possibilité, il faudra maintenir cette table à jour avec des trigger ou taches planifiées en fonction du besoin de synchronisation.

D'ailleurs, je me demande si votre volumétrie et vos besoins sont vraiment en phase avec l'utilisation de MySQL...

[ben500fr]

Salut ben500fr.
L'inconvénient est le nombre de colonnes que l'on doit créer pour chaque test à faire.

J'ai prévu de générer les colonne a la volée le jour ou on en a besoin, et supprimer les colonnes qui n'ont pas été utilisées depuis 6 mois par exemple.


Mon problème aujourd’hui c'est que pour les nouveaux enregistrements dans la base, les valeurs dans les colonnes 'calculées' sont initialisées comme NULL.
Pour rappel voici le bout de script qui a permis d'ajouter les colonnes:

Code :

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

1
2
ALTER TABLE T1 ADD the_time_15 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 15)* 15)) persistent;
ALTER TABLE T1 add index idx2 (the_time_15);

Vais-je devoir passer par un trigger?

[Artemus24]

Salut ben500fr.

Mon problème aujourd’hui c'est que pour les nouveaux enregistrements dans la base, les valeurs dans les colonnes 'calculées' sont initialisées comme NULL.

Avez-vous vérifié que la colonne "the_time" a été correctement renseigné ?

@+

[ben500fr]

Avez-vous vérifié que la colonne "the_time" a été correctement renseigné ?

Oui tout le couple datetime et valeur sont bien la.

Pour info je ne fais pas des insert, mais:

Code :

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

LOAD DATA LOCAL INFILE monfichier.csv IGNORE INTO TABLE T1

[Artemus24]

Salut Ben500fr.

Voici un exemple qui a été testé sous MariaDB (version 10.3.11) :

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
--------------
START TRANSACTION
--------------
 
--------------
SHOW VARIABLES LIKE 'secure_file_priv'
--------------
 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
--------------
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
( `id`           smallint unsigned not null auto_increment primary key,
  `the_time`     timestamp(3)             null,
  `the_time_15`  timestamp(3)  generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 15)* 15)) persistent
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA INFILE 'E:/33.MariaDB/01.Exercices/Ex_09/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (`the_time`)
--------------
 
--------------
select * from trav
--------------
 
+----+-------------------------+-------------------------+
| id | the_time                | the_time_15             |
+----+-------------------------+-------------------------+
|  1 | 2019-03-01 12:00:00.123 | 2019-03-01 13:00:00.000 |
|  2 | 2019-03-03 13:59:59.456 | 2019-03-03 14:59:45.000 |
+----+-------------------------+-------------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Et voici le fichier qui est lu :

Code :

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

1
2
2019-03-01 12:00:00.123
2019-03-03 13:59:59.456

Vous devez vérifier que vos données dans le fichier ".csv" sont bien au format attendu par votre table.
Ou alors, votre version MariaDB est trop ancienne.
En fait, je ne vois pas trop où peut se trouver votre problème.

Donnez moi un exemple de ce que vous avez dans votre fichier ".csv".

@+

[ben500fr]

Le fichier (j'avais déjà oublie) se présente sous deux colonnes séparées par une tabulation ( \t ).
La première colonne contient un espace entre la date et l'heure.

Code :

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

1
2
3
4
 
2019/03/06 12:00:35.265     -0.05763110997964205
2019/03/06 12:01:07.265     -0.15158348124789712
2019/03/06 12:01:21.267     -0.02779221545618348

[Artemus24]

Salut ben500fr.

C'est normal que cela ne fonctionne pas car dans votre fichier, vous avez trois colonnes ! Le caractère blanc faisant office de séparateur.
Essayez ceci :

Code :

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

1
2
3
4
5
6
7
8
LOAD DATA INFILE 'E:/33.MariaDB/01.Exercices/Ex_09/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY '\t'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES

C'est un fichier de type windows ou de type linux que vous avez ?

@+

[ben500fr]

Pourtant je fais les inserts dans la base avec l'espace dans la date depuis le début. J'ai justement mis la tabulation pour que SQL comprenne.
Et tous les derniers enregistrements sont bien dans la base, il ne manque que les colonnes "calculées".

Par contre je vois que ces colonnes sont par défaut "NULL" au lieu de "none".

Pour le moment le soft qui collecte les data est sous windows: c'est mon laptop qui lance le programme en Java avec Eclipse en mode debug.
Le .jar serra migré sur le serveur Red Hat qui fait tourner mySQL des que j'aurai résolu mes petits soucis windows/Linux... Je reprend un vieux code source codé pour windows.

[Artemus24]

Salut ben500fr.

L'exemple que je vous ai communiqué fonctionne sous MariaDB.
Je n'ai pas de problème lors du chargement de la table "trav" et la colonne calculée me donne bien le résultat attendu.
Que ce soit avec 1 colonne ou avec plusieurs, MySql doit être capable de reconnaître les données à charger.
Si le problème vient du "load", mysql ne sait peut-être pas reconnaître le format de votre ligne et insère peut-être une ligne sur deux.
Je pense que votre "load" n'est pas suffisamment complet (voir mon exemple).
Vous pouvez préciser l'ordre des colonnes à insérer dans la table, le format de la ligne dans ".csv", ainsi que le nombre de lignes à ne pas lire au début du fichier.
Ne pas oublier que si vous faites le chargement sous linux, votre fichier doit-être au format linux.
Pour windows, c'est "line feed" + "carriage return" soit "/r/n". Pour linux, je ne me souviens plus très bien mais je crois que c'est "line feed" soit "/r" (à vérifier).

Si vous utilisez la tabulation "/t", vous devez le préciser dans le load à la ligne "FIELDS TERMINATE BY '\t'".
Autrement dit, c'est le séparateur de vos champs, dans le fichier ".csv".

@+

[ben500fr]

J'ai ajoute le "FILEDS TERMINATED BY '\t' " mais ça ne change rien au problème.
Si je fait un insert manuel, en laissent les colonnes calculées vide, le système les calcule tous seul.
Et si je fais un "OPTIMIZE TABLE" alors les colonnes sont re-calculées correctement. Mais ça ne résout pas le problème a la source...

[Artemus24]

Salut Ben500fr.

En l'état, je ne sais pas résoudre votre problème, mais je peux y jeter un coup d’œil.
Pouvez-vous me communiquer un script mysql, comme je le fais, contenant votre table, un jeu d'essai représentatif de votre problème.
Je ne vais pas relire le sujet, mais quelle est la version mariadb que vous utilisez ?

@+

[ben500fr]

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
 
CREATE DATABASE IF NOT EXISTS DB1;
CREATE TABLE DB1.T1 ( the_time DATETIME(3) NOT NULL, value FLOAT NOT NULL, PRIMARY KEY (the_time) );
 
 
ALTER TABLE DB1.T1 ADD the_time_00 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 1)* 1)) persistent;
ALTER TABLE DB1.T1 add index idx0 (the_time_00);
 
ALTER TABLE DB1.T1 ADD the_time_05 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 5)* 5)) persistent;
ALTER TABLE DB1.T1 add index idx1 (the_time_05);
 
ALTER TABLE DB1.T1 ADD the_time_15 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 15)* 15)) persistent;
ALTER TABLE DB1.T1 add index idx2 (the_time_15);
 
LOAD DATA LOCAL INFILE 'H:/bba_backup/data_debug.data' IGNORE INTO TABLE DB1.T1 FIELDS TERMINATED BY '\t';

et le fichier data_debug.data:

Code :

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

1
2
3
4
5
6
7
8
 
2019/03/06 11:54:50.304	148.99913851756398
2019/03/06 11:54:58.297	149.62387152605504
2019/03/06 11:55:06.297	150.25080239846068
2019/03/06 11:55:14.297	150.8798365298866
2019/03/06 11:55:22.299	151.510880355936
2019/03/06 11:55:30.297	152.14383403250653
2019/03/06 11:57:22.296	158.85420455441383

Il y a un espace entre la date et l'heure, et une tabulation entre l'heure et les valeurs.

Version de MariaDB: 5.5.60

Je n'ai pas pu reproduire la ligne "LOAD DATA LOCAl..." sur phpMyAdmin car le fichier est local et le SQL exécuté sur le serveur...
Merci d'avance

[Artemus24]

Salut Ben500fr.

Ça fonctionne chez moi :

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
--------------
START TRANSACTION
--------------
 
--------------
drop database db1
--------------
 
--------------
commit
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS DB1
--------------
 
--------------
CREATE TABLE DB1.T1 ( the_time DATETIME(3) NOT NULL, value FLOAT NOT NULL, PRIMARY KEY (the_time) )
--------------
 
--------------
ALTER TABLE DB1.T1 ADD the_time_00 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 1)* 1)) persistent
--------------
 
--------------
ALTER TABLE DB1.T1 add index idx0 (the_time_00)
--------------
 
--------------
ALTER TABLE DB1.T1 ADD the_time_05 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 5)* 5)) persistent
--------------
 
--------------
ALTER TABLE DB1.T1 add index idx1 (the_time_05)
--------------
 
--------------
ALTER TABLE DB1.T1 ADD the_time_15 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, '1970-01-01 00:00:00', the_time)/ 15)* 15)) persistent
--------------
 
--------------
ALTER TABLE DB1.T1 add index idx2 (the_time_15)
--------------
 
--------------
LOAD DATA LOCAL INFILE 'E:\\33.MariaDB\\01.Exercices\\Ex_10\\fichier.txt' IGNORE INTO TABLE DB1.T1 FIELDS TERMINATED BY '\t'
--------------
 
--------------
select * from db1.t1
--------------
 
+-------------------------+---------+---------------------+---------------------+---------------------+
| the_time                | value   | the_time_00         | the_time_05         | the_time_15         |
+-------------------------+---------+---------------------+---------------------+---------------------+
| 0000-00-00 00:00:00.000 |       0 | NULL                | NULL                | NULL                |
| 2019-03-06 11:54:50.304 | 148.999 | 2019-03-06 12:54:50 | 2019-03-06 12:54:50 | 2019-03-06 12:54:45 |
| 2019-03-06 11:54:58.297 | 149.624 | 2019-03-06 12:54:58 | 2019-03-06 12:54:55 | 2019-03-06 12:54:45 |
| 2019-03-06 11:55:06.297 | 150.251 | 2019-03-06 12:55:06 | 2019-03-06 12:55:05 | 2019-03-06 12:55:00 |
| 2019-03-06 11:55:14.297 |  150.88 | 2019-03-06 12:55:14 | 2019-03-06 12:55:10 | 2019-03-06 12:55:00 |
| 2019-03-06 11:55:22.299 | 151.511 | 2019-03-06 12:55:22 | 2019-03-06 12:55:20 | 2019-03-06 12:55:15 |
| 2019-03-06 11:55:30.297 | 152.144 | 2019-03-06 12:55:30 | 2019-03-06 12:55:30 | 2019-03-06 12:55:30 |
| 2019-03-06 11:57:22.296 | 158.854 | 2019-03-06 12:57:22 | 2019-03-06 12:57:20 | 2019-03-06 12:57:15 |
+-------------------------+---------+---------------------+---------------------+---------------------+
--------------
COMMIT
--------------
 
 
Appuyez sur une touche pour continuer...

Remarque : la date à zéro est une erreur. Le mieux est de mettre NULL.

Version de MariaDB: 5.5.60

Je l'ai testé sur la version MariaDB 10.3.11.
MariabDB 5.5.60 est une très ancienne version. Il serait plus judicieux de migrer vers une version plus récente et stable.

@+

[ben500fr]

Merci encore pour votre nouvelle réponse

Pour le version de MariaDB je me suis fait avoir: j'ai l’habitude depuis une grosse dizaine d’années de tourner sous Linux Debian: en version "stable" j'ai toujours une logiciel suffisamment a jour pour ne pas me poser la question. Je viens de vérifier, actuellement Debian 9.8 (Stable = "stretch" actuellement) permet d'installer MariaDB 10.1. Ce n'est pas la 10.3 (MariaDB stable actuellement si j'ai bien vu) mais ça reste très bien pour un serveur de production (je pense).
La RedHat que l'on m'a fourni (version 7.6) ne propose que MariaDB 5.5 => il me fautaller cherche une version sur les depot mariadb.org sachant que mon serveur est offline...
En bref: il risque de me falloir plusieurs jours pour mettre le serveur a jour, le temps d'obtenir les autorisations...

Salut Ben500fr.
Remarque : la date à zéro est une erreur. Le mieux est de mettre NULL.
@+

Je ne comprend pas le sens de votre phrase, qu'est ce que "la date à zéro"?
Vous voule que je remplace '1970-01-01 00:00:00' par NULL? Comme ceci:

Code :

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

ALTER TABLE DB1.T1 ADD the_time_00 timestamp(0) generated always AS (FROM_UNIXTIME(floor(TIMESTAMPDIFF(SECOND, NULL, the_time)/ 1)* 1)) persistent;

En tous cas je suis rassure que ça marche avec une version moderne de MariaDB, il me tarde d'avoir fais la mise a jour!

[Artemus24]

Salut Ben500fr.

Je ne comprends pas le sens de votre phrase, qu'est ce que "la date à zéro"?

C'est une date qui est renseigné ainsi : "0000-00-00 00:00:00", comme dans votre exemple à la première ligne du select.

Vous voulez que je remplace '1970-01-01 00:00:00' par NULL?

Cette date est correcte.

@+

[ben500fr]

Le serveur MariaDB est passe a la version 10.3, et je confirme que tout est rentre dans l'ordre! Merci!!!!