[ArchivingRoot] archiving every 100ms with TdbArchiver
avatar par défaut
JCM
Hello,

I would like to know if the ms are copied by TdbArchiver into the TDB table, knowing that they are in the .dat file?
Because I cannot retrieve the ms from the TDB database (data archived every 100ms).
Both in the database with the 'select' command and with a python script and the 'GetAttDataBetweenDates' command…

I put prints below :)

THANKS.

extracted from the .dat file:

2024-07-09 11:20:17.089,
5.12820528820157050E-03
%%%
2024-07-09 11:20:17.188,
6.83760736137628560E-03
%%%
2024-07-09 11:20:17.389,
6.83760736137628560E-03
%%%
2024-07-09 11:20:17.488,
8.54700896888971300E-03
%%%
2024-07-09 11:20:17.788,
1.02564105764031410E-02
%%%
2024-07-09 11:20:17.888,
8.54700896888971300E-03
%%%
mysql commands:

MariaDB [tdb] > select * from att_00181 where ((time >= '2024:07:09 11:20:17.000') and (time <= '2024:07:09 11:20:17.999'));
+———————-+———————–+
| time | value |
+———————-+———————–+
| 2024-07-09 11:20:17 | 0.0051282052882015705 |
| 2024-07-09 11:20:17 | 0.0068376073613762856 |
| 2024-07-09 11:20:17 | 0.0068376073613762856 |
| 2024-07-09 11:20:17 | 0.008547008968889713 |
| 2024-07-09 11:20:17 | 0.010256410576403141 |
| 2024-07-09 11:20:17 | 0.008547008968889713 |
+———————-+———————–+
6 rows in set (3.42 sec)

MariaDB [tdb]> select DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s.%f') from att_00181 where time like '2024-07-09 11:20:17%' ;
+——————————————–+
| DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s.%f') |
+——————————————–+
| 2024-07-09 11:20:17.000000 |
| 2024-07-09 11:20:17.000000 |
| 2024-07-09 11:20:17.000000 |
| 2024-07-09 11:20:17.000000 |
| 2024-07-09 11:20:17.000000 |
| 2024-07-09 11:20:17.000000 |
+——————————————–+
6 rows in set (3.81 sec)

extract from the python script with a DeviceProxy:

>>>[N,], [name,] = extractor.GetAttDataBetweenDates(["er/sy/rac.17-elr.01-rdp.03/ai_2","2024-07-09 11:20:17" ,"2024-07-09 11:20:17"])
>>>attrHist = extractor.attribute_history(name, N)
>>>values ​​= np.empty(N, dtype=float)
>>>dates = np.empty(N, dtype=object)
>>> for i_h in range(N):
… values[i_h]=attrHist[i_h].value
… dates[i_h]=attrHist[i_h].time.todatetime()
…
>>>print(values)
array([0.00512821, 0.00683761, 0.00683761, 0.00854701, 0.01025641,
0.00854701])
>>>print(dates)
array([datetime.datetime(2024, 7, 9, 11, 20, 17),
datetime.datetime(2024, 7, 9, 11, 20, 17),
datetime.datetime(2024, 7, 9, 11, 20, 17),
datetime.datetime(2024, 7, 9, 11, 20, 17),
datetime.datetime(2024, 7, 9, 11, 20, 17),
datetime.datetime(2024, 7, 9, 11, 20, 17)], dtype=object)
avatar par défaut
JCM
Hi,

I may have found the explanation and I can try to give an answer.
To be confirmed though ;)

The ms seem to be archived well but on the other hand the datetime type in MariaDB does not contain the milliseconds!

The precision of datetime depends on the SQL base

Must modify the format in the table:
MariaDB [tdb]> ALTER TABLE `att_00181` MODIFY `time` datetime(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000';

Checking the result after the `ALTER TABLE`:

With mysql:
MariaDB [tdb]> SELECT DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s.%f') FROM att_00181 WHERE time LIKE '2024-07-18 00:00:00%';
+——————————————-+
| DATE_FORMAT(time, '%Y-%m-%d %H:%i:%s.%f') |
+——————————————-+
| 2024-07-18 00:00:00.061000 |
| 2024-07-18 00:00:00.161000 |
| 2024-07-18 00:00:00.261000 |
| 2024-07-18 00:00:00.360000 |
| 2024-07-18 00:00:00.461000 |
| 2024-07-18 00:00:00.610000 |
| 2024-07-18 00:00:00.711000 |
| 2024-07-18 00:00:00.811000 |
| 2024-07-18 00:00:00.911000 |
+——————————————-+
9 rows in set (7.88 sec)

MariaDB [tdb]> SELECT * FROM att_00181 WHERE ((time >= '2024:07:18 00:00:00.000') AND (time <= '2024:07:18 00:00:05.000'));
+————————-+———————–+
| time | value |
+————————-+———————–+
| 2024-07-18 00:00:00.061 | 0.010256410576403141 |
| 2024-07-18 00:00:00.161 | 0.010256410576403141 |
| 2024-07-18 00:00:00.261 | 0.010256410576403141 |
| 2024-07-18 00:00:00.360 | 0.0068376073613762856 |
…
| 2024-07-18 00:00:04.811 | 0.008547008968889713 |
| 2024-07-18 00:00:04.910 | 0.008547008968889713 |
+————————-+———————–+
46 rows in set (6.99 sec)

With the use of TdbExtractor:
[datetime.datetime(2024, 7, 18, 0, 0, 0, 61000)
datetime.datetime(2024, 7, 18, 0, 0, 0, 161000)
datetime.datetime(2024, 7, 18, 0, 0, 0, 261000)
datetime.datetime(2024, 7, 18, 0, 0, 0, 360000)
…
datetime.datetime(2024, 7, 18, 0, 0, 4, 811000)
datetime.datetime(2024, 7, 18, 0, 0, 4, 910000)]
 
Register or login to create to post a reply.