Skip to content

Commit ec6258f

Browse files
rivudhkmihaibudiu
rivudhk
authored and
mihaibudiu
committed
py: Arithmetic tests for TIME and TIMESTAMP types
Signed-off-by: rivudhk <rivudhkr@gmail.com>
1 parent 7307acf commit ec6258f

4 files changed

Lines changed: 177 additions & 2 deletions

File tree

‎python/tests/arithmetic_tests/main.py‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,8 @@
1010
from tests.aggregate_tests.aggtst_base import * # noqa: F403
1111
from tests.arithmetic_tests.test_tables import * # noqa: F403
1212
from tests.arithmetic_tests.test_date import * # noqa: F403
13+
from tests.arithmetic_tests.test_time import * # noqa: F403
14+
from tests.arithmetic_tests.test_timestamp import * # noqa: F403
1315

1416

1517
def register_tests_in_module(module, ta: TstAccumulator):

‎python/tests/arithmetic_tests/test_tables.py‎

Lines changed: 29 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,8 +2,6 @@
22

33

44
# Left to add dates before the Gregorian calendar
5-
6-
75
class arithtst_date_table(TstTable):
86
"""Define the table used by DATE tests"""
97

@@ -17,3 +15,32 @@ def __init__(self):
1715
{"id": 1, "c1": "2020-06-21", "c2": "2023-02-26"},
1816
{"id": 2, "c1": "1969-06-21", "c2": "1948-12-02"},
1917
]
18+
19+
20+
class arithtst_timestamp_table(TstTable):
21+
"""Define the table used by TIMESTAMP tests"""
22+
23+
def __init__(self):
24+
self.sql = """CREATE TABLE timestamp_tbl(
25+
id INT,
26+
c1 TIMESTAMP NOT NULL,
27+
c2 TIMESTAMP)"""
28+
self.data = [
29+
{"id": 0, "c1": "2019-12-05 08:27:00", "c2": "2014-11-05 12:45:00"},
30+
{"id": 1, "c1": "2020-06-21 14:00:00", "c2": "2023-02-26 18:00:00"},
31+
{"id": 2, "c1": "1959-06-21 11:32:00", "c2": "1948-12-02 09:15:00"}
32+
]
33+
34+
35+
class arithtst_time_table(TstTable):
36+
"""Define the table used by TIME tests"""
37+
38+
def __init__(self):
39+
self.sql = """CREATE TABLE time_tbl(
40+
id INT,
41+
c1 TIME NOT NULL,
42+
c2 TIME)"""
43+
self.data = [
44+
{"id": 0, "c1": "18:30:00", "c2": "12:45:00"},
45+
{"id": 1, "c1": "08:00:00", "c2": "14:00:00"}
46+
]

‎python/tests/arithmetic_tests/test_time.py‎

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
from tests.aggregate_tests.aggtst_base import TstView
2+
3+
4+
class arithtst_time_minus_time(TstView):
5+
def __init__(self):
6+
# Result validation is not required for local views
7+
self.data = []
8+
self.sql = """CREATE LOCAL VIEW time_minus_time AS SELECT
9+
id,
10+
(c1-c2)SECOND AS c1_minus_c2
11+
FROM time_tbl"""
12+
13+
14+
class arithtst_time_minus_time_seconds(TstView):
15+
def __init__(self):
16+
# Validated on Postgres
17+
self.data = [
18+
{'id': 0, 'c1_minus_c2_seconds': 20700},
19+
{'id': 1, 'c1_minus_c2_seconds': -21600}
20+
]
21+
self.sql = """CREATE MATERIALIZED VIEW time_minus_time_seconds AS SELECT
22+
id,
23+
CAST((c1_minus_c2) AS BIGINT) AS c1_minus_c2_seconds
24+
FROM time_minus_time"""
25+
26+
# Equivalent SQL for Postgres
27+
# CREATE TABLE time_sub_time AS
28+
# SELECT
29+
# id,
30+
# (c1 - c2) AS c1_minus_c2_days
31+
# FROM time_tbl;
32+
#
33+
# SELECT
34+
# id,
35+
# EXTRACT(EPOCH FROM c1_minus_c2_days) AS c1_minus_c2_seconds
36+
# FROM time_sub_time;
37+
38+
39+
class arithtst_time_minus_interval(TstView):
40+
def __init__(self):
41+
# Validated on Postgres
42+
self.data = [
43+
{'id': 0, 'c1': '18:20:00', 'c2': '08:45:00'},
44+
{'id': 1, 'c1': '07:50:00', 'c2': '10:00:00'}
45+
]
46+
self.sql = """CREATE MATERIALIZED VIEW time_minus_interval AS SELECT
47+
id,
48+
c1 - INTERVAL '10' MINUTES AS c1,
49+
c2 - INTERVAL '4' HOUR AS c2
50+
FROM time_tbl"""
51+
52+
53+
class arithtst_time_plus_interval(TstView):
54+
def __init__(self):
55+
# Validated on Postgres
56+
self.data = [
57+
{'id': 0, 'c1': '18:40:00', 'c2': '16:45:00'},
58+
{'id': 1, 'c1': '08:10:00', 'c2': '18:00:00'}
59+
]
60+
self.sql = """CREATE MATERIALIZED VIEW time_plus_interval AS SELECT
61+
id,
62+
c1 + INTERVAL '10' MINUTES AS c1,
63+
c2 + INTERVAL '4' HOUR AS c2
64+
FROM time_tbl"""
65+
66+
67+
# Equivalent SQL for Postgres
68+
# SELECT
69+
# (c1 + INTERVAL '10 minute') AS c1,
70+
# (c2 + INTERVAL '4 hour') AS c2
71+
# FROM time_tbl;

