ClickHouse

1
#include <DataTypes/DataTypeDateTime.h>
2
#include <DataTypes/DataTypeDateTime64.h>
3
#include <DataTypes/DataTypesNumber.h>
4
#include <Common/IntervalKind.h>
5
#include <Columns/ColumnString.h>
6
#include <Columns/ColumnsDateTime.h>
7
#include <Columns/ColumnsNumber.h>
8
#include <Columns/ColumnDecimal.h>
9
#include <Formats/FormatSettings.h>
10
#include <Functions/IFunction.h>
11
#include <Functions/FunctionHelpers.h>
12
#include <Functions/FunctionFactory.h>
13
#include <Functions/extractTimeZoneFromFunctionArguments.h>
14
#include <Functions/DateTimeTransforms.h>
15
#include <Functions/TransformDateTime64.h>
16

17
#include <IO/WriteHelpers.h>
18

19
#include <base/find_symbols.h>
20

21
#include <type_traits>
22

23

24
namespace DB
25
{
26

27
namespace ErrorCodes
28
{
29
    extern const int NUMBER_OF_ARGUMENTS_DOESNT_MATCH;
30
    extern const int ILLEGAL_TYPE_OF_ARGUMENT;
31
    extern const int ILLEGAL_COLUMN;
32
    extern const int BAD_ARGUMENTS;
33
}
34

35
namespace
36
{
37

38
template <bool is_diff>
39
class DateDiffImpl
40
{
41
public:
42
    using ColumnDateTime64 = ColumnDecimal<DateTime64>;
43

44
    explicit DateDiffImpl(const String & name_) : name(name_) {}
45

46
    template <typename Transform>
47
    void dispatchForColumns(
48
        const IColumn & x, const IColumn & y,
49
        const DateLUTImpl & timezone_x, const DateLUTImpl & timezone_y,
50
        ColumnInt64::Container & result) const
51
    {
52
        if (const auto * x_vec_16 = checkAndGetColumn<ColumnDate>(&x))
53
            dispatchForSecondColumn<Transform>(*x_vec_16, y, timezone_x, timezone_y, result);
54
        else if (const auto * x_vec_32 = checkAndGetColumn<ColumnDateTime>(&x))
55
            dispatchForSecondColumn<Transform>(*x_vec_32, y, timezone_x, timezone_y, result);
56
        else if (const auto * x_vec_32_s = checkAndGetColumn<ColumnDate32>(&x))
57
            dispatchForSecondColumn<Transform>(*x_vec_32_s, y, timezone_x, timezone_y, result);
58
        else if (const auto * x_vec_64 = checkAndGetColumn<ColumnDateTime64>(&x))
59
            dispatchForSecondColumn<Transform>(*x_vec_64, y, timezone_x, timezone_y, result);
60
        else if (const auto * x_const_16 = checkAndGetColumnConst<ColumnDate>(&x))
61
            dispatchConstForSecondColumn<Transform>(x_const_16->getValue<UInt16>(), y, timezone_x, timezone_y, result);
62
        else if (const auto * x_const_32 = checkAndGetColumnConst<ColumnDateTime>(&x))
63
            dispatchConstForSecondColumn<Transform>(x_const_32->getValue<UInt32>(), y, timezone_x, timezone_y, result);
64
        else if (const auto * x_const_32_s = checkAndGetColumnConst<ColumnDate32>(&x))
65
            dispatchConstForSecondColumn<Transform>(x_const_32_s->getValue<Int32>(), y, timezone_x, timezone_y, result);
66
        else if (const auto * x_const_64 = checkAndGetColumnConst<ColumnDateTime64>(&x))
67
            dispatchConstForSecondColumn<Transform>(x_const_64->getValue<DecimalField<DateTime64>>(), y, timezone_x, timezone_y, result);
68
        else
69
            throw Exception(ErrorCodes::ILLEGAL_COLUMN,
70
                "Illegal column for first argument of function {}, must be Date, Date32, DateTime or DateTime64",
71
                name);
72
    }
73

74
    template <typename Transform, typename LeftColumnType>
75
    void dispatchForSecondColumn(
76
        const LeftColumnType & x, const IColumn & y,
77
        const DateLUTImpl & timezone_x, const DateLUTImpl & timezone_y,
78
        ColumnInt64::Container & result) const
79
    {
80
        if (const auto * y_vec_16 = checkAndGetColumn<ColumnDate>(&y))
81
            vectorVector<Transform>(x, *y_vec_16, timezone_x, timezone_y, result);
82
        else if (const auto * y_vec_32 = checkAndGetColumn<ColumnDateTime>(&y))
83
            vectorVector<Transform>(x, *y_vec_32, timezone_x, timezone_y, result);
84
        else if (const auto * y_vec_32_s = checkAndGetColumn<ColumnDate32>(&y))
85
            vectorVector<Transform>(x, *y_vec_32_s, timezone_x, timezone_y, result);
86
        else if (const auto * y_vec_64 = checkAndGetColumn<ColumnDateTime64>(&y))
87
            vectorVector<Transform>(x, *y_vec_64, timezone_x, timezone_y, result);
88
        else if (const auto * y_const_16 = checkAndGetColumnConst<ColumnDate>(&y))
89
            vectorConstant<Transform>(x, y_const_16->getValue<UInt16>(), timezone_x, timezone_y, result);
90
        else if (const auto * y_const_32 = checkAndGetColumnConst<ColumnDateTime>(&y))
91
            vectorConstant<Transform>(x, y_const_32->getValue<UInt32>(), timezone_x, timezone_y, result);
92
        else if (const auto * y_const_32_s = checkAndGetColumnConst<ColumnDate32>(&y))
93
            vectorConstant<Transform>(x, y_const_32_s->getValue<Int32>(), timezone_x, timezone_y, result);
94
        else if (const auto * y_const_64 = checkAndGetColumnConst<ColumnDateTime64>(&y))
95
            vectorConstant<Transform>(x, y_const_64->getValue<DecimalField<DateTime64>>(), timezone_x, timezone_y, result);
96
        else
97
            throw Exception(ErrorCodes::ILLEGAL_COLUMN,
98
                "Illegal column for second argument of function {}, must be Date, Date32, DateTime or DateTime64",
99
                name);
100
    }
101

102
    template <typename Transform, typename T1>
103
    void dispatchConstForSecondColumn(
104
        T1 x, const IColumn & y,
105
        const DateLUTImpl & timezone_x, const DateLUTImpl & timezone_y,
106
        ColumnInt64::Container & result) const
107
    {
108
        if (const auto * y_vec_16 = checkAndGetColumn<ColumnDate>(&y))
109
            constantVector<Transform>(x, *y_vec_16, timezone_x, timezone_y, result);
110
        else if (const auto * y_vec_32 = checkAndGetColumn<ColumnDateTime>(&y))
111
            constantVector<Transform>(x, *y_vec_32, timezone_x, timezone_y, result);
112
        else if (const auto * y_vec_32_s = checkAndGetColumn<ColumnDate32>(&y))
113
            constantVector<Transform>(x, *y_vec_32_s, timezone_x, timezone_y, result);
114
        else if (const auto * y_vec_64 = checkAndGetColumn<ColumnDateTime64>(&y))
115
            constantVector<Transform>(x, *y_vec_64, timezone_x, timezone_y, result);
116
        else
117
            throw Exception(ErrorCodes::ILLEGAL_COLUMN,
118
                "Illegal column for second argument of function {}, must be Date, Date32, DateTime or DateTime64",
119
                name);
120
    }
121

122
    template <typename Transform, typename LeftColumnType, typename RightColumnType>
123
    void vectorVector(
124
        const LeftColumnType & x, const RightColumnType & y,
125
        const DateLUTImpl & timezone_x, const DateLUTImpl & timezone_y,
126
        ColumnInt64::Container & result) const
127
    {
128
        const auto & x_data = x.getData();
129
        const auto & y_data = y.getData();
130

131
        const auto transform_x = TransformDateTime64<Transform>(getScale(x));
132
        const auto transform_y = TransformDateTime64<Transform>(getScale(y));
133
        for (size_t i = 0, size = x.size(); i < size; ++i)
134
                result[i] = calculate(transform_x, transform_y, x_data[i], y_data[i], timezone_x, timezone_y);
135
    }
136

137
    template <typename Transform, typename LeftColumnType, typename T2>
138
    void vectorConstant(
139
        const LeftColumnType & x, T2 y,
140
        const DateLUTImpl & timezone_x, const DateLUTImpl & timezone_y,
141
        ColumnInt64::Container & result) const
142
    {
143
        const auto & x_data = x.getData();
144
        const auto transform_x = TransformDateTime64<Transform>(getScale(x));
145
        const auto transform_y = TransformDateTime64<Transform>(getScale(y));
146
        const auto y_value = stripDecimalFieldValue(y);
147

148
        for (size_t i = 0, size = x.size(); i < size; ++i)
149
            result[i] = calculate(transform_x, transform_y, x_data[i], y_value, timezone_x, timezone_y);
150
    }
151

152
    template <typename Transform, typename T1, typename RightColumnType>
153
    void constantVector(
154
        T1 x, const RightColumnType & y,
155
        const DateLUTImpl & timezone_x, const DateLUTImpl & timezone_y,
156
        ColumnInt64::Container & result) const
157
    {
158
        const auto & y_data = y.getData();
159
        const auto transform_x = TransformDateTime64<Transform>(getScale(x));
160
        const auto transform_y = TransformDateTime64<Transform>(getScale(y));
161
        const auto x_value = stripDecimalFieldValue(x);
162

163
        for (size_t i = 0, size = y.size(); i < size; ++i)
164
            result[i] = calculate(transform_x, transform_y, x_value, y_data[i], timezone_x, timezone_y);
165
    }
166

167
    template <typename TransformX, typename TransformY, typename T1, typename T2>
168
    Int64 calculate(const TransformX & transform_x, const TransformY & transform_y, T1 x, T2 y, const DateLUTImpl & timezone_x, const DateLUTImpl & timezone_y) const
169
    {
170
        if constexpr (is_diff)
171
            return static_cast<Int64>(transform_y.execute(y, timezone_y))
172
                - static_cast<Int64>(transform_x.execute(x, timezone_x));
173
        else
174
        {
175
            auto res = static_cast<Int64>(transform_y.execute(y, timezone_y))
176
                - static_cast<Int64>(transform_x.execute(x, timezone_x));
177
            DateTimeComponentsWithFractionalPart a_comp;
178
            DateTimeComponentsWithFractionalPart b_comp;
179
            Int64 adjust_value;
180
            auto x_nanoseconds = TransformDateTime64<ToRelativeSubsecondNumImpl<nanosecond_multiplier>>(transform_x.getScaleMultiplier()).execute(x, timezone_x);
181
            auto y_nanoseconds = TransformDateTime64<ToRelativeSubsecondNumImpl<nanosecond_multiplier>>(transform_y.getScaleMultiplier()).execute(y, timezone_y);
182

183
            if (x_nanoseconds <= y_nanoseconds)
184
            {
185
                a_comp = TransformDateTime64<ToDateTimeComponentsImpl>(transform_x.getScaleMultiplier()).execute(x, timezone_x);
186
                b_comp = TransformDateTime64<ToDateTimeComponentsImpl>(transform_y.getScaleMultiplier()).execute(y, timezone_y);
187
                adjust_value = -1;
188
            }
189
            else
190
            {
191
                a_comp = TransformDateTime64<ToDateTimeComponentsImpl>(transform_y.getScaleMultiplier()).execute(y, timezone_y);
192
                b_comp = TransformDateTime64<ToDateTimeComponentsImpl>(transform_x.getScaleMultiplier()).execute(x, timezone_x);
193
                adjust_value = 1;
194
            }
195

196
            if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeYearNumImpl<ResultPrecision::Extended>>>)
197
            {
198
                if ((a_comp.date.month > b_comp.date.month)
199
                    || ((a_comp.date.month == b_comp.date.month) && ((a_comp.date.day > b_comp.date.day)
200
                    || ((a_comp.date.day == b_comp.date.day) && ((a_comp.time.hour > b_comp.time.hour)
201
                    || ((a_comp.time.hour == b_comp.time.hour) && ((a_comp.time.minute > b_comp.time.minute)
202
                    || ((a_comp.time.minute == b_comp.time.minute) && ((a_comp.time.second > b_comp.time.second)
203
                    || ((a_comp.time.second == b_comp.time.second) && ((a_comp.millisecond > b_comp.millisecond)
204
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
205
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))))))))))))
206
                    res += adjust_value;
207
            }
208
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeQuarterNumImpl<ResultPrecision::Extended>>>)
209
            {
210
                auto x_month_in_quarter = (a_comp.date.month - 1) % 3;
211
                auto y_month_in_quarter = (b_comp.date.month - 1) % 3;
212
                if ((x_month_in_quarter > y_month_in_quarter)
213
                    || ((x_month_in_quarter == y_month_in_quarter) && ((a_comp.date.day > b_comp.date.day)
214
                    || ((a_comp.date.day == b_comp.date.day) && ((a_comp.time.hour > b_comp.time.hour)
215
                    || ((a_comp.time.hour == b_comp.time.hour) && ((a_comp.time.minute > b_comp.time.minute)
216
                    || ((a_comp.time.minute == b_comp.time.minute) && ((a_comp.time.second > b_comp.time.second)
217
                    || ((a_comp.time.second == b_comp.time.second) && ((a_comp.millisecond > b_comp.millisecond)
218
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
219
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))))))))))))
220
                    res += adjust_value;
221
            }
222
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeMonthNumImpl<ResultPrecision::Extended>>>)
223
            {
224
                if ((a_comp.date.day > b_comp.date.day)
225
                    || ((a_comp.date.day == b_comp.date.day) && ((a_comp.time.hour > b_comp.time.hour)
226
                    || ((a_comp.time.hour == b_comp.time.hour) && ((a_comp.time.minute > b_comp.time.minute)
227
                    || ((a_comp.time.minute == b_comp.time.minute) && ((a_comp.time.second > b_comp.time.second)
228
                    || ((a_comp.time.second == b_comp.time.second) && ((a_comp.millisecond > b_comp.millisecond)
229
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
230
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))))))))))
231
                    res += adjust_value;
