1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Extract function", "[date]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // create and insert into table
14 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(i DATE)"));
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO dates VALUES ('1993-08-14'), (NULL)"));
16
17 // extract various parts of the date
18 // year
19 result = con.Query("SELECT EXTRACT(year FROM i) FROM dates");
20 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(1993), Value()}));
21 // month
22 result = con.Query("SELECT EXTRACT(month FROM i) FROM dates");
23 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(8), Value()}));
24 // day
25 result = con.Query("SELECT EXTRACT(day FROM i) FROM dates");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(14), Value()}));
27 // decade
28 result = con.Query("SELECT EXTRACT(decade FROM i) FROM dates");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(199), Value()}));
30 // century
31 result = con.Query("SELECT EXTRACT(century FROM i) FROM dates");
32 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(20), Value()}));
33 // day of the week (Sunday = 0, Saturday = 6)
34 result = con.Query("SELECT EXTRACT(DOW FROM i) FROM dates");
35 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(6), Value()}));
36 // day of the year (1 - 365/366)
37 result = con.Query("SELECT EXTRACT(DOY FROM i) FROM dates");
38 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(226), Value()}));
39 // epoch
40 result = con.Query("SELECT EXTRACT(epoch FROM i) FROM dates");
41 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(745286400), Value()}));
42 // isodow (Monday = 1, Sunday = 7)
43 result = con.Query("SELECT EXTRACT(ISODOW FROM i) FROM dates");
44 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(6), Value()}));
45 // millenium (change of millenium is January 1, X001)
46 result = con.Query("SELECT EXTRACT(millennium FROM i) FROM dates");
47 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(2), Value()}));
48 // timestamp variants all give 0 for date
49 result = con.Query("SELECT EXTRACT(second FROM i) FROM dates");
50 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(0), Value()}));
51 result = con.Query("SELECT EXTRACT(minute FROM i) FROM dates");
52 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(0), Value()}));
53 result = con.Query("SELECT EXTRACT(hour FROM i) FROM dates");
54 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(0), Value()}));
55 result = con.Query("SELECT EXTRACT(milliseconds FROM i) FROM dates");
56 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(0), Value()}));
57}
58
59TEST_CASE("Extract function edge cases", "[date]") {
60 unique_ptr<QueryResult> result;
61 DuckDB db(nullptr);
62 Connection con(db);
63
64 // century changes in the year 1
65 result = con.Query("SELECT EXTRACT(century FROM cast('2000-10-10' AS DATE));");
66 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(20)}));
67 result = con.Query("SELECT EXTRACT(century FROM cast('2001-10-10' AS DATE));");
68 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(21)}));
69 // millennium changes in the year 1
70 result = con.Query("SELECT EXTRACT(millennium FROM cast('2000-10-10' AS DATE));");
71 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(2)}));
72 result = con.Query("SELECT EXTRACT(millennium FROM cast('2001-10-10' AS DATE));");
73 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(3)}));
74 // check DOW
75 // start from the epoch and go up/down, every time the day should go up/down
76 // one as well
77 int epoch_day = 4;
78 int expected_day_up = epoch_day, expected_day_down = epoch_day;
79 for (size_t i = 0; i < 7; i++) {
80 result = con.Query("SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + " + to_string(i) + ");");
81 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(expected_day_up)}));
82 result = con.Query("SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - " + to_string(i) + ");");
83 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(expected_day_down)}));
84 expected_day_up = (expected_day_up + 1) % 7;
85 expected_day_down = expected_day_down == 0 ? 6 : expected_day_down - 1;
86 }
87
88 // week numbers are weird
89 result = con.Query("SELECT EXTRACT(week FROM cast('2005-01-01' AS DATE));");
90 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(53)}));
91 result = con.Query("SELECT EXTRACT(week FROM cast('2006-01-01' AS DATE));");
92 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(52)}));
93 result = con.Query("SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE));");
94 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(1)}));
95 result = con.Query("SELECT EXTRACT(week FROM cast('2008-01-01' AS DATE));");
96 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(1)}));
97 result = con.Query("SELECT EXTRACT(week FROM cast('2009-01-01' AS DATE));");
98 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(1)}));
99 result = con.Query("SELECT EXTRACT(week FROM cast('2010-01-01' AS DATE));");
100 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(53)}));
101
102 // every 7 days the week number should go up by 7
103 int expected_week = 1;
104 for (size_t i = 0; i < 40; i++) {
105 result = con.Query("SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + " + to_string(i * 7) + ");");
106 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(expected_week)}));
107 expected_week++;
108 }
109}
110
111TEST_CASE("Extract timestamp function", "[timestamp]") {
112 unique_ptr<QueryResult> result;
113 DuckDB db(nullptr);
114 Connection con(db);
115 con.EnableQueryVerification();
116
117 // create and insert into table
118 REQUIRE_NO_FAIL(con.Query("CREATE TABLE timestamps(i TIMESTAMP)"));
119 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamps VALUES ('1993-08-14 08:22:33'), (NULL)"));
120
121 // extract various parts of the date
122 // year
123 result = con.Query("SELECT EXTRACT(year FROM i) FROM timestamps");
124 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(1993), Value()}));
125 // month
126 result = con.Query("SELECT EXTRACT(month FROM i) FROM timestamps");
127 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(8), Value()}));
128 // day
129 result = con.Query("SELECT EXTRACT(day FROM i) FROM timestamps");
130 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(14), Value()}));
131 // decade
132 result = con.Query("SELECT EXTRACT(decade FROM i) FROM timestamps");
133 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(199), Value()}));
134 // century
135 result = con.Query("SELECT EXTRACT(century FROM i) FROM timestamps");
136 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(20), Value()}));
137 // day of the week (Sunday = 0, Saturday = 6)
138 result = con.Query("SELECT EXTRACT(DOW FROM i) FROM timestamps");
139 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(6), Value()}));
140 // day of the year (1 - 365/366)
141 result = con.Query("SELECT EXTRACT(DOY FROM i) FROM timestamps");
142 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(226), Value()}));
143 // epoch
144 result = con.Query("SELECT EXTRACT(epoch FROM i) FROM timestamps");
145 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(745316553), Value()}));
146 // isodow (Monday = 1, Sunday = 7)
147 result = con.Query("SELECT EXTRACT(ISODOW FROM i) FROM timestamps");
148 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(6), Value()}));
149 // millenium (change of millenium is January 1, X001)
150 result = con.Query("SELECT EXTRACT(millennium FROM i) FROM timestamps");
151 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(2), Value()}));
152 result = con.Query("SELECT EXTRACT(second FROM i) FROM timestamps");
153 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(33), Value()}));
154 result = con.Query("SELECT EXTRACT(minute FROM i) FROM timestamps");
155 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(22), Value()}));
156 result = con.Query("SELECT EXTRACT(hour FROM i) FROM timestamps");
157 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(8), Value()}));
158 result = con.Query("SELECT EXTRACT(milliseconds FROM i) FROM timestamps");
159 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(33000), Value()}));
160}
161
162TEST_CASE("Extract milliseconds from timestamp", "[timestamp]") {
163 unique_ptr<QueryResult> result;
164 DuckDB db(nullptr);
165 Connection con(db);
166 con.EnableQueryVerification();
167
168 // create and insert into table
169 REQUIRE_NO_FAIL(con.Query("CREATE TABLE timestamps(i TIMESTAMP)"));
170 REQUIRE_NO_FAIL(con.Query("INSERT INTO timestamps VALUES ('1993-08-14 08:22:33.42'), (NULL)"));
171
172 result = con.Query("SELECT EXTRACT(second FROM i) FROM timestamps");
173 REQUIRE(CHECK_COLUMN(result, 0, {Value::DOUBLE(33), Value()})); // postgres returns 33.42 here
174 result = con.Query("SELECT EXTRACT(minute FROM i) FROM timestamps");
175 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(22), Value()}));
176 result = con.Query("SELECT EXTRACT(milliseconds FROM i) FROM timestamps");
177 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(33420), Value()}));
178}
179