modules/calcite/src/test/sql/function/timestamp/test_timestampdiff.test

# name: test/sql/function/test/test_timestampdiff.test
# description: Just simple test to check support of timestampdiff functions
# group: [timestamp]

query T
SELECT TIMESTAMPDIFF(SECOND, TIMESTAMP '2022-02-01 10:30:28.123', TIMESTAMP '2022-02-01 10:32:26')
----
117

query T
SELECT TIMESTAMPDIFF(SECOND, TIMESTAMP '2022-02-01 10:30:28', TIMESTAMP '2022-02-01 10:32:26')
----
118

query T
SELECT TIMESTAMPDIFF(SECOND, TIMESTAMP '2022-02-01 10:30:28', TIMESTAMP '2022-02-02 10:32:26')
----
86518

query T
SELECT TIMESTAMPDIFF(MINUTE, TIMESTAMP '2022-02-01 10:30:28', TIMESTAMP '2022-02-01 10:32:26')
----
1

query T
SELECT TIMESTAMPDIFF(MINUTE, TIMESTAMP '2022-02-01 10:30:25', TIMESTAMP '2022-02-02 11:32:26')
----
1502

query T
SELECT TIMESTAMPDIFF(MINUTE, TIMESTAMP '2022-02-02 11:32:26', TIMESTAMP '2022-02-01 10:30:25')
----
-1502

query T
SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2022-02-01 10:30:28', TIMESTAMP '2022-02-02 10:32:26')
----
24

query T
SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2022-02-01 10:30:25', TIMESTAMP '2022-09-02 11:32:26')
----
5113

query T
SELECT TIMESTAMPDIFF(HOUR, TIMESTAMP '2022-02-02 11:32:26', TIMESTAMP '2023-02-01 10:30:25')
----
8734

query T
SELECT TIMESTAMPDIFF(DAY, TIMESTAMP '2022-02-01 10:30:28', TIMESTAMP '2022-02-02 10:32:26')
----
1

query T
SELECT TIMESTAMPDIFF(DAY, TIMESTAMP '2022-02-01 10:30:25', TIMESTAMP '2022-09-02 11:32:26')
----
213

query T
SELECT TIMESTAMPDIFF(DAY, TIMESTAMP '2022-02-02 11:32:26', TIMESTAMP '2029-02-01 10:30:25')
----
2555

query T
SELECT TIMESTAMPDIFF(WEEK, TIMESTAMP '2022-02-01 10:32:25', TIMESTAMP '2022-02-08 10:32:26')
----
1

query T
SELECT TIMESTAMPDIFF(WEEK, TIMESTAMP '2022-02-01 10:30:25', TIMESTAMP '2022-09-02 11:32:26')
----
30

query T
SELECT TIMESTAMPDIFF(WEEK, TIMESTAMP '2022-02-02 11:32:26', TIMESTAMP '2029-02-01 10:30:25')
----
365

query T
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '2022-09-01 10:32:25', TIMESTAMP '2022-02-08 10:32:26')
----
-6

query T
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '2022-02-01 10:30:25', TIMESTAMP '2028-09-02 11:32:26')
----
79

query T
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '2022-02-02 11:32:26', TIMESTAMP '2099-01-01 10:30:25')
----
922

query T
SELECT TIMESTAMPDIFF(YEAR, TIMESTAMP '2022-09-01 10:32:25', TIMESTAMP '2022-02-08 10:32:26')
----
0

query T
SELECT TIMESTAMPDIFF(YEAR, TIMESTAMP '2022-02-01 10:30:25', TIMESTAMP '2028-09-02 11:32:26')
----
6

query T
SELECT TIMESTAMPDIFF(YEAR, TIMESTAMP '2022-02-02 11:32:26', TIMESTAMP '2099-01-01 10:30:25')
----
76

query T
SELECT TIMESTAMPDIFF(MICROSECOND, TIMESTAMP '2022-02-01 10:30:28.000', TIMESTAMP '2022-02-01 10:30:28.128')
----
128000

query T
SELECT TIMESTAMPDIFF(MICROSECOND, TIMESTAMP '2022-02-01 10:30:26.000', TIMESTAMP '2022-02-01 10:30:28.122')
----
2122000

query T
SELECT TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '2022-02-01 10:30:28.000', TIMESTAMP '2022-02-01 10:30:28.128')
----
128000000

query T
SELECT TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '2022-02-01 10:30:26.000', TIMESTAMP '2022-02-01 10:30:28.122')
----
2122000000

query T
SELECT TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '2022-02-01 10:30:26.000', TIMESTAMP '2022-05-01 10:30:28.122')
----
7689602122000000

query T
SELECT TIMESTAMPDIFF(NANOSECOND, TIMESTAMP '2022-05-01 10:30:28.122', TIMESTAMP '2022-02-01 10:30:26.398')
----
-7689601724000000