232
            }
233
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeWeekNumImpl<ResultPrecision::Extended>>>)
234
            {
235
                auto x_day_of_week = TransformDateTime64<ToDayOfWeekImpl>(transform_x.getScaleMultiplier()).execute(x, 0, timezone_x);
236
                auto y_day_of_week = TransformDateTime64<ToDayOfWeekImpl>(transform_y.getScaleMultiplier()).execute(y, 0, timezone_y);
237
                if ((x_day_of_week > y_day_of_week)
238
                    || ((x_day_of_week == y_day_of_week) && (a_comp.time.hour > b_comp.time.hour))
239
                    || ((a_comp.time.hour == b_comp.time.hour) && ((a_comp.time.minute > b_comp.time.minute)
240
                    || ((a_comp.time.minute == b_comp.time.minute) && ((a_comp.time.second > b_comp.time.second)
241
                    || ((a_comp.time.second == b_comp.time.second) && ((a_comp.millisecond > b_comp.millisecond)
242
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
243
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))))))))
244
                    res += adjust_value;
245
            }
246
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeDayNumImpl<ResultPrecision::Extended>>>)
247
            {
248
                if ((a_comp.time.hour > b_comp.time.hour)
249
                    || ((a_comp.time.hour == b_comp.time.hour) && ((a_comp.time.minute > b_comp.time.minute)
250
                    || ((a_comp.time.minute == b_comp.time.minute) && ((a_comp.time.second > b_comp.time.second)
251
                    || ((a_comp.time.second == b_comp.time.second) && ((a_comp.millisecond > b_comp.millisecond)
252
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
253
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))))))))
254
                    res += adjust_value;
