1#include "duckdb/function/scalar/date_functions.hpp"
2#include "duckdb/common/enums/date_part_specifier.hpp"
3#include "duckdb/common/exception.hpp"
4#include "duckdb/common/types/date.hpp"
5#include "duckdb/common/types/timestamp.hpp"
6#include "duckdb/common/vector_operations/vector_operations.hpp"
7#include "duckdb/common/string_util.hpp"
8using namespace std;
9
10namespace duckdb {
11
12DatePartSpecifier GetDatePartSpecifier(string specifier) {
13 specifier = StringUtil::Lower(specifier);
14 if (specifier == "year") {
15 return DatePartSpecifier::YEAR;
16 } else if (specifier == "month") {
17 return DatePartSpecifier::MONTH;
18 } else if (specifier == "day") {
19 return DatePartSpecifier::DAY;
20 } else if (specifier == "decade") {
21 return DatePartSpecifier::DECADE;
22 } else if (specifier == "century") {
23 return DatePartSpecifier::CENTURY;
24 } else if (specifier == "millennium") {
25 return DatePartSpecifier::MILLENNIUM;
26 } else if (specifier == "microseconds") {
27 return DatePartSpecifier::MICROSECONDS;
28 } else if (specifier == "milliseconds") {
29 return DatePartSpecifier::MILLISECONDS;
30 } else if (specifier == "second") {
31 return DatePartSpecifier::SECOND;
32 } else if (specifier == "minute") {
33 return DatePartSpecifier::MINUTE;
34 } else if (specifier == "hour") {
35 return DatePartSpecifier::HOUR;
36 } else if (specifier == "epoch") {
37 // seconds since 1970-01-01
38 return DatePartSpecifier::EPOCH;
39 } else if (specifier == "dow") {
40 // day of the week (Sunday = 0, Saturday = 6)
41 return DatePartSpecifier::DOW;
42 } else if (specifier == "isodow") {
43 // isodow (Monday = 1, Sunday = 7)
44 return DatePartSpecifier::ISODOW;
45 } else if (specifier == "week") {
46 // week number
47 return DatePartSpecifier::WEEK;
48 } else if (specifier == "doy") {
49 // day of the year (1-365/366)
50 return DatePartSpecifier::DOY;
51 } else if (specifier == "quarter") {
52 // quarter of the year (1-4)
53 return DatePartSpecifier::QUARTER;
54 } else {
55 throw ConversionException("extract specifier \"%s\" not recognized", specifier.c_str());
56 }
57}
58
59struct YearOperator {
60 template <class TA, class TR> static inline TR Operation(TA input) {
61 return Date::ExtractYear(input);
62 }
63};
64
65template <> int64_t YearOperator::Operation(timestamp_t input) {
66 return YearOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
67}
68
69struct MonthOperator {
70 template <class TA, class TR> static inline TR Operation(TA input) {
71 return Date::ExtractMonth(input);
72 }
73};
74
75template <> int64_t MonthOperator::Operation(timestamp_t input) {
76 return MonthOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
77}
78
79struct DayOperator {
80 template <class TA, class TR> static inline TR Operation(TA input) {
81 return Date::ExtractDay(input);
82 }
83};
84
85template <> int64_t DayOperator::Operation(timestamp_t input) {
86 return DayOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
87}
88
89struct DecadeOperator {
90 template <class TA, class TR> static inline TR Operation(TA input) {
91 return Date::ExtractYear(input) / 10;
92 }
93};
94
95template <> int64_t DecadeOperator::Operation(timestamp_t input) {
96 return DecadeOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
97}
98
99struct CenturyOperator {
100 template <class TA, class TR> static inline TR Operation(TA input) {
101 return ((Date::ExtractYear(input) - 1) / 100) + 1;
102 }
103};
104
105template <> int64_t CenturyOperator::Operation(timestamp_t input) {
106 return CenturyOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
107}
108
109struct MilleniumOperator {
110 template <class TA, class TR> static inline TR Operation(TA input) {
111 return ((Date::ExtractYear(input) - 1) / 1000) + 1;
112 }
113};
114
115template <> int64_t MilleniumOperator::Operation(timestamp_t input) {
116 return MilleniumOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
117}
118
119struct QuarterOperator {
120 template <class TA, class TR> static inline TR Operation(TA input) {
121 return Date::ExtractMonth(input) / 4;
122 }
123};
124
125template <> int64_t QuarterOperator::Operation(timestamp_t input) {
126 return QuarterOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
127}
128
129struct DayOfWeekOperator {
130 template <class TA, class TR> static inline TR Operation(TA input) {
131 // day of the week (Sunday = 0, Saturday = 6)
132 // turn sunday into 0 by doing mod 7
133 return Date::ExtractISODayOfTheWeek(input) % 7;
134 }
135};
136
137template <> int64_t DayOfWeekOperator::Operation(timestamp_t input) {
138 return DayOfWeekOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
139}
140
141struct ISODayOfWeekOperator {
142 template <class TA, class TR> static inline TR Operation(TA input) {
143 // isodow (Monday = 1, Sunday = 7)
144 return Date::ExtractISODayOfTheWeek(input);
145 }
146};
147
148template <> int64_t ISODayOfWeekOperator::Operation(timestamp_t input) {
149 return ISODayOfWeekOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
150}
151
152struct DayOfYearOperator {
153 template <class TA, class TR> static inline TR Operation(TA input) {
154 return Date::ExtractDayOfTheYear(input);
155 }
156};
157
158template <> int64_t DayOfYearOperator::Operation(timestamp_t input) {
159 return DayOfYearOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
160}
161
162struct WeekOperator {
163 template <class TA, class TR> static inline TR Operation(TA input) {
164 return Date::ExtractWeekNumber(input);
165 }
166};
167
168template <> int64_t WeekOperator::Operation(timestamp_t input) {
169 return WeekOperator::Operation<date_t, int64_t>(Timestamp::GetDate(input));
170}
171
172struct YearWeekOperator {
173 template <class TA, class TR> static inline TR Operation(TA input) {
174 return YearOperator::Operation<TA, TR>(input) * 100 + WeekOperator::Operation<TA, TR>(input);
175 }
176};
177
178struct EpochOperator {
179 template <class TA, class TR> static inline TR Operation(TA input) {
180 return Date::Epoch(input);
181 }
182};
183
184template <> int64_t EpochOperator::Operation(timestamp_t input) {
185 return Timestamp::GetEpoch(input);
186}
187
188struct MicrosecondsOperator {
189 template <class TA, class TR> static inline TR Operation(TA input) {
190 return 0;
191 }
192};
193
194template <> int64_t MicrosecondsOperator::Operation(timestamp_t input) {
195 return Timestamp::GetMilliseconds(input) * 1000;
196}
197
198struct MillisecondsOperator {
199 template <class TA, class TR> static inline TR Operation(TA input) {
200 return 0;
201 }
202};
203
204template <> int64_t MillisecondsOperator::Operation(timestamp_t input) {
205 return Timestamp::GetMilliseconds(input);
206}
207
208struct SecondsOperator {
209 template <class TA, class TR> static inline TR Operation(TA input) {
210 return 0;
211 }
212};
213
214template <> int64_t SecondsOperator::Operation(timestamp_t input) {
215 return Timestamp::GetSeconds(input);
216}
217
218struct MinutesOperator {
219 template <class TA, class TR> static inline TR Operation(TA input) {
220 return 0;
221 }
222};
223
224template <> int64_t MinutesOperator::Operation(timestamp_t input) {
225 return Timestamp::GetMinutes(input);
226}
227
228struct HoursOperator {
229 template <class TA, class TR> static inline TR Operation(TA input) {
230 return 0;
231 }
232};
233
234template <> int64_t HoursOperator::Operation(timestamp_t input) {
235 return Timestamp::GetHours(input);
236}
237
238template <class T> static int64_t extract_element(DatePartSpecifier type, T element) {
239 switch (type) {
240 case DatePartSpecifier::YEAR:
241 return YearOperator::Operation<T, int64_t>(element);
242 case DatePartSpecifier::MONTH:
243 return MonthOperator::Operation<T, int64_t>(element);
244 case DatePartSpecifier::DAY:
245 return DayOperator::Operation<T, int64_t>(element);
246 case DatePartSpecifier::DECADE:
247 return DecadeOperator::Operation<T, int64_t>(element);
248 case DatePartSpecifier::CENTURY:
249 return CenturyOperator::Operation<T, int64_t>(element);
250 case DatePartSpecifier::MILLENNIUM:
251 return MilleniumOperator::Operation<T, int64_t>(element);
252 case DatePartSpecifier::QUARTER:
253 return QuarterOperator::Operation<T, int64_t>(element);
254 case DatePartSpecifier::DOW:
255 return DayOfWeekOperator::Operation<T, int64_t>(element);
256 case DatePartSpecifier::ISODOW:
257 return ISODayOfWeekOperator::Operation<T, int64_t>(element);
258 case DatePartSpecifier::DOY:
259 return DayOfYearOperator::Operation<T, int64_t>(element);
260 case DatePartSpecifier::WEEK:
261 return WeekOperator::Operation<T, int64_t>(element);
262 case DatePartSpecifier::EPOCH:
263 return EpochOperator::Operation<T, int64_t>(element);
264 case DatePartSpecifier::MICROSECONDS:
265 return MicrosecondsOperator::Operation<T, int64_t>(element);
266 case DatePartSpecifier::MILLISECONDS:
267 return MillisecondsOperator::Operation<T, int64_t>(element);
268 case DatePartSpecifier::SECOND:
269 return SecondsOperator::Operation<T, int64_t>(element);
270 case DatePartSpecifier::MINUTE:
271 return MinutesOperator::Operation<T, int64_t>(element);
272 case DatePartSpecifier::HOUR:
273 return HoursOperator::Operation<T, int64_t>(element);
274 default:
275 throw NotImplementedException("Specifier type not implemented");
276 }
277}
278
279struct DatePartOperator {
280 template <class TA, class TB, class TR> static inline TR Operation(TA specifier, TB date) {
281 return extract_element<TB>(GetDatePartSpecifier(specifier.GetString()), date);
282 }
283};
284
285template <class OP> static void AddDatePartOperator(BuiltinFunctions &set, string name) {
286 ScalarFunctionSet operator_set(name);
287 operator_set.AddFunction(
288 ScalarFunction({SQLType::DATE}, SQLType::BIGINT, ScalarFunction::UnaryFunction<date_t, int64_t, OP>));
289 operator_set.AddFunction(
290 ScalarFunction({SQLType::TIMESTAMP}, SQLType::BIGINT, ScalarFunction::UnaryFunction<timestamp_t, int64_t, OP>));
291 set.AddFunction(operator_set);
292}
293
294struct LastDayOperator {
295 template <class TA, class TR> static inline TR Operation(TA input) {
296 int32_t yyyy, mm, dd;
297 Date::Convert(input, yyyy, mm, dd);
298 yyyy += (mm / 12);
299 mm %= 12;
300 ++mm;
301 return Date::FromDate(yyyy, mm, 1) - 1;
302 }
303};
304
305template <> date_t LastDayOperator::Operation(timestamp_t input) {
306 return LastDayOperator::Operation<date_t, date_t>(Timestamp::GetDate(input));
307}
308
309static string_t s_monthNames[] = {"January", "February", "March", "April", "May", "June",
310 "July", "August", "September", "October", "November", "December"};
311
312struct MonthNameOperator {
313 template <class TA, class TR> static inline TR Operation(TA input) {
314 return s_monthNames[MonthOperator::Operation<TA, int64_t>(input) - 1];
315 }
316};
317
318static string_t s_dayNames[] = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"};
319
320struct DayNameOperator {
321 template <class TA, class TR> static inline TR Operation(TA input) {
322 return s_dayNames[DayOfWeekOperator::Operation<TA, int64_t>(input)];
323 }
324};
325
326void DatePartFun::RegisterFunction(BuiltinFunctions &set) {
327 // register the individual operators
328 AddDatePartOperator<YearOperator>(set, "year");
329 AddDatePartOperator<MonthOperator>(set, "month");
330 AddDatePartOperator<DayOperator>(set, "day");
331 AddDatePartOperator<DecadeOperator>(set, "decade");
332 AddDatePartOperator<CenturyOperator>(set, "century");
333 AddDatePartOperator<MilleniumOperator>(set, "millenium");
334 AddDatePartOperator<QuarterOperator>(set, "quarter");
335 AddDatePartOperator<DayOfWeekOperator>(set, "dayofweek");
336 AddDatePartOperator<ISODayOfWeekOperator>(set, "isodow");
337 AddDatePartOperator<DayOfYearOperator>(set, "dayofyear");
338 AddDatePartOperator<WeekOperator>(set, "week");
339 AddDatePartOperator<EpochOperator>(set, "epoch");
340 AddDatePartOperator<MicrosecondsOperator>(set, "microsecond");
341 AddDatePartOperator<MillisecondsOperator>(set, "millisecond");
342 AddDatePartOperator<SecondsOperator>(set, "second");
343 AddDatePartOperator<MinutesOperator>(set, "minute");
344 AddDatePartOperator<HoursOperator>(set, "hour");
345
346 // register combinations
347 AddDatePartOperator<YearWeekOperator>(set, "yearweek");
348
349 // register various aliases
350 AddDatePartOperator<DayOperator>(set, "dayofmonth");
351 AddDatePartOperator<DayOfWeekOperator>(set, "weekday");
352 AddDatePartOperator<WeekOperator>(set, "weekofyear"); // Note that WeekOperator is ISO-8601, not US
353
354 // register the last_day function
355 ScalarFunctionSet last_day("last_day");
356 last_day.AddFunction(ScalarFunction({SQLType::DATE}, SQLType::DATE,
357 ScalarFunction::UnaryFunction<date_t, date_t, LastDayOperator, true>));
358 last_day.AddFunction(ScalarFunction({SQLType::TIMESTAMP}, SQLType::DATE,
359 ScalarFunction::UnaryFunction<timestamp_t, date_t, LastDayOperator, true>));
360 set.AddFunction(last_day);
361
362 // register the monthname function
363 ScalarFunctionSet monthname("monthname");
364 monthname.AddFunction(ScalarFunction({SQLType::DATE}, SQLType::VARCHAR,
365 ScalarFunction::UnaryFunction<date_t, string_t, MonthNameOperator, true>));
366 monthname.AddFunction(
367 ScalarFunction({SQLType::TIMESTAMP}, SQLType::VARCHAR,
368 ScalarFunction::UnaryFunction<timestamp_t, string_t, MonthNameOperator, true>));
369 set.AddFunction(monthname);
370
371 // register the dayname function
372 ScalarFunctionSet dayname("dayname");
373 dayname.AddFunction(ScalarFunction({SQLType::DATE}, SQLType::VARCHAR,
374 ScalarFunction::UnaryFunction<date_t, string_t, DayNameOperator, true>));
375 dayname.AddFunction(ScalarFunction({SQLType::TIMESTAMP}, SQLType::VARCHAR,
376 ScalarFunction::UnaryFunction<timestamp_t, string_t, DayNameOperator, true>));
377 set.AddFunction(dayname);
378
379 // finally the actual date_part function
380 ScalarFunctionSet date_part("date_part");
381 date_part.AddFunction(
382 ScalarFunction({SQLType::VARCHAR, SQLType::DATE}, SQLType::BIGINT,
383 ScalarFunction::BinaryFunction<string_t, date_t, int64_t, DatePartOperator, true>));
384 date_part.AddFunction(
385 ScalarFunction({SQLType::VARCHAR, SQLType::TIMESTAMP}, SQLType::BIGINT,
386 ScalarFunction::BinaryFunction<string_t, timestamp_t, int64_t, DatePartOperator, true>));
387 set.AddFunction(date_part);
388 date_part.name = "datepart";
389 set.AddFunction(date_part);
390}
391
392} // namespace duckdb
393