After I have repaired the data using the timestamp_repair function, filtering the result set using the WHERE keyword does not give me the correct results.
- Create table
CREATE table wzz(value double);
- Writing data sets
INSERT wzz VALUES
('2024-01-01T00:00:00.000',1),
('2024-01-01T00:00:10.000',2),
('2024-01-01T00:00:19.000',3),
('2024-01-01T00:00:30.000',4),
('2024-01-01T00:00:40.000',5),
('2024-01-01T00:00:50.000',6),
('2024-01-01T00:01:01.000',7),
('2024-01-01T00:01:11.000',8),
('2024-01-01T00:01:21.000',9),
('2024-01-01T00:01:31.000',10);
- Repairing data with
timestamp_repair
SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') FROM wzz;
The following result is returned:
+-------------------------+------------------------------------------------------------------------------+
| time | timestamp_repair(wzz.time, wzz.value, Utf8("method=mode&start_mode=linear")) |
+-------------------------+------------------------------------------------------------------------------+
| 2024-01-01T00:00:00.300 | 1.0 |
| 2024-01-01T00:00:10.300 | 2.0 |
| 2024-01-01T00:00:20.300 | 3.0 |
| 2024-01-01T00:00:30.300 | 4.0 |
| 2024-01-01T00:00:40.300 | 5.0 |
| 2024-01-01T00:00:50.300 | 6.0 |
| 2024-01-01T00:01:00.300 | 7.0 |
| 2024-01-01T00:01:10.300 | 8.0 |
| 2024-01-01T00:01:20.300 | 9.0 |
| 2024-01-01T00:01:30.300 | 10.0 |
| 2024-01-01T00:01:40.300 | NaN |
+-------------------------+------------------------------------------------------------------------------+
- Filtering of repaired data
Because the result set has an extra piece of redundant data
2024-01-01T00:01:40.300 | NaN
Remove all data that requires the value to equal NaN.
So here are a few of the various writing styles I tried that failed to achieve the desired effect:
(1)
SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') FROM wzz WHERE timestamp_repair(time, value, 'method=mode&start_mode=linear') != 'NaN';
Returns an error: 422 Unprocessable Entity, details: {"error_code":"010001","error_message":"Datafusion: This feature is not implemented: timestamp_repair is not yet implemented"}
(2)
SELECT timestamp_repair(time, value, 'method=mode&start_mode=linear') as f1 FROM wzz WHERE f1 != 'NaN';
Returns an error: 422 Unprocessable Entity, details: {"error_code":"010001","error_message":"Datafusion: Schema error: No field named f1. Valid fields are wzz.time, wzz.value."}
I would like to be able to remove data in the result set that is NaN, i.e.
+-------------------------+------------------------------------------------------------------------------+
| time | timestamp_repair(wzz.time, wzz.value, Utf8("method=mode&start_mode=linear")) |
+-------------------------+------------------------------------------------------------------------------+
| 2024-01-01T00:00:00.300 | 1.0 |
| 2024-01-01T00:00:10.300 | 2.0 |
| 2024-01-01T00:00:20.300 | 3.0 |
| 2024-01-01T00:00:30.300 | 4.0 |
| 2024-01-01T00:00:40.300 | 5.0 |
| 2024-01-01T00:00:50.300 | 6.0 |
| 2024-01-01T00:01:00.300 | 7.0 |
| 2024-01-01T00:01:10.300 | 8.0 |
| 2024-01-01T00:01:20.300 | 9.0 |
| 2024-01-01T00:01:30.300 | 10.0 |
+-------------------------+------------------------------------------------------------------------------+