forked from MaterializeInc/materialize
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatediff.slt
135 lines (115 loc) · 3.46 KB
/
datediff.slt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
mode cockroach
query IIIII
SELECT
datediff('year', '2023-05-08', '2023-06-07'),
datediff('month', '2023-05-08', '2023-06-07'),
datediff('week', '2023-05-08', '2023-06-07'),
datediff('day', '2023-05-08', '2023-06-07'),
datediff('hour', '2023-05-08', '2023-06-07')
----
0 1 4 30 720
query IIIIIIII
SELECT
datediff('millennia', '2000-12-31', '2001-01-01'),
datediff('century', '2000-12-31', '2001-01-01'),
datediff('decade', '2000-12-31', '2001-01-01'),
datediff('year', '2000-12-31', '2001-01-01'),
datediff('quarter', '2000-12-31', '2001-01-01'),
datediff('month', '2000-12-31', '2001-01-01'),
datediff('year', '2000-12-31', '2001-01-01'),
datediff('day', '2000-12-31', '2001-01-01')
----
1 1 0 1 1 1 1 1
query IIIII
SELECT
datediff('year', '1998-01-01', '2000-06-01'),
datediff('quarter', '1998-01-01', '2000-06-01'),
datediff('month', '1998-01-01', '2000-06-01'),
datediff('week', '1998-01-01', '2000-06-01'),
datediff('day', '1998-01-01', '2000-06-01')
----
2 9 29 126 882
query I
SELECT datediff('month', '2023-05-08', '2024-06-08');
----
13
query I
SELECT datediff('day', '2023-06-08', '2023-06-07');
----
-1
# Make sure we correctly handle leap years
query I
SELECT datediff('day', '2004-03-01', '2004-02-28');
----
-2
query I
SELECT datediff('day', '2005-03-01', '2005-02-28');
----
-1
query I
SELECT datediff('day', '2005-02-01', '2004-02-01');
----
-366
query I
SELECT datediff('day', '2004-02-01', '2005-02-01');
----
366
query I
SELECT datediff('day', '2005-03-01', '2004-03-01');
----
-365
query I
SELECT datediff('hour', '2017/08/25 07:00', '2017/08/25 12:45');
----
5
query I
SELECT datediff('hour', '2023-01-01', '2023-01-03 05:04:03');
----
53
query I
SELECT datediff('days', '2008-06-01 09:59:59 EST', '2008-07-04 09:59:59 EST');
----
33
query T
SELECT to_timestamp(-210833720368);
----
4713-12-10 21:40:32+00 BC
query T
SELECT to_timestamp(0);
----
1970-01-01 00:00:00+00
query T
SELECT to_timestamp(8200000000000);
----
261817-08-28 09:46:40+00
query T
SELECT datediff('mil', '0001-01-01', '2000-01-01');
----
1
query IIIII
SELECT
datediff('usec', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('ms', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('s', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('m', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('h', to_timestamp(-210833720368), to_timestamp(8200000000000))
----
8410833720368000000 8410833720368000 8410833720368 140180562006 2336342700
query IIIIII
SELECT
datediff('days', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('months', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('years', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('decade', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('century', to_timestamp(-210833720368), to_timestamp(8200000000000)),
datediff('millennia', to_timestamp(-210833720368), to_timestamp(8200000000000))
----
97347612 3198344 266529 26653 2667 267