255
            }
256
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeHourNumImpl<ResultPrecision::Extended>>>)
257
            {
258
                if ((a_comp.time.minute > b_comp.time.minute)
259
                    || ((a_comp.time.minute == b_comp.time.minute) && ((a_comp.time.second > b_comp.time.second)
260
                    || ((a_comp.time.second == b_comp.time.second) && ((a_comp.millisecond > b_comp.millisecond)
261
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
262
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))))))
263
                    res += adjust_value;
264
            }
265
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeMinuteNumImpl<ResultPrecision::Extended>>>)
266
            {
267
                if ((a_comp.time.second > b_comp.time.second)
268
                    || ((a_comp.time.second == b_comp.time.second) && ((a_comp.millisecond > b_comp.millisecond)
269
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
270
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))))
271
                    res += adjust_value;
272
            }
273
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeSecondNumImpl<ResultPrecision::Extended>>>)
274
            {
275
                if ((a_comp.millisecond > b_comp.millisecond)
276
                    || ((a_comp.millisecond == b_comp.millisecond) && ((a_comp.microsecond > b_comp.microsecond)
277
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))))
278
                    res += adjust_value;
279
            }
280
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeSubsecondNumImpl<millisecond_multiplier>>>)
281
            {
282
                if ((a_comp.microsecond > b_comp.microsecond)
283
                    || ((a_comp.microsecond == b_comp.microsecond) && (a_comp.nanosecond > b_comp.nanosecond)))
284
                    res += adjust_value;
285
            }