‎python/tests/arithmetic_tests/test_timestamp.py‎

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,75 @@
1+
from tests.aggregate_tests.aggtst_base import TstView
2+
3+
4+
class arithtst_timestamp_minus_timestamp(TstView):
5+
def __init__(self):
6+
# Result validation is not required for local views
7+
self.data = []
8+
self.sql = """CREATE LOCAL VIEW timestamp_minus_timestamp AS SELECT
9+
id,
10+
(c1-c2)SECOND AS c1_minus_c2
11+
FROM timestamp_tbl"""
12+
13+
14+
class arithtst_timestamp_minus_timestamp_seconds(TstView):
15+
def __init__(self):
16+
# Validated on Postgres
17+
self.data = [
18+
{'id': 0, 'c1_minus_c2_seconds': 160342920},
19+
{'id': 1, 'c1_minus_c2_seconds': -84686400},
20+
{'id': 2, 'c1_minus_c2_seconds': 332907420}
21+
]
22+
self.sql = """CREATE MATERIALIZED VIEW timestamp_minus_timestamp_seconds AS SELECT
23+
id,
24+
CAST((c1_minus_c2) AS BIGINT) AS c1_minus_c2_seconds
25+
FROM timestamp_minus_timestamp"""
26+
27+
28+
# Equivalent SQL for Postgres
29+
# CREATE TABLE timestamp_minus_timestamp AS
30+
# SELECT
31+
# id,
32+
# c1 - c2 AS c1_minus_c2_days
33+
# FROM timestamp_tbl;
34+
#
35+
# SELECT
36+
# id,
37+
# EXTRACT(EPOCH FROM c1_minus_c2_days) AS c1_minus_c2_seconds
38+
# FROM timestamp_minus_timestamp ;
39+
40+
41+
class arithtst_timestamp_minus_interval(TstView):
42+
def __init__(self):
43+
# Validated on Postgres
44+
self.data = [
45+
{'id': 0, 'c1': '2019-11-05T08:27:00', 'c2': '2013-11-05T12:45:00'},
46+
{'id': 1, 'c1': '2020-05-22T14:00:00', 'c2': '2022-02-26T18:00:00'},
47+
{'id': 2, 'c1': '1959-05-22T11:32:00', 'c2': '1947-12-03T09:15:00'}
48+
]
49+
self.sql = """CREATE MATERIALIZED VIEW timestamp_minus_interval AS SELECT
50+
id,
51+
c1 - INTERVAL '2592000' SECOND AS c1,
52+
c2 - INTERVAL '31536000' SECOND AS c2
53+
FROM timestamp_tbl"""
54+
55+
56+
class arithtst_timestamp_plus_interval(TstView):
57+
def __init__(self):
58+
# Validated on Postgres
59+
self.data = [
60+
{'id': 0, 'c1': '2020-01-04T08:27:00', 'c2': '2015-11-05T12:45:00'},
61+
{'id': 1, 'c1': '2020-07-21T14:00:00', 'c2': '2024-02-26T18:00:00'},
62+
{'id': 2, 'c1': '1959-07-21T11:32:00', 'c2': '1949-12-02T09:15:00'}
63+
]
64+
self.sql = """CREATE MATERIALIZED VIEW timestamp_plus_interval AS SELECT
65+
id,
66+
c1 + INTERVAL '2592000' SECOND AS c1,
67+
c2 + INTERVAL '31536000' SECOND AS c2
68+
FROM timestamp_tbl"""
69+
70+
71+
# Equivalent SQL for Postgres
72+
# SELECT
73+
# (c1 + INTERVAL '2592000 second') AS c1,
74+
# (c2 + INTERVAL '31536000 second') AS c2
75+
# FROM timestamp_tbl;

0 commit comments

Comments
 (0)