1#include "catch.hpp"
2#include "duckdb/common/types/date.hpp"
3#include "duckdb/common/types/time.hpp"
4#include "duckdb/common/types/timestamp.hpp"
5#include "test_helpers.hpp"
6#include "iostream"
7
8using namespace duckdb;
9using namespace std;
10
11TEST_CASE("Test date truncate functionality", "[date]") {
12 unique_ptr<QueryResult> result;
13 DuckDB db(nullptr);
14 Connection con(db);
15 con.EnableQueryVerification();
16
17 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(d DATE, s VARCHAR);"));
18 REQUIRE_NO_FAIL(con.Query("CREATE TABLE timestamps(d TIMESTAMP, s VARCHAR);"));
19 REQUIRE_NO_FAIL(
20 con.Query("INSERT INTO dates VALUES ('1992-12-02', 'year'), ('1993-03-03', 'month'), ('1994-05-05', 'day');"));
21 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamps VALUES "
22 "('1992-02-02 02:02:03', 'millennium'), "
23 "('1992-02-02 02:02:03', 'century'), "
24 "('1992-02-02 02:02:03', 'decade'), "
25 "('1992-02-02 02:02:03', 'year'), "
26 "('1992-02-02 02:02:03', 'quarter'), "
27 "('1992-02-02 02:02:03', 'month'), "
28 "('1992-02-02 02:02:03', 'week'), "
29 "('1992-02-02 02:02:03', 'day'), "
30 "('1992-02-02 02:02:03', 'hour'), "
31 "('1992-02-02 02:02:03', 'minute'), "
32 "('1992-02-02 02:02:03', 'second'), "
33 "('1992-02-02 02:02:03', 'milliseconds'), "
34 "('1992-02-02 02:02:03', 'microseconds');"));
35
36 // test date_trunc with different combinations of constant/non-constant columns on both dates and timestamps
37 result = con.Query("SELECT date_trunc(NULL::VARCHAR, NULL::TIMESTAMP) FROM dates;");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
39 result = con.Query("SELECT date_trunc(s, NULL::TIMESTAMP) FROM dates;");
40 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
41 result = con.Query("SELECT date_trunc(NULL, d) FROM dates;");
42 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
43 result = con.Query("SELECT date_trunc(NULL::VARCHAR, NULL::TIMESTAMP) FROM timestamps LIMIT 3;");
44 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
45 result = con.Query("SELECT date_trunc(s, NULL::TIMESTAMP) FROM timestamps LIMIT 3;");
46 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
47 result = con.Query("SELECT date_trunc(NULL, d) FROM timestamps LIMIT 3;");
48 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
49
50 // dates should be cast to timestamp correctly
51 result = con.Query("SELECT date_trunc('month', DATE '1992-02-02') FROM dates LIMIT 1;");
52 REQUIRE(result->sql_types[0] == SQLType::TIMESTAMP);
53 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("1992-02-01 00:00:00"))}));
54 result = con.Query("SELECT date_trunc(s, d) FROM dates;");
55 REQUIRE(CHECK_COLUMN(result, 0,
56 {Value::BIGINT(Timestamp::FromString("1992-01-01 00:00:00")),
57 Value::BIGINT(Timestamp::FromString("1993-03-01 00:00:00")),
58 Value::BIGINT(Timestamp::FromString("1994-05-05 00:00:00"))}));
59
60 // Timestamps should return timestamp type
61 result = con.Query("SELECT date_trunc('minute', TIMESTAMP '1992-02-02 04:03:02') FROM timestamps LIMIT 1;");
62 REQUIRE(result->sql_types[0] == SQLType::TIMESTAMP);
63 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("1992-02-02 04:03:00"))}));
64
65 // Test all truncate operators on timestamps
66 result = con.Query("SELECT date_trunc(s, d) FROM timestamps;");
67 REQUIRE(CHECK_COLUMN(result, 0,
68 {
69 Value::BIGINT(Timestamp::FromString("1000-01-01 00:00:00")), // millennium
70 Value::BIGINT(Timestamp::FromString("1900-01-01 00:00:00")), // century
71 Value::BIGINT(Timestamp::FromString("1990-01-01 00:00:00")), // decade
72 Value::BIGINT(Timestamp::FromString("1992-01-01 00:00:00")), // year
73 Value::BIGINT(Timestamp::FromString("1992-01-01 00:00:00")), // quarter
74 Value::BIGINT(Timestamp::FromString("1992-02-01 00:00:00")), // month
75 Value::BIGINT(Timestamp::FromString("1992-01-27 00:00:00")), // week
76 Value::BIGINT(Timestamp::FromString("1992-02-02 00:00:00")), // day
77 Value::BIGINT(Timestamp::FromString("1992-02-02 02:00:00")), // hour
78 Value::BIGINT(Timestamp::FromString("1992-02-02 02:02:00")), // minute
79 Value::BIGINT(Timestamp::FromString("1992-02-02 02:02:03")), // second
80 Value::BIGINT(Timestamp::FromString("1992-02-02 02:02:03")), // millisecond
81 Value::BIGINT(Timestamp::FromString("1992-02-02 02:02:03")) // microsecond
82 }));
83
84 // Redo previous test but with casting to date first
85 result = con.Query("SELECT date_trunc(s, CAST(d as DATE)) FROM timestamps;");
86 REQUIRE(CHECK_COLUMN(result, 0,
87 {
88 Value::BIGINT(Timestamp::FromString("1000-01-01 00:00:00")), // millennium
89 Value::BIGINT(Timestamp::FromString("1900-01-01 00:00:00")), // century
90 Value::BIGINT(Timestamp::FromString("1990-01-01 00:00:00")), // decade
91 Value::BIGINT(Timestamp::FromString("1992-01-01 00:00:00")), // year
92 Value::BIGINT(Timestamp::FromString("1992-01-01 00:00:00")), // quarter
93 Value::BIGINT(Timestamp::FromString("1992-02-01 00:00:00")), // month
94 Value::BIGINT(Timestamp::FromString("1992-01-27 00:00:00")), // week
95 Value::BIGINT(Timestamp::FromString("1992-02-02 00:00:00")), // day
96 Value::BIGINT(Timestamp::FromString("1992-02-02 00:00:00")), // hour
97 Value::BIGINT(Timestamp::FromString("1992-02-02 00:00:00")), // minute
98 Value::BIGINT(Timestamp::FromString("1992-02-02 00:00:00")), // second
99 Value::BIGINT(Timestamp::FromString("1992-02-02 00:00:00")), // millisecond
100 Value::BIGINT(Timestamp::FromString("1992-02-02 00:00:00")) // microsecond
101 }));
102
103 // Test week operator special cases
104 result = con.Query("SELECT date_trunc('week', TIMESTAMP '2020-01-01 04:03:02') FROM timestamps LIMIT 1;");
105 REQUIRE(result->sql_types[0] == SQLType::TIMESTAMP);
106 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2019-12-30 00:00:00"))}));
107 result = con.Query("SELECT date_trunc('week', TIMESTAMP '2019-01-06 04:03:02') FROM timestamps LIMIT 1;");
108 REQUIRE(result->sql_types[0] == SQLType::TIMESTAMP);
109 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2018-12-31 00:00:00"))}));
110
111 // Test quarter operator more thoroughly
112 result = con.Query("SELECT date_trunc('quarter', TIMESTAMP '2020-12-02 04:03:02') FROM timestamps LIMIT 1;");
113 REQUIRE(result->sql_types[0] == SQLType::TIMESTAMP);
114 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2020-10-01 00:00:00"))}));
115 result = con.Query("SELECT date_trunc('quarter', TIMESTAMP '2019-01-06 04:03:02') FROM timestamps LIMIT 1;");
116 REQUIRE(result->sql_types[0] == SQLType::TIMESTAMP);
117 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(Timestamp::FromString("2019-01-01 00:00:00"))}));
118
119 // Unknown specifier should fail
120 REQUIRE_FAIL(con.Query("SELECT date_trunc('epoch', TIMESTAMP '2019-01-06 04:03:02') FROM timestamps LIMIT 1;"));
121}
122