286
            else if constexpr (std::is_same_v<TransformX, TransformDateTime64<ToRelativeSubsecondNumImpl<microsecond_multiplier>>>)
287
            {
288
                if (a_comp.nanosecond > b_comp.nanosecond)
289
                    res += adjust_value;
290
            }
291
            return res;
292
        }
293
    }
294

295
    template <typename T>
296
    static UInt32 getScale(const T & v)
297
    {
298
        if constexpr (std::is_same_v<T, ColumnDateTime64>)
299
            return v.getScale();
300
        else if constexpr (std::is_same_v<T, DecimalField<DateTime64>>)
301
            return v.getScale();
302

303
        return 0;
304
    }
305
    template <typename T>
306
    static auto stripDecimalFieldValue(T && v)
307
    {
308
        if constexpr (std::is_same_v<std::decay_t<T>, DecimalField<DateTime64>>)
309
            return v.getValue();
310
        else
311
            return v;
312
    }
313
private:
314
    String name;
315
};
316

317

318
/** dateDiff('unit', t1, t2, [timezone])
319
  * age('unit', t1, t2, [timezone])
320
  * t1 and t2 can be Date, Date32, DateTime or DateTime64
321
  *
322
  * If timezone is specified, it applied to both arguments.
323
  * If not, timezones from datatypes t1 and t2 are used.
324
  * If that timezones are not the same, the result is unspecified.
325
  *
326
  * Timezone matters because days can have different length.
327
  */
