1#include "catch.hpp"
2#include "duckdb/common/types/timestamp.hpp"
3#include "test_helpers.hpp"
4#include "duckdb/common/types/date.hpp"
5#include "duckdb/common/types/time.hpp"
6
7using namespace duckdb;
8using namespace std;
9
10TEST_CASE("Test TIMESTAMP type", "[timestamp]") {
11 unique_ptr<QueryResult> result;
12 DuckDB db(nullptr);
13 Connection con(db);
14 con.EnableQueryVerification();
15
16 // creates a timestamp table with a timestamp column and inserts a value
17 REQUIRE_NO_FAIL(con.Query("CREATE TABLE IF NOT EXISTS timestamp (t TIMESTAMP);"));
18 REQUIRE_NO_FAIL(con.Query(
19 "INSERT INTO timestamp VALUES ('2008-01-01 00:00:01'), (NULL), ('2007-01-01 00:00:01'), ('2008-02-01 "
20 "00:00:01'), "
21 "('2008-01-02 00:00:01'), ('2008-01-01 10:00:00'), ('2008-01-01 00:10:00'), ('2008-01-01 00:00:10')"));
22
23 // check if we can select timestamps
24 result = con.Query("SELECT timestamp '2017-07-23 13:10:11';");
25 REQUIRE(result->sql_types[0] == SQLType::TIMESTAMP);
26 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2017-07-23 13:10:11"))}));
27 // check order
28 result = con.Query("SELECT t FROM timestamp ORDER BY t;");
29 REQUIRE(CHECK_COLUMN(result, 0,
30 {Value(), Value::BIGINT(Timestamp::FromString("2007-01-01 00:00:01")),
31 Value::BIGINT(Timestamp::FromString("2008-01-01 00:00:01")),
32 Value::BIGINT(Timestamp::FromString("2008-01-01 00:00:10")),
33 Value::BIGINT(Timestamp::FromString("2008-01-01 00:10:00")),
34 Value::BIGINT(Timestamp::FromString("2008-01-01 10:00:00")),
35 Value::BIGINT(Timestamp::FromString("2008-01-02 00:00:01")),
36 Value::BIGINT(Timestamp::FromString("2008-02-01 00:00:01"))}));
37
38 result = con.Query("SELECT MIN(t) FROM timestamp;");
39 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2007-01-01 00:00:01"))}));
40
41 result = con.Query("SELECT MAX(t) FROM timestamp;");
42 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2008-02-01 00:00:01"))}));
43
44 // can't sum/avg timestamps
45 REQUIRE_FAIL(con.Query("SELECT SUM(t) FROM timestamp"));
46 REQUIRE_FAIL(con.Query("SELECT AVG(t) FROM timestamp"));
47 // can't add/multiply/divide timestamps
48 REQUIRE_FAIL(con.Query("SELECT t+t FROM timestamp"));
49 REQUIRE_FAIL(con.Query("SELECT t*t FROM timestamp"));
50 REQUIRE_FAIL(con.Query("SELECT t/t FROM timestamp"));
51 REQUIRE_FAIL(con.Query("SELECT t%t FROM timestamp"));
52 // FIXME: we can subtract timestamps!
53 // REQUIRE_NO_FAIL(con.Query("SELECT t-t FROM timestamp"));
54
55 // test YEAR function
56 result = con.Query("SELECT YEAR(TIMESTAMP '1992-01-01 01:01:01');");
57 REQUIRE(CHECK_COLUMN(result, 0, {1992}));
58 result = con.Query("SELECT YEAR(TIMESTAMP '1992-01-01 01:01:01'::DATE);");
59 REQUIRE(CHECK_COLUMN(result, 0, {1992}));
60 // test casting timestamp
61 result = con.Query("SELECT (TIMESTAMP '1992-01-01 01:01:01')::DATE;");
62 REQUIRE(CHECK_COLUMN(result, 0, {Value::DATE(1992, 1, 1)}));
63 result = con.Query("SELECT (TIMESTAMP '1992-01-01 01:01:01')::TIME;");
64 REQUIRE(CHECK_COLUMN(result, 0, {Value::TIME(1, 1, 1, 0)}));
65 // scalar timestamp
66 result = con.Query("SELECT t::DATE FROM timestamp WHERE EXTRACT(YEAR from t)=2007 ORDER BY 1");
67 REQUIRE(CHECK_COLUMN(result, 0, {Value::DATE(2007, 1, 1)}));
68 result = con.Query("SELECT t::TIME FROM timestamp WHERE EXTRACT(YEAR from t)=2007 ORDER BY 1");
69 REQUIRE(CHECK_COLUMN(result, 0, {Value::TIME(0, 0, 1, 0)}));
70 // date -> timestamp
71 result = con.Query("SELECT (DATE '1992-01-01')::TIMESTAMP;");
72 REQUIRE(CHECK_COLUMN(result, 0, {Value::TIMESTAMP(1992, 1, 1, 0, 0, 0, 0)}));
73
74 // test timestamp with ms
75 result = con.Query("SELECT TIMESTAMP '2008-01-01 00:00:01.5'::VARCHAR");
76 REQUIRE(CHECK_COLUMN(result, 0, {"2008-01-01 00:00:01.500"}));
77 // test timestamp with BC date
78 result = con.Query("SELECT TIMESTAMP '-8-01-01 00:00:01.5'::VARCHAR");
79 REQUIRE(CHECK_COLUMN(result, 0, {"0008-01-01 (BC) 00:00:01.500"}));
80 // test timestamp with large date
81 // FIXME:
82 // result = con.Query("SELECT TIMESTAMP '100000-01-01 00:00:01.5'::VARCHAR");
83 // REQUIRE(CHECK_COLUMN(result, 0, {"100000-01-01 (BC) 00:00:01.500"}));
84}
85
86TEST_CASE("Test out of range/incorrect timestamp formats", "[timestamp]") {
87 unique_ptr<QueryResult> result;
88 DuckDB db(nullptr);
89 Connection con(db);
90 con.EnableQueryVerification();
91
92 // create and insert into table
93 REQUIRE_NO_FAIL(con.Query("CREATE TABLE timestamp(t TIMESTAMP)"));
94 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('blabla')"));
95 // month out of range
96 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1993-20-14 00:00:00')"));
97 // day out of range
98 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1993-08-99 00:00:00')"));
99 // day out of range because not a leapyear
100 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1993-02-29 00:00:00')"));
101 // day out of range because not a leapyear
102 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1900-02-29 00:00:00')"));
103 // day in range because of leapyear
104 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES ('1992-02-29 00:00:00')"));
105 // day in range because of leapyear
106 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES ('2000-02-29 00:00:00')"));
107
108 // test incorrect timestamp formats
109 // dd-mm-YYYY
110 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('02-02-1992 00:00:00')"));
111 // ss-mm-hh
112 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1900-1-1 59:59:23')"));
113 // different separators are not supported
114 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1900a01a01 00:00:00')"));
115 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1900-1-1 00;00;00')"));
116 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1900-1-1 00a00a00')"));
117 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1900-1-1 00/00/00')"));
118 REQUIRE_FAIL(con.Query("INSERT INTO timestamp VALUES ('1900-1-1 00-00-00')"));
119}
120
121TEST_CASE("Test storage for timestamp type", "[timestamp]") {
122 unique_ptr<QueryResult> result;
123 auto storage_database = TestCreatePath("storage_timestamp_test");
124
125 // make sure the database does not exist
126 DeleteDatabase(storage_database);
127 {
128 // create a database and insert values
129 DuckDB db(storage_database);
130 Connection con(db);
131 REQUIRE_NO_FAIL(con.Query("CREATE TABLE timestamp (t TIMESTAMP);"));
132 REQUIRE_NO_FAIL(con.Query(
133 "INSERT INTO timestamp VALUES ('2008-01-01 00:00:01'), (NULL), ('2007-01-01 00:00:01'), ('2008-02-01 "
134 "00:00:01'), "
135 "('2008-01-02 00:00:01'), ('2008-01-01 10:00:00'), ('2008-01-01 00:10:00'), ('2008-01-01 00:00:10')"));
136 }
137 // reload the database from disk
138 for (idx_t i = 0; i < 2; i++) {
139 DuckDB db(storage_database);
140 Connection con(db);
141 result = con.Query("SELECT t FROM timestamp ORDER BY t;");
142 REQUIRE(CHECK_COLUMN(result, 0,
143 {Value(), Value::BIGINT(Timestamp::FromString("2007-01-01 00:00:01")),
144 Value::BIGINT(Timestamp::FromString("2008-01-01 00:00:01")),
145 Value::BIGINT(Timestamp::FromString("2008-01-01 00:00:10")),
146 Value::BIGINT(Timestamp::FromString("2008-01-01 00:10:00")),
147 Value::BIGINT(Timestamp::FromString("2008-01-01 10:00:00")),
148 Value::BIGINT(Timestamp::FromString("2008-01-02 00:00:01")),
149 Value::BIGINT(Timestamp::FromString("2008-02-01 00:00:01"))}));
150 }
151 DeleteDatabase(storage_database);
152}
153
154TEST_CASE("Test timestamp functions", "[timestamp]") {
155 unique_ptr<QueryResult> result;
156 DuckDB db(nullptr);
157 Connection con(db);
158
159 result = con.Query("SELECT AGE(TIMESTAMP '1957-06-13');");
160 auto current_timestamp = Timestamp::GetCurrentTimestamp();
161 auto interval = Timestamp::GetDifference(Timestamp::FromString("1957-06-13"), current_timestamp);
162 auto timestamp = Timestamp::IntervalToTimestamp(interval);
163 auto years = timestamp.year;
164 auto months = timestamp.month;
165 auto days = timestamp.day;
166
167 std::string output{""};
168 if (years == 0 && months == 0 && days == 0) {
169 output += "00:00:00";
170 } else {
171 if (years != 0) {
172 output = std::to_string(years);
173 output += " years ";
174 }
175 if (months != 0) {
176 output += std::to_string(months);
177 output += " mons ";
178 }
179 if (days != 0) {
180 output += std::to_string(days);
181 output += " days";
182 }
183 }
184 REQUIRE(CHECK_COLUMN(result, 0, {output.c_str()}));
185
186 result = con.Query("SELECT AGE(TIMESTAMP '2001-04-10', TIMESTAMP '1957-06-13');");
187 REQUIRE(CHECK_COLUMN(result, 0, {"43 years 9 mons 27 days"}));
188
189 result = con.Query("SELECT age(TIMESTAMP '2014-04-25', TIMESTAMP '2014-04-17');");
190 REQUIRE(CHECK_COLUMN(result, 0, {"8 days"}));
191
192 result = con.Query("SELECT age(TIMESTAMP '2014-04-25', TIMESTAMP '2014-01-01');");
193 REQUIRE(CHECK_COLUMN(result, 0, {"3 mons 24 days"}));
194
195 result = con.Query("SELECT age(TIMESTAMP '2019-06-11', TIMESTAMP '2019-06-11');");
196 REQUIRE(CHECK_COLUMN(result, 0, {"00:00:00"}));
197
198 result = con.Query(" SELECT age(timestamp '2019-06-11 12:00:00', timestamp '2019-07-11 11:00:00');");
199 REQUIRE(CHECK_COLUMN(result, 0, {"-29 days -23:00:00"}));
200
201 // create and insert into table
202 REQUIRE_NO_FAIL(con.Query("CREATE TABLE timestamp(t1 TIMESTAMP, t2 TIMESTAMP)"));
203 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES('2001-04-10', '1957-06-13')"));
204 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES('2014-04-25', '2014-04-17')"));
205 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES('2014-04-25','2014-01-01')"));
206 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES('2019-06-11', '2019-06-11')"));
207 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES(NULL, '2019-06-11')"));
208 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES('2019-06-11', NULL)"));
209 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamp VALUES(NULL, NULL)"));
210
211 result = con.Query("SELECT AGE(t1, TIMESTAMP '1957-06-13') FROM timestamp;");
212 REQUIRE(CHECK_COLUMN(result, 0,
213 {{"43 years 9 mons 27 days"},
214 {"56 years 10 mons 12 days"},
215 {"56 years 10 mons 12 days"},
216 {"61 years 11 mons 28 days"},
217 {Value()},
218 {"61 years 11 mons 28 days"},
219 {Value()}}));
220
221 result = con.Query("SELECT AGE(TIMESTAMP '2001-04-10', t2) FROM timestamp;");
222 REQUIRE(CHECK_COLUMN(result, 0,
223 {{"43 years 9 mons 27 days"},
224 {"-13 years -7 days"},
225 {"-12 years -8 mons -21 days"},
226 {"-18 years -2 mons -1 days"},
227 {"-18 years -2 mons -1 days"},
228 {Value()},
229 {Value()}}));
230
231 result = con.Query("SELECT AGE(t1, t2) FROM timestamp;");
232 REQUIRE(CHECK_COLUMN(
233 result, 0,
234 {{"43 years 9 mons 27 days"}, {"8 days"}, {"3 mons 24 days"}, {"00:00:00"}, {Value()}, {Value()}, {Value()}}));
235
236 result = con.Query("SELECT AGE(t1, t2) FROM timestamp WHERE t1 > '2001-12-12';");
237 REQUIRE(CHECK_COLUMN(result, 0, {{"8 days"}, {"3 mons 24 days"}, {"00:00:00"}, {Value()}}));
238
239 // Test NULLS
240 result = con.Query("SELECT AGE(NULL, NULL);");
241 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
242
243 result = con.Query("SELECT AGE(TIMESTAMP '1957-06-13', NULL);");
244 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
245
246 result = con.Query("SELECT AGE(NULL, TIMESTAMP '1957-06-13');");
247 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
248}
249
250TEST_CASE("Test milliseconds with timestamps", "[timestamp]") {
251 unique_ptr<QueryResult> result;
252 DuckDB db(nullptr);
253 Connection con(db);
254
255 result = con.Query(
256 "SELECT CAST('2001-04-20 14:42:11.123' AS TIMESTAMP) a, CAST('2001-04-20 14:42:11.0' AS TIMESTAMP) b;");
257 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2001-04-20 14:42:11.123"))}));
258 REQUIRE(CHECK_COLUMN(result, 1, {Value::BIGINT(Timestamp::FromString("2001-04-20 14:42:11"))}));
259}
260
261TEST_CASE("Test more timestamp functions", "[timestamp]") {
262 unique_ptr<QueryResult> result;
263 DuckDB db(nullptr);
264 Connection con(db);
265
266 result = con.Query("SELECT CAST(CURRENT_TIME AS STRING), CAST(CURRENT_DATE AS STRING), CAST(CURRENT_TIMESTAMP AS "
267 "STRING), CAST(NOW() AS STRING)");
268 REQUIRE(result->success);
269
270 auto ds = result->Fetch();
271 REQUIRE(ds->size() == 1);
272 REQUIRE(ds->column_count() == 4);
273
274 auto time = Time::FromString(ds->GetValue(0, 0).str_value);
275 REQUIRE(time > 0);
276
277 auto date = Date::FromString(ds->GetValue(1, 0).str_value);
278 REQUIRE(date > 0);
279
280 auto ts = Timestamp::FromString(ds->GetValue(2, 0).str_value);
281 REQUIRE(ts > 0);
282
283 auto ts2 = Timestamp::FromString(ds->GetValue(3, 0).str_value);
284 REQUIRE(ts2 > 0);
285}
286