mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-10 15:22:56 +00:00
7402320abc
* fix: close issue_6701 phase 1 make it return now Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: tests Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: make tests stable Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: drop useless tests Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: address comments Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: close issue_6701 phase 1 make it return now Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: tests Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: make tests stable Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: drop useless tests Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: address comments Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: make time() real right Signed-off-by: yihong0618 <zouzou0208@gmail.com> * fix: fix tests Signed-off-by: yihong0618 <zouzou0208@gmail.com> * add two sqlness cases Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * simplify impl Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: yihong0618 <zouzou0208@gmail.com> Signed-off-by: Ruihang Xia <waynestxia@gmail.com> Co-authored-by: Ruihang Xia <waynestxia@gmail.com>
173 lines
5.4 KiB
Plaintext
173 lines
5.4 KiB
Plaintext
-- Test `timestamp()` function
|
|
-- timestamp() returns the timestamp of each sample as seconds since Unix epoch
|
|
create table timestamp_test (ts timestamp time index, val double);
|
|
|
|
Affected Rows: 0
|
|
|
|
insert into timestamp_test values
|
|
(0, 1.0),
|
|
(1000, 2.0),
|
|
(60000, 3.0),
|
|
(3600000, 4.0),
|
|
-- 2021-01-01 00:00:00
|
|
(1609459200000, 5.0),
|
|
-- 2021-01-01 00:01:00
|
|
(1609459260000, 6.0);
|
|
|
|
Affected Rows: 6
|
|
|
|
-- Test timestamp() with time series
|
|
tql eval (0, 3600, '30s') timestamp(timestamp_test);
|
|
|
|
+---------------------+--------+
|
|
| ts | value |
|
|
+---------------------+--------+
|
|
| 1970-01-01T00:00:00 | 0.0 |
|
|
| 1970-01-01T00:00:30 | 1.0 |
|
|
| 1970-01-01T00:01:00 | 60.0 |
|
|
| 1970-01-01T00:01:30 | 60.0 |
|
|
| 1970-01-01T00:02:00 | 60.0 |
|
|
| 1970-01-01T00:02:30 | 60.0 |
|
|
| 1970-01-01T00:03:00 | 60.0 |
|
|
| 1970-01-01T00:03:30 | 60.0 |
|
|
| 1970-01-01T00:04:00 | 60.0 |
|
|
| 1970-01-01T00:04:30 | 60.0 |
|
|
| 1970-01-01T00:05:00 | 60.0 |
|
|
| 1970-01-01T00:05:30 | 60.0 |
|
|
| 1970-01-01T00:06:00 | 60.0 |
|
|
| 1970-01-01T01:00:00 | 3600.0 |
|
|
+---------------------+--------+
|
|
|
|
-- Test timestamp() with specific time range
|
|
tql eval (0, 60, '30s') timestamp(timestamp_test);
|
|
|
|
+---------------------+-------+
|
|
| ts | value |
|
|
+---------------------+-------+
|
|
| 1970-01-01T00:00:00 | 0.0 |
|
|
| 1970-01-01T00:00:30 | 1.0 |
|
|
| 1970-01-01T00:01:00 | 60.0 |
|
|
+---------------------+-------+
|
|
|
|
tql eval (0, 60, '30s') -timestamp(timestamp_test);
|
|
|
|
+---------------------+-----------+
|
|
| ts | (- value) |
|
|
+---------------------+-----------+
|
|
| 1970-01-01T00:00:00 | -0.0 |
|
|
| 1970-01-01T00:00:30 | -1.0 |
|
|
| 1970-01-01T00:01:00 | -60.0 |
|
|
+---------------------+-----------+
|
|
|
|
-- Test timestamp() with 2021 data
|
|
tql eval (1609459200, 1609459260, '30s') timestamp(timestamp_test);
|
|
|
|
+---------------------+--------------+
|
|
| ts | value |
|
|
+---------------------+--------------+
|
|
| 2021-01-01T00:00:00 | 1609459200.0 |
|
|
| 2021-01-01T00:00:30 | 1609459200.0 |
|
|
| 2021-01-01T00:01:00 | 1609459260.0 |
|
|
+---------------------+--------------+
|
|
|
|
-- Test timestamp() with arithmetic operations
|
|
tql eval (0, 60, '30s') timestamp(timestamp_test) + 1;
|
|
|
|
+---------------------+--------------------+
|
|
| ts | value + Float64(1) |
|
|
+---------------------+--------------------+
|
|
| 1970-01-01T00:00:00 | 1.0 |
|
|
| 1970-01-01T00:00:30 | 2.0 |
|
|
| 1970-01-01T00:01:00 | 61.0 |
|
|
+---------------------+--------------------+
|
|
|
|
-- Test timestamp() with boolean operations
|
|
tql eval (0, 60, '30s') timestamp(timestamp_test) > bool 30;
|
|
|
|
+---------------------+---------------------+
|
|
| ts | value > Float64(30) |
|
|
+---------------------+---------------------+
|
|
| 1970-01-01T00:00:00 | 0.0 |
|
|
| 1970-01-01T00:00:30 | 0.0 |
|
|
| 1970-01-01T00:01:00 | 1.0 |
|
|
+---------------------+---------------------+
|
|
|
|
-- Test timestamp() with time functions
|
|
tql eval (0, 60, '30s') timestamp(timestamp_test) - time();
|
|
|
|
+---------------------+----------------------------------------------+
|
|
| time | timestamp_test.value - .time / Float64(1000) |
|
|
+---------------------+----------------------------------------------+
|
|
| 1970-01-01T00:00:00 | 0.0 |
|
|
| 1970-01-01T00:00:30 | -29.0 |
|
|
| 1970-01-01T00:01:00 | 0.0 |
|
|
+---------------------+----------------------------------------------+
|
|
|
|
-- Test timestamp() with other functions
|
|
tql eval (0, 60, '30s') abs(timestamp(timestamp_test) - avg(timestamp(timestamp_test))) > 20;
|
|
|
|
++
|
|
++
|
|
|
|
-- Test Issue 6707
|
|
tql eval timestamp(demo_memory_usage_bytes * 1);
|
|
|
|
++
|
|
++
|
|
|
|
tql eval timestamp(-demo_memory_usage_bytes);
|
|
|
|
++
|
|
++
|
|
|
|
tql eval (0, 60, '30s') timestamp(timestamp_test) == 60;
|
|
|
|
+---------------------+-------+
|
|
| ts | value |
|
|
+---------------------+-------+
|
|
| 1970-01-01T00:01:00 | 60.0 |
|
|
+---------------------+-------+
|
|
|
|
-- Test timestamp() with multiple metrics
|
|
create table timestamp_test2 (ts timestamp time index, val double);
|
|
|
|
Affected Rows: 0
|
|
|
|
insert into timestamp_test2 values
|
|
(0, 10.0),
|
|
(1000, 20.0),
|
|
(60000, 30.0);
|
|
|
|
Affected Rows: 3
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
tql eval (0, 60, '30s') timestamp(timestamp_test) + timestamp(timestamp_test2);
|
|
|
|
+---------------------+----------------------------------------------+
|
|
| ts | timestamp_test.value + timestamp_test2.value |
|
|
+---------------------+----------------------------------------------+
|
|
| 1970-01-01T00:00:00 | 0.0 |
|
|
| 1970-01-01T00:00:30 | 2.0 |
|
|
| 1970-01-01T00:01:00 | 120.0 |
|
|
+---------------------+----------------------------------------------+
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
tql eval (0, 60, '30s') timestamp(timestamp_test) == timestamp(timestamp_test2);
|
|
|
|
+---------------------+-------+---------------------+-------+
|
|
| ts | value | ts | value |
|
|
+---------------------+-------+---------------------+-------+
|
|
| 1970-01-01T00:00:00 | 0.0 | 1970-01-01T00:00:00 | 0.0 |
|
|
| 1970-01-01T00:00:30 | 1.0 | 1970-01-01T00:00:30 | 1.0 |
|
|
| 1970-01-01T00:01:00 | 60.0 | 1970-01-01T00:01:00 | 60.0 |
|
|
+---------------------+-------+---------------------+-------+
|
|
|
|
drop table timestamp_test;
|
|
|
|
Affected Rows: 0
|
|
|
|
drop table timestamp_test2;
|
|
|
|
Affected Rows: 0
|
|
|