328
template <bool is_relative>
329
class FunctionDateDiff : public IFunction
330
{
331
public:
332
    static constexpr auto name = is_relative ? "dateDiff" : "age";
333
    static FunctionPtr create(ContextPtr) { return std::make_shared<FunctionDateDiff>(); }
334

335
    String getName() const override
336
    {
337
        return name;
338
    }
339

340
    bool isVariadic() const override { return true; }
341
    bool isSuitableForShortCircuitArgumentsExecution(const DataTypesWithConstInfo & /*arguments*/) const override { return true; }
342
    size_t getNumberOfArguments() const override { return 0; }
343

344
    DataTypePtr getReturnTypeImpl(const DataTypes & arguments) const override
345
    {
346
        if (arguments.size() != 3 && arguments.size() != 4)
347
            throw Exception(ErrorCodes::NUMBER_OF_ARGUMENTS_DOESNT_MATCH,
348
                "Number of arguments for function {} doesn't match: passed {}, should be 3 or 4",
349
                getName(), arguments.size());
350

351
        if (!isString(arguments[0]))
352
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
353
                "First argument for function {} (unit) must be String",
354
                getName());
355

356
        if (!isDate(arguments[1]) && !isDate32(arguments[1]) && !isDateTime(arguments[1]) && !isDateTime64(arguments[1]))
357
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
358
                "Second argument for function {} must be Date, Date32, DateTime or DateTime64",
359
                getName());
360

