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/time.hpp"
6#include "duckdb/common/types/timestamp.hpp"
7#include "duckdb/common/string_util.hpp"
8
9// TODO date_trunc function should also handle interval data type when it is implemented. See
10// https://www.postgresql.org/docs/9.1/functions-datetime.html
11
12using namespace std;
13
14namespace duckdb {
15
16struct MillenniumTruncOperator {
17 template <class TA, class TR> static inline TR Operation(TA input) {
18 date_t date = Timestamp::GetDate(input);
19 return Timestamp::FromDatetime(Date::FromDate((Date::ExtractYear(date) / 1000) * 1000, 1, 1), 0);
20 }
21};
22template <> timestamp_t MillenniumTruncOperator::Operation(date_t input) {
23 return MillenniumTruncOperator::Operation<timestamp_t, timestamp_t>(Timestamp::FromDatetime(input, 0));
24}
25
26struct CenturyTruncOperator {
27 template <class TA, class TR> static inline TR Operation(TA input) {
28 date_t date = Timestamp::GetDate(input);
29 return Timestamp::FromDatetime(Date::FromDate((Date::ExtractYear(date) / 100) * 100, 1, 1), 0);
30 }
31};
32template <> timestamp_t CenturyTruncOperator::Operation(date_t input) {
33 return CenturyTruncOperator::Operation<timestamp_t, timestamp_t>(Timestamp::FromDatetime(input, 0));
34}
35
36struct DecadeTruncOperator {
37 template <class TA, class TR> static inline TR Operation(TA input) {
38 date_t date = Timestamp::GetDate(input);
39 return Timestamp::FromDatetime(Date::FromDate((Date::ExtractYear(date) / 10) * 10, 1, 1), 0);
40 }
41};
42template <> timestamp_t DecadeTruncOperator::Operation(date_t input) {
43 return DecadeTruncOperator::Operation<timestamp_t, timestamp_t>(Timestamp::FromDatetime(input, 0));
44}
45
46struct YearTruncOperator {
47 template <class TA, class TR> static inline TR Operation(TA input) {
48 date_t date = Timestamp::GetDate(input);
49 return Timestamp::FromDatetime(Date::FromDate(Date::ExtractYear(date), 1, 1), 0);
50 }
51};
52template <> timestamp_t YearTruncOperator::Operation(date_t input) {
53 return YearTruncOperator::Operation<timestamp_t, timestamp_t>(Timestamp::FromDatetime(input, 0));
54}
55
56struct QuarterTruncOperator {
57 template <class TA, class TR> static inline TR Operation(TA input) {
58 date_t date = Timestamp::GetDate(input);
59
60 int32_t month = Date::ExtractMonth(date);
61 month = 1 + (((month - 1) / 3) * 3);
62 return Timestamp::FromDatetime(Date::FromDate(Date::ExtractYear(date), month, 1), 0);
63 }
64};
65template <> timestamp_t QuarterTruncOperator::Operation(date_t input) {
66 return QuarterTruncOperator::Operation<timestamp_t, timestamp_t>(Timestamp::FromDatetime(input, 0));
67}
68
69struct MonthTruncOperator {
70 template <class TA, class TR> static inline TR Operation(TA input) {
71 date_t date = Timestamp::GetDate(input);
72 return Timestamp::FromDatetime(Date::FromDate(Date::ExtractYear(date), Date::ExtractMonth(date), 1), 0);
73 }
74};
75template <> timestamp_t MonthTruncOperator::Operation(date_t input) {
76 return MonthTruncOperator::Operation<timestamp_t, timestamp_t>(Timestamp::FromDatetime(input, 0));
77}
78
79struct WeekTruncOperator {
80 template <class TA, class TR> static inline TR Operation(TA input) {
81 date_t date = Timestamp::GetDate(input);
82
83 return Timestamp::FromDatetime(Date::GetMondayOfCurrentWeek(date), 0);
84 }
85};
86template <> timestamp_t WeekTruncOperator::Operation(date_t input) {
87 return WeekTruncOperator::Operation<timestamp_t, timestamp_t>(Timestamp::FromDatetime(input, 0));
88}
89
90struct DayTruncOperator {
91 template <class TA, class TR> static inline TR Operation(TA input) {
92 date_t date = Timestamp::GetDate(input);
93 return Timestamp::FromDatetime(date, 0);
94 }
95};
96template <> timestamp_t DayTruncOperator::Operation(date_t input) {
97 return Timestamp::FromDatetime(input, 0);
98}
99
100struct HourTruncOperator {
101 template <class TA, class TR> static inline TR Operation(TA input) {
102 date_t date = Timestamp::GetDate(input);
103 return Timestamp::FromDatetime(date, Time::FromTime(Timestamp::GetHours(input), 0, 0, 0));
104 }
105};
106template <> timestamp_t HourTruncOperator::Operation(date_t input) {
107 return Timestamp::FromDatetime(input, 0);
108}
109
110struct MinuteTruncOperator {
111 template <class TA, class TR> static inline TR Operation(TA input) {
112 date_t date = Timestamp::GetDate(input);
113 return Timestamp::FromDatetime(date,
114 Time::FromTime(Timestamp::GetHours(input), Timestamp::GetMinutes(input), 0, 0));
115 }
116};
117template <> timestamp_t MinuteTruncOperator::Operation(date_t input) {
118 return Timestamp::FromDatetime(input, 0);
119}
120
121struct SecondsTruncOperator {
122 template <class TA, class TR> static inline TR Operation(TA input) {
123 date_t date = Timestamp::GetDate(input);
124 return Timestamp::FromDatetime(date, Time::FromTime(Timestamp::GetHours(input), Timestamp::GetMinutes(input),
125 Timestamp::GetSeconds(input), 0));
126 }
127};
128template <> timestamp_t SecondsTruncOperator::Operation(date_t input) {
129 return Timestamp::FromDatetime(input, 0);
130}
131
132struct MilliSecondsTruncOperator {
133 template <class TA, class TR> static inline TR Operation(TA input) {
134 return input;
135 }
136};
137template <> timestamp_t MilliSecondsTruncOperator::Operation(date_t input) {
138 return Timestamp::FromDatetime(input, 0);
139}
140
141template <class TA, class TR> static TR truncate_element(DatePartSpecifier type, TA element) {
142 switch (type) {
143 case DatePartSpecifier::MILLENNIUM:
144 return MillenniumTruncOperator::Operation<TA, TR>(element);
145 case DatePartSpecifier::CENTURY:
146 return CenturyTruncOperator::Operation<TA, TR>(element);
147 case DatePartSpecifier::DECADE:
148 return DecadeTruncOperator::Operation<TA, TR>(element);
149 case DatePartSpecifier::YEAR:
150 return YearTruncOperator::Operation<TA, TR>(element);
151 case DatePartSpecifier::QUARTER:
152 return QuarterTruncOperator::Operation<TA, TR>(element);
153 case DatePartSpecifier::MONTH:
154 return MonthTruncOperator::Operation<TA, TR>(element);
155 case DatePartSpecifier::WEEK:
156 return WeekTruncOperator::Operation<TA, TR>(element);
157 case DatePartSpecifier::DAY:
158 return DayTruncOperator::Operation<TA, TR>(element);
159 case DatePartSpecifier::HOUR:
160 return HourTruncOperator::Operation<TA, TR>(element);
161 case DatePartSpecifier::MINUTE:
162 return MinuteTruncOperator::Operation<TA, TR>(element);
163 case DatePartSpecifier::SECOND:
164 return SecondsTruncOperator::Operation<TA, TR>(element);
165 case DatePartSpecifier::MILLISECONDS:
166 return MilliSecondsTruncOperator::Operation<TA, TR>(element);
167 case DatePartSpecifier::MICROSECONDS:
168 // Since microseconds are not stored truncating to microseconds does the same as to milliseconds.
169 return MilliSecondsTruncOperator::Operation<TA, TR>(element);
170 default:
171 throw NotImplementedException("Specifier type not implemented");
172 }
173}
174
175struct DateTruncOperator {
176 template <class TA, class TB, class TR> static inline TR Operation(TA specifier, TB date) {
177 return truncate_element<TB, TR>(GetDatePartSpecifier(specifier.GetString()), date);
178 }
179};
180
181void DateTruncFun::RegisterFunction(BuiltinFunctions &set) {
182 ScalarFunctionSet date_trunc("date_trunc");
183 date_trunc.AddFunction(
184 ScalarFunction({SQLType::VARCHAR, SQLType::TIMESTAMP}, SQLType::TIMESTAMP,
185 ScalarFunction::BinaryFunction<string_t, timestamp_t, timestamp_t, DateTruncOperator>));
186 date_trunc.AddFunction(
187 ScalarFunction({SQLType::VARCHAR, SQLType::DATE}, SQLType::TIMESTAMP,
188 ScalarFunction::BinaryFunction<string_t, date_t, timestamp_t, DateTruncOperator>));
189 set.AddFunction(date_trunc);
190 date_trunc.name = "datetrunc";
191 set.AddFunction(date_trunc);
192}
193
194} // namespace duckdb
195