mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-08 06:12:55 +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>
390 lines
23 KiB
Plaintext
390 lines
23 KiB
Plaintext
-- Test `time()` and related functions.
|
|
-- Some cases are part of promql/testdata/functions.test, "Test time-related functions" section
|
|
-- And others are from compliance test
|
|
-- time() with itself or scalar
|
|
tql eval (3000, 3000, '1s') time();
|
|
|
|
+---------------------+----------------------+
|
|
| time | time / Float64(1000) |
|
|
+---------------------+----------------------+
|
|
| 1970-01-01T00:50:00 | 3000.0 |
|
|
+---------------------+----------------------+
|
|
|
|
tql eval (0, 0, '1s') time();
|
|
|
|
+---------------------+----------------------+
|
|
| time | time / Float64(1000) |
|
|
+---------------------+----------------------+
|
|
| 1970-01-01T00:00:00 | 0.0 |
|
|
+---------------------+----------------------+
|
|
|
|
tql eval (0.001, 1, '1s') time();
|
|
|
|
+-------------------------+----------------------+
|
|
| time | time / Float64(1000) |
|
|
+-------------------------+----------------------+
|
|
| 1970-01-01T00:00:00.001 | 0.001 |
|
|
+-------------------------+----------------------+
|
|
|
|
tql eval (0, 0, '1s') time() + 1;
|
|
|
|
+---------------------+-----------------------------------+
|
|
| time | time / Float64(1000) + Float64(1) |
|
|
+---------------------+-----------------------------------+
|
|
| 1970-01-01T00:00:00 | 1.0 |
|
|
+---------------------+-----------------------------------+
|
|
|
|
tql eval (0, 0, '1s') 1 + time();
|
|
|
|
+---------------------+-----------------------------------+
|
|
| time | Float64(1) + time / Float64(1000) |
|
|
+---------------------+-----------------------------------+
|
|
| 1970-01-01T00:00:00 | 1.0 |
|
|
+---------------------+-----------------------------------+
|
|
|
|
tql eval (10000, 10000, '1s') time() * time();
|
|
|
|
+---------------------+-----------------------------------------------------+
|
|
| time | lhs.time / Float64(1000) * rhs.time / Float64(1000) |
|
|
+---------------------+-----------------------------------------------------+
|
|
| 1970-01-01T02:46:40 | 100000000.0 |
|
|
+---------------------+-----------------------------------------------------+
|
|
|
|
tql eval (10000, 10000, '1s') time() / time();
|
|
|
|
+---------------------+-----------------------------------------------------+
|
|
| time | lhs.time / Float64(1000) / rhs.time / Float64(1000) |
|
|
+---------------------+-----------------------------------------------------+
|
|
| 1970-01-01T02:46:40 | 1.0 |
|
|
+---------------------+-----------------------------------------------------+
|
|
|
|
-- expect error: parse error: comparisons between scalars must use BOOL modifier
|
|
tql eval (0, 0, '1s') time() < 1;
|
|
|
|
Error: 2000(InvalidSyntax), comparisons between scalars must use BOOL modifier
|
|
|
|
tql eval (0, 0, '1s') time() < bool 1;
|
|
|
|
+---------------------+-----------------------------------+
|
|
| time | time / Float64(1000) < Float64(1) |
|
|
+---------------------+-----------------------------------+
|
|
| 1970-01-01T00:00:00 | 1.0 |
|
|
+---------------------+-----------------------------------+
|
|
|
|
tql eval (0, 0, '1s') time() > bool 1;
|
|
|
|
+---------------------+-----------------------------------+
|
|
| time | time / Float64(1000) > Float64(1) |
|
|
+---------------------+-----------------------------------+
|
|
| 1970-01-01T00:00:00 | 0.0 |
|
|
+---------------------+-----------------------------------+
|
|
|
|
tql eval (1000, 1000, '1s') time() + time();
|
|
|
|
+---------------------+-----------------------------------------------------+
|
|
| time | lhs.time / Float64(1000) + rhs.time / Float64(1000) |
|
|
+---------------------+-----------------------------------------------------+
|
|
| 1970-01-01T00:16:40 | 2000.0 |
|
|
+---------------------+-----------------------------------------------------+
|
|
|
|
-- expect error: parse error: comparisons between scalars must use BOOL modifier
|
|
tql eval (1000, 1000, '1s') time() == time();
|
|
|
|
Error: 2000(InvalidSyntax), comparisons between scalars must use BOOL modifier
|
|
|
|
tql eval (1000, 1000, '1s') time() == bool time();
|
|
|
|
+---------------------+-----------------------------------------------------+
|
|
| time | lhs.time / Float64(1000) = rhs.time / Float64(1000) |
|
|
+---------------------+-----------------------------------------------------+
|
|
| 1970-01-01T00:16:40 | 1.0 |
|
|
+---------------------+-----------------------------------------------------+
|
|
|
|
tql eval (1000, 1000, '1s') time() != bool time();
|
|
|
|
+---------------------+------------------------------------------------------+
|
|
| time | lhs.time / Float64(1000) != rhs.time / Float64(1000) |
|
|
+---------------------+------------------------------------------------------+
|
|
| 1970-01-01T00:16:40 | 0.0 |
|
|
+---------------------+------------------------------------------------------+
|
|
|
|
-- time() with table
|
|
create table metrics (ts timestamp time index, val double);
|
|
|
|
Affected Rows: 0
|
|
|
|
insert into metrics values (0, 0), (1000, 1), (2000, 2), (3000, 3);
|
|
|
|
Affected Rows: 4
|
|
|
|
tql eval (1, 2, '1s') time() + metrics;
|
|
|
|
+---------------------+-------------------------------------+
|
|
| ts | .time / Float64(1000) + metrics.val |
|
|
+---------------------+-------------------------------------+
|
|
| 1970-01-01T00:00:01 | 2.0 |
|
|
| 1970-01-01T00:00:02 | 4.0 |
|
|
+---------------------+-------------------------------------+
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
tql eval (1, 2, '1s') time() == metrics;
|
|
|
|
+---------------------+----------------------+---------------------+-----+
|
|
| time | time / Float64(1000) | ts | val |
|
|
+---------------------+----------------------+---------------------+-----+
|
|
| 1970-01-01T00:00:01 | 1.0 | 1970-01-01T00:00:01 | 1.0 |
|
|
| 1970-01-01T00:00:02 | 2.0 | 1970-01-01T00:00:02 | 2.0 |
|
|
+---------------------+----------------------+---------------------+-----+
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
tql eval (1, 2, '1s') time() == bool metrics;
|
|
|
|
+---------------------+-------------------------------------+
|
|
| ts | .time / Float64(1000) = metrics.val |
|
|
+---------------------+-------------------------------------+
|
|
| 1970-01-01T00:00:01 | 1.0 |
|
|
| 1970-01-01T00:00:02 | 1.0 |
|
|
+---------------------+-------------------------------------+
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
tql eval (1, 2, '1s') metrics + time();
|
|
|
|
+---------------------+-------------------------------------+
|
|
| time | metrics.val + .time / Float64(1000) |
|
|
+---------------------+-------------------------------------+
|
|
| 1970-01-01T00:00:01 | 2.0 |
|
|
| 1970-01-01T00:00:02 | 4.0 |
|
|
+---------------------+-------------------------------------+
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
tql eval (1, 2, '1s') metrics == time();
|
|
|
|
+---------------------+-----+---------------------+----------------------+
|
|
| ts | val | time | time / Float64(1000) |
|
|
+---------------------+-----+---------------------+----------------------+
|
|
| 1970-01-01T00:00:01 | 1.0 | 1970-01-01T00:00:01 | 1.0 |
|
|
| 1970-01-01T00:00:02 | 2.0 | 1970-01-01T00:00:02 | 2.0 |
|
|
+---------------------+-----+---------------------+----------------------+
|
|
|
|
-- SQLNESS SORT_RESULT 3 1
|
|
tql eval (1, 2, '1s') metrics == bool time();
|
|
|
|
+---------------------+-------------------------------------+
|
|
| time | metrics.val = .time / Float64(1000) |
|
|
+---------------------+-------------------------------------+
|
|
| 1970-01-01T00:00:01 | 1.0 |
|
|
| 1970-01-01T00:00:02 | 1.0 |
|
|
+---------------------+-------------------------------------+
|
|
|
|
-- other time-related functions
|
|
tql eval (1, 2, '1s') hour();
|
|
|
|
+---------------------+------------------------------+
|
|
| time | date_part(Utf8("hour"),time) |
|
|
+---------------------+------------------------------+
|
|
| 1970-01-01T00:00:01 | 0 |
|
|
| 1970-01-01T00:00:02 | 0 |
|
|
+---------------------+------------------------------+
|
|
|
|
tql eval (1, 2, '1s') hour(metrics);
|
|
|
|
+---------------------+----------------------------+
|
|
| ts | date_part(Utf8("hour"),ts) |
|
|
+---------------------+----------------------------+
|
|
| 1970-01-01T00:00:01 | 0 |
|
|
| 1970-01-01T00:00:02 | 0 |
|
|
+---------------------+----------------------------+
|
|
|
|
-- 2023-12-01T06:43:43Z
|
|
tql eval (1701413023, 1701413023, '1s') hour();
|
|
|
|
+---------------------+------------------------------+
|
|
| time | date_part(Utf8("hour"),time) |
|
|
+---------------------+------------------------------+
|
|
| 2023-12-01T06:43:43 | 6 |
|
|
+---------------------+------------------------------+
|
|
|
|
tql eval (1701413023, 1701413023, '1s') hour(metrics);
|
|
|
|
++
|
|
++
|
|
|
|
tql eval (1701413023, 1701413023, '1s') minute();
|
|
|
|
+---------------------+--------------------------------+
|
|
| time | date_part(Utf8("minute"),time) |
|
|
+---------------------+--------------------------------+
|
|
| 2023-12-01T06:43:43 | 43 |
|
|
+---------------------+--------------------------------+
|
|
|
|
tql eval (1701413023, 1701413023, '1s') month();
|
|
|
|
+---------------------+-------------------------------+
|
|
| time | date_part(Utf8("month"),time) |
|
|
+---------------------+-------------------------------+
|
|
| 2023-12-01T06:43:43 | 12 |
|
|
+---------------------+-------------------------------+
|
|
|
|
tql eval (1701413023, 1701413023, '1s') year();
|
|
|
|
+---------------------+------------------------------+
|
|
| time | date_part(Utf8("year"),time) |
|
|
+---------------------+------------------------------+
|
|
| 2023-12-01T06:43:43 | 2023 |
|
|
+---------------------+------------------------------+
|
|
|
|
tql eval (1701413023, 1701413023, '1s') day_of_month();
|
|
|
|
+---------------------+-----------------------------+
|
|
| time | date_part(Utf8("day"),time) |
|
|
+---------------------+-----------------------------+
|
|
| 2023-12-01T06:43:43 | 1 |
|
|
+---------------------+-----------------------------+
|
|
|
|
tql eval (1701413023, 1701413023, '1s') day_of_week();
|
|
|
|
+---------------------+-----------------------------+
|
|
| time | date_part(Utf8("dow"),time) |
|
|
+---------------------+-----------------------------+
|
|
| 2023-12-01T06:43:43 | 5 |
|
|
+---------------------+-----------------------------+
|
|
|
|
tql eval (1701413023, 1701413023, '1s') day_of_year();
|
|
|
|
+---------------------+-----------------------------+
|
|
| time | date_part(Utf8("doy"),time) |
|
|
+---------------------+-----------------------------+
|
|
| 2023-12-01T06:43:43 | 335 |
|
|
+---------------------+-----------------------------+
|
|
|
|
-- 2024-01-01T06:43:43Z leap year
|
|
tql eval (1704091423, 1704091423, '1s') day_of_year();
|
|
|
|
+---------------------+-----------------------------+
|
|
| time | date_part(Utf8("doy"),time) |
|
|
+---------------------+-----------------------------+
|
|
| 2024-01-01T06:43:43 | 1 |
|
|
+---------------------+-----------------------------+
|
|
|
|
-- 2023-01-01T06:43:43Z
|
|
tql eval (1672555423, 1672555423, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-01-01T06:43:43 | 31 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-02-01T06:43:43Z
|
|
tql eval (1675233823, 1675233823, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-02-01T06:43:43 | 28 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2024-02-01T06:43:43Z leap year
|
|
tql eval (1706769823, 1706769823, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2024-02-01T06:43:43 | 29 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-03-01T06:43:43Z
|
|
tql eval (1677653023, 1677653023, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-03-01T06:43:43 | 31 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-04-01T06:43:43Z
|
|
tql eval (1680331423, 1680331423, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-04-01T06:43:43 | 30 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-05-01T06:43:43Z
|
|
tql eval (1682923423, 1682923423, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-05-01T06:43:43 | 31 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-06-01T06:43:43Z
|
|
tql eval (1685601823, 1685601823, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-06-01T06:43:43 | 30 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-07-01T06:43:43Z
|
|
tql eval (1688193823, 1688193823, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-07-01T06:43:43 | 31 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-08-01T06:43:43Z
|
|
tql eval (1690872223, 1690872223, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-08-01T06:43:43 | 31 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-09-01T06:43:43Z
|
|
tql eval (1693550623, 1693550623, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-09-01T06:43:43 | 30 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-10-01T06:43:43Z
|
|
tql eval (1696142623, 1696142623, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-10-01T06:43:43 | 31 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-11-01T06:43:43Z
|
|
tql eval (1698821023, 1698821023, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-11-01T06:43:43 | 30 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
-- 2023-12-01T06:43:43Z
|
|
tql eval (1701413023, 1701413023, '1s') days_in_month();
|
|
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| time | date_part(Utf8("day"),date_trunc(Utf8("month"),time) + IntervalYearMonth("1") - IntervalDayTime("IntervalDayTime { days: 1, milliseconds: 0 }")) |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
| 2023-12-01T06:43:43 | 31 |
|
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
drop table metrics;
|
|
|
|
Affected Rows: 0
|
|
|