361
        if (!isDate(arguments[2]) && !isDate32(arguments[2]) && !isDateTime(arguments[2]) && !isDateTime64(arguments[2]))
362
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
363
                "Third argument for function {} must be Date, Date32, DateTime or DateTime64",
364
                getName()
365
                );
366

367
        if (arguments.size() == 4 && !isString(arguments[3]))
368
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
369
                "Fourth argument for function {} (timezone) must be String",
370
                getName());
371

372
        return std::make_shared<DataTypeInt64>();
373
    }
374

375
    bool useDefaultImplementationForConstants() const override { return true; }
376
    ColumnNumbers getArgumentsThatAreAlwaysConstant() const override { return {0, 3}; }
377

378
    ColumnPtr executeImpl(const ColumnsWithTypeAndName & arguments, const DataTypePtr &, size_t input_rows_count) const override
379
    {
380
        const auto * unit_column = checkAndGetColumnConst<ColumnString>(arguments[0].column.get());
381
        if (!unit_column)
382
            throw Exception(ErrorCodes::ILLEGAL_COLUMN,
383
                "First argument for function {} must be constant String",
384
                getName());
385

386
        String unit = Poco::toLower(unit_column->getValue<String>());
387

388
        const IColumn & x = *arguments[1].column;
389
        const IColumn & y = *arguments[2].column;
390

391
        size_t rows = input_rows_count;
392
        auto res = ColumnInt64::create(rows);
393

394
        const auto & timezone_x = extractTimeZoneFromFunctionArguments(arguments, 3, 1);
395
        const auto & timezone_y = extractTimeZoneFromFunctionArguments(arguments, 3, 2);
396

397
        if (unit == "year" || unit == "years" || unit == "yy" || unit == "yyyy")
398
            impl.template dispatchForColumns<ToRelativeYearNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
399
        else if (unit == "quarter" || unit == "quarters" || unit == "qq" || unit == "q")
400
            impl.template dispatchForColumns<ToRelativeQuarterNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
401
        else if (unit == "month" || unit == "months" || unit == "mm" || unit == "m")
402
            impl.template dispatchForColumns<ToRelativeMonthNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
403
        else if (unit == "week" || unit == "weeks" || unit == "wk" || unit == "ww")
404
            impl.template dispatchForColumns<ToRelativeWeekNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
405
        else if (unit == "day" || unit == "days" || unit == "dd" || unit == "d")
406
            impl.template dispatchForColumns<ToRelativeDayNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
407
        else if (unit == "hour" || unit == "hours" || unit == "hh" || unit == "h")
408
            impl.template dispatchForColumns<ToRelativeHourNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
409
        else if (unit == "minute" || unit == "minutes" || unit == "mi" || unit == "n")
410
            impl.template dispatchForColumns<ToRelativeMinuteNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
411
        else if (unit == "second" || unit == "seconds" || unit == "ss" || unit == "s")
412
            impl.template dispatchForColumns<ToRelativeSecondNumImpl<ResultPrecision::Extended>>(x, y, timezone_x, timezone_y, res->getData());
413
        else if (unit == "millisecond" || unit == "milliseconds" || unit == "ms")
414
            impl.template dispatchForColumns<ToRelativeSubsecondNumImpl<millisecond_multiplier>>(x, y, timezone_x, timezone_y, res->getData());
415
        else if (unit == "microsecond" || unit == "microseconds" || unit == "us" || unit == "u")
416
            impl.template dispatchForColumns<ToRelativeSubsecondNumImpl<microsecond_multiplier>>(x, y, timezone_x, timezone_y, res->getData());
417
        else if (unit == "nanosecond" || unit == "nanoseconds" || unit == "ns")
418
            impl.template dispatchForColumns<ToRelativeSubsecondNumImpl<nanosecond_multiplier>>(x, y, timezone_x, timezone_y, res->getData());
419
        else
420
            throw Exception(ErrorCodes::BAD_ARGUMENTS,
421
                "Function {} does not support '{}' unit", getName(), unit);
422

423
        return res;
424
    }
425
private:
426
    DateDiffImpl<is_relative> impl{name};
427
};
428

429

430
/** timeDiff(t1, t2)
431
  * t1 and t2 can be Date or DateTime
432
  */
433
class FunctionTimeDiff : public IFunction
434
{
435
    using ColumnDateTime64 = ColumnDecimal<DateTime64>;
436
public:
437
    static constexpr auto name = "timeDiff";
438
    static FunctionPtr create(ContextPtr) { return std::make_shared<FunctionTimeDiff>(); }
439

440
    String getName() const override
441
    {
442
        return name;
443
    }
444

445
    bool isVariadic() const override { return false; }
446
    bool isSuitableForShortCircuitArgumentsExecution(const DataTypesWithConstInfo & /*arguments*/) const override { return true; }
447
    size_t getNumberOfArguments() const override { return 2; }
448

449
    DataTypePtr getReturnTypeImpl(const DataTypes & arguments) const override
450
    {
451
        if (arguments.size() != 2)
452
            throw Exception(ErrorCodes::NUMBER_OF_ARGUMENTS_DOESNT_MATCH,
453
                "Number of arguments for function {} doesn't match: passed {}, should be 2",
454
                getName(), arguments.size());
455

456
        if (!isDate(arguments[0]) && !isDate32(arguments[0]) && !isDateTime(arguments[0]) && !isDateTime64(arguments[0]))
457
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
458
                "First argument for function {} must be Date, Date32, DateTime or DateTime64",
459
                getName());
460

461
        if (!isDate(arguments[1]) && !isDate32(arguments[1]) && !isDateTime(arguments[1]) && !isDateTime64(arguments[1]))
462
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT,
463
                "Second argument for function {} must be Date, Date32, DateTime or DateTime64",
464
                getName()
465
                );
466

467
        return std::make_shared<DataTypeInt64>();
468
    }
469

470
    bool useDefaultImplementationForConstants() const override { return true; }
471
    ColumnNumbers getArgumentsThatAreAlwaysConstant() const override { return {}; }
472

473
    ColumnPtr executeImpl(const ColumnsWithTypeAndName & arguments, const DataTypePtr &, size_t input_rows_count) const override
474
    {
475
        const IColumn & x = *arguments[0].column;
476
        const IColumn & y = *arguments[1].column;
477

478
        size_t rows = input_rows_count;
479
        auto res = ColumnInt64::create(rows);
480

481
        impl.dispatchForColumns<ToRelativeSecondNumImpl<ResultPrecision::Extended>>(x, y, DateLUT::instance(), DateLUT::instance(), res->getData());
482

483
        return res;
484
    }
485
private:
486
    DateDiffImpl<true> impl{name};
487
};
488

489
}
490

491
REGISTER_FUNCTION(DateDiff)
492
{
493
    factory.registerFunction<FunctionDateDiff<true>>({}, FunctionFactory::CaseInsensitive);
494
    factory.registerAlias("date_diff", FunctionDateDiff<true>::name);
495
    factory.registerAlias("DATE_DIFF", FunctionDateDiff<true>::name);
496
    factory.registerAlias("timestampDiff", FunctionDateDiff<true>::name);
497
    factory.registerAlias("timestamp_diff", FunctionDateDiff<true>::name);
498
    factory.registerAlias("TIMESTAMP_DIFF", FunctionDateDiff<true>::name);
499
}
500

501
REGISTER_FUNCTION(TimeDiff)
502
{
503
    factory.registerFunction<FunctionTimeDiff>(FunctionDocumentation{.description=R"(
504
Returns the difference between two dates or dates with time values. The difference is calculated in seconds units (see toRelativeSecondNum).
505
It is same as `dateDiff` and was added only for MySQL support. `dateDiff` is preferred.
506

507
Example:
508
[example:typical]
509
)",
510
    .examples{
511
        {"typical", "SELECT timeDiff(UTCTimestamp(), now());", ""}},
512
    .categories{"Dates and Times"}}, FunctionFactory::CaseInsensitive);
513
}
514

515
REGISTER_FUNCTION(Age)
516
{
517
    factory.registerFunction<FunctionDateDiff<false>>({}, FunctionFactory::CaseInsensitive);
518
}
519

520
}
521