addDate #

adddate returns the date after adding the specified time.
Syntax

adddate(date, INTERVAL expr unit) 
adddate(expr, days)

Type

• date
  • The date or datetime expression to which time will be added.
  • Date/Date32/Datetime/Datetime64
  • String
• INTERVAL expr unit
  • The time interval and unit to add.
  • The unit can be: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, year_month
  • String
• expr
  • The time interval to add.
  • String
  • Int64
• days
  • The number of days to add.
  • String
  • Int64

Return type
Date
Example
Example 1:

SELECT adddate(date '2024-07-21', INTERVAL '3' day) as result;

┌─────result─┐ 
│ 2024-07-24 │ 
└────────────┘

Example 2:

SELECT adddate(datetime '2024-07-21', INTERVAL '3' day) as result;

┌──────────────result─┐ 
│ 2024-07-24 00:00:00 │ 
└─────────────────────┘

Example 3:

SELECT adddate('2024-07-21', INTERVAL '3' day) as result;

┌──────────────────result─┐ 
│ 2024-07-24 00:00:00.000 │ 
└─────────────────────────┘

Example 4:

SELECT adddate(datetime '2024-07-21', INTERVAL '3' second) as result;

┌──────────────result─┐ 
│ 2024-07-21 00:00:03 │ 
└─────────────────────┘

Example 5:

SELECT adddate(datetime '2024-07-21', INTERVAL '3' minute) as result;

┌──────────────result─┐ 
│ 2024-07-21 00:03:00 │ 
└─────────────────────┘

Example 6:

SELECT adddate(datetime '2024-07-21', INTERVAL '3' hour) as result;

┌──────────────result─┐ 
│ 2024-07-21 03:00:00 │ 
└─────────────────────┘

Example 7:

SELECT adddate(datetime '2024-07-21', INTERVAL '3' month) as result;

┌──────────────result─┐ 
│ 2024-10-21 00:00:00 │ 
└─────────────────────┘

Example 8:

SELECT adddate(datetime '2024-07-21', INTERVAL '3' year) as result;

┌──────────────result─┐ 
│ 2027-07-21 00:00:00 │ 
└─────────────────────┘

Example 9:

SELECT adddate(datetime '2024-07-21', INTERVAL '01:01:10' hour_second) AS result;

┌──────────────result─┐ 
│ 2024-07-21 01:01:10 │ 
└─────────────────────┘

Example 10:

SELECT adddate(datetime '2024-07-21', INTERVAL '00:10' hour_minute) AS result;

┌──────────────result─┐ 
│ 2024-07-21 00:10:00 │ 
└─────────────────────┘

Example 11:

SELECT adddate(datetime '2024-07-21', INTERVAL '1 01:01:10' day_second) AS result;

┌──────────────result─┐ 
│ 2024-07-22 01:01:10 │ 
└─────────────────────┘

Example 12:

SELECT adddate(datetime '2024-07-21', INTERVAL '01:10' minute_second) AS result;

┌──────────────result─┐ 
│ 2024-07-21 00:01:10 │ 
└─────────────────────┘

Example 13:

SELECT adddate(datetime '2024-07-21', INTERVAL '1 01:01' day_minute) AS result;

┌──────────────result─┐ 
│ 2024-07-22 01:01:00 │ 
└─────────────────────┘

Example 14:

SELECT adddate(datetime '2024-07-21', INTERVAL '1 01' day_hour) AS result;

┌──────────────result─┐ 
│ 2024-07-22 01:00:00 │ 
└─────────────────────┘

Example 15:

SELECT adddate(datetime '2024-07-21 12:13:14', INTERVAL '2 2' year_month) AS result;

┌──────────────result─┐ 
│ 2026-09-21 12:13:14 │ 
└─────────────────────┘

Example 16:

SELECT adddate('2024-07-21', '3');

┌─addDays('2024-07-21', '3')─┐ 
│    2024-07-24 00:00:00.000 │ 
└────────────────────────────┘

Example 17:

SELECT adddate('2024-07-21', 3);

┌─addDays('2024-07-21', 3)─┐ 
│  2024-07-24 00:00:00.000 │ 
└──────────────────────────┘

Example 18:

SELECT adddate(datetime '2024-07-21 12:13:14', 3);

┌─addDays(toDateTime('2024-07-21 12:13:14'), 3)─┐ 
│                           2024-07-24 12:13:14 │ 
└───────────────────────────────────────────────┘

Example 19:

SELECT adddate(datetime '2024-07-21 12:13:14', '3');

┌─addDays(toDateTime('2024-07-21 12:13:14'), '3')─┐ 
│                             2024-07-24 12:13:14 │ 
└─────────────────────────────────────────────────┘

Example 20:

SELECT adddate('2024-07-21 12:13:14', 3);

┌─addDays('2024-07-21 12:13:14', 3)─┐ 
│           2024-07-24 12:13:14.000 │ 
└───────────────────────────────────┘

Example 21:

SELECT adddate('2024-07-21 12:13:14', '3');

┌─addDays('2024-07-21 12:13:14', '3')─┐ 
│             2024-07-24 12:13:14.000 │ 
└─────────────────────────────────────┘

addDays #

This function adds a date or datetime interval to a date or datetime, then returns the result.
Syntax

addDays(date, interval) 
addDays(datetime, interval)

Parameters

• date/datetime Date/DateTime
• interval Date/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addDays(date, 1) AS add_days_with_date, 
    addDays(date_time, 1) AS add_days_with_date_time

┌─add_days_with_date─┬─add_days_with_date_time─┐ 
│ 2018-01-02         │ 2018-01-02 00:00:00     │ 
└────────────────────┴─────────────────────────┘

addHours #

This function adds a date or datetime interval to a date or datetime, then returns the result.
Syntax

addHours(date, interval) 
addHours(datetime, interval)

Parameters

• date/datetime Date/DateTime
• interval Date/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addHours(date, 1) AS add_hours_with_date, 
    addHours(date_time, 1) AS add_hours_with_date_time

┌─add_hours_with_date─┬─add_hours_with_date_time─┐ 
│ 2018-01-01 01:00:00 │ 2018-01-01 01:00:00      │ 
└─────────────────────┴──────────────────────────┘

addMinutes #

This function adds a date or datetime interval to a date or datetime, then returns the result.
Syntax

addMinutes(date, interval) 
addMinutes(datetime, interval)

Parameters

• date/datetime Date/DateTime
• interval Date/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addMinutes(date, 1) AS add_minutes_with_date, 
    addMinutes(date_time, 1) AS add_minutes_with_date_time

┌─add_minutes_with_date─┬─add_minutes_with_date_time─┐ 
│ 2018-01-01 00:01:00   │ 2018-01-01 00:01:00        │ 
└───────────────────────┴────────────────────────────┘

addMonths #

This function adds a date or datetime interval to a date or datetime, then returns the result. For example:
Syntax

addMonths(date, interval) 
addMonths(datetime, interval)

Parameters

• date/datetime Date/DateTime
• interval Date/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addMonths(date, 1) AS add_months_with_date, 
    addMonths(date_time, 1) AS add_months_with_date_time

┌─add_months_with_date─┬─add_months_with_date_time─┐ 
│ 2018-02-01           │ 2018-02-01 00:00:00       │ 
└──────────────────────┴───────────────────────────┘

addQuarters #

This function adds a date or datetime interval to a date or datetime, then returns the result.
Syntax

addQuarters(date, interval) 
addQuarters(datetime, interval)

Parameters

• date/datetime Date/DateTime
• interval Date/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addQuarters(date, 1) AS add_quarters_with_date, 
    addQuarters(date_time, 1) AS add_quarters_with_date_time

┌─add_quarters_with_date─┬─add_quarters_with_date_time─┐ 
│ 2018-04-01             │ 2018-04-01 00:00:00         │ 
└────────────────────────┴─────────────────────────────┘

addSeconds #

This function adds a date or datetime interval to a date or datetime, then returns the result. For example:
Syntax

addSeconds(date, interval) 
addSeconds(datetime, interval)

Parameters

• date/datetime Date/DateTime
• interval Date/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addSeconds(date, 1) AS add_seconds_with_date, 
    addSeconds(date_time, 1) AS add_seconds_with_date_time

┌─add_seconds_with_date─┬─add_seconds_with_date_time─┐ 
│ 2018-01-01 00:00:01   │ 2018-01-01 00:00:01        │ 
└───────────────────────┴────────────────────────────┘

addWeeks #

The function adds a date or date-time interval to a date or date-time value, then returns the resulting date or date-time.
Syntax

addWeeks(date, interval) 
addWeeks(datetime, interval)

Parameters

• date/datetime Date or date-time value
• interval Date or date-time interval

Return value

• Date or date-time value

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addWeeks(date, 1) AS add_weeks_with_date, 
    addWeeks(date_time, 1) AS add_weeks_with_date_time

┌─add_weeks_with_date─┬─add_weeks_with_date_time─┐ 
│ 2018-01-08          │ 2018-01-08 00:00:00      │ 
└─────────────────────┴──────────────────────────┘

addYears #

The function adds a date or date-time interval to a date or date-time value, then returns the resulting date or date-time. For example:
Syntax

addYears(date, interval) 
addYears(datetime, interval)

Parameters

• date/datetime Date or date-time value
• interval Date or date-time interval

Return value

• Date or date-time value

Example

WITH 
    toDate('2018-01-01') AS date, 
    toDateTime('2018-01-01 00:00:00') AS date_time 
SELECT 
    addYears(date, 1) AS add_years_with_date, 
    addYears(date_time, 1) AS add_years_with_date_time

┌─add_years_with_date─┬─add_years_with_date_time─┐ 
│ 2019-01-01          │ 2019-01-01 00:00:00      │ 
└─────────────────────┴──────────────────────────┘

addTime #

addtime returns the time after adding the specified time, that is, returns the result of expr1 plus expr2.
Syntax

addtime(expr1, expr2)

Parameter types

• expr1 Base time
  • Date/Date32
  • Datetime/Datetime64
  • Time
  • String
• expr2 Incremental time
  • Time
  • String

Return type

• String

Example
Example 1:

SELECT addtime(DATE '2023-07-15', '02:30:30');

┌─addtime(toDate('2023-07-15'), '02:30:30')─┐ 
│                   2023-07-15 02:30:30.000 │ 
└───────────────────────────────────────────┘

Example 2:

SELECT addtime(TIME '12:00:00', '02:30:30');

┌─addtime(toTimeType('12:00:00'), '02:30:30')─┐ 
│                                14:30:30.000 │ 
└─────────────────────────────────────────────┘

Example 3:

SELECT addtime(DATETIME '2023-07-15 14:00:00', '02:30:30');

┌─addtime(toDateTime('2023-07-15 14:00:00'), '02:30:30')─┐ 
│                                2023-07-15 16:30:30.000 │ 
└────────────────────────────────────────────────────────┘

Example 4:

SELECT addtime(TIMESTAMP '2023-07-15 14:00:00', Time'02:30:30');

┌─addtime(toDateTime('2023-07-15 14:00:00'), '02:30:30')─┐ 
│                                2023-07-15 16:30:30.000 │ 
└────────────────────────────────────────────────────────┘

Example 5:

SELECT addtime('2023-07-15 14:00:00', '02:30:30');

┌─addtime('2023-07-15 14:00:00', '02:30:30')─┐ 
│                    2023-07-15 16:30:30.000 │ 
└────────────────────────────────────────────┘

convert_tz #

convert_tz function is used to convert a date-time value from one timezone to another, and returns the result.
Syntax

convert_tz(expr, from_tz, to_tz)

Parameters

• expr
  • Date-time value to be converted
  • Datetime
• from_tz
  • Original timezone of the date-time value.
• to_tz
  • Target timezone to convert to.

Return type

• Datetime

Example
Example 1:

SELECT convert_tz('2023-06-15 09:30:00', '+00:00', '+08:00');

┌─convert_tz('2023-06-15 09:30:00', '+00:00', '+08:00')─┐ 
│                                   2023-06-15 17:30:00 │ 
└───────────────────────────────────────────────────────┘

Example 2:

SELECT convert_tz('2023-12-25 18:45:00', 'UTC', 'CET');

┌─convert_tz('2023-12-25 18:45:00', 'UTC', 'CET')─┐ 
│                             2023-12-25 19:45:00 │ 
└─────────────────────────────────────────────────┘

curdate #

curdate function returns the current date. Same as today()
Syntax

curdate()

Return type

• Date

Example

SELECT curdate();

┌────today()─┐ 
│ 2024-07-25 │ 
└────────────┘

curtime #

curtime function returns the current time.
Syntax

curtime()

Return type

• Time

Example

SELECT curtime();

┌────CURTIME()─┐ 
│ 16:39:50.844 │ 
└──────────────┘

date #

date returns the date from a date or date-time expression.
Syntax

DATE(expr)

Parameter types

• expr
  • Date/Date32
  • Datetime/Datetime64
  • String

Return type

• Date

Example
Example 1:

SELECT DATE('2023-06-15 08:45:30'::Datetime);

┌─DATE(CAST('2023-06-15 08:45:30', 'Datetime'))─┐ 
│                                    2023-06-15 │ 
└───────────────────────────────────────────────┘

Example 2:

SELECT DATE('2023-06-15 08:45:30'::Date);

┌─DATE(CAST('2023-06-15 08:45:30', 'Date'))─┐ 
│                                2023-06-15 │ 
└───────────────────────────────────────────┘

Example 3:

SELECT DATE('2023-06-15 08:45:30');

┌─DATE('2023-06-15 08:45:30')─┐ 
│                  2023-06-15 │ 
└─────────────────────────────┘

dateDiff #

Returns the difference between two dates or date-time values.
Syntax

dateDiff('unit', startdate, enddate, [timezone])

Parameters

• unit — Type of the result interval. String. Possible values:
  • second
  • minute
  • hour
  • day
  • week
  • month
  • quarter
  • year
• startdate — The first time value to subtract (minuend). Date or date-time value.
• enddate — The second time value to subtract (subtrahend). Date or date-time value.
• timezone — Timezone name (optional). If specified, it applies to both the start date and end date. If not specified, the timezone of the start date and end date is used. If they are not identical, the result is unspecified. String.

Example

SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00'));

┌─dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00'))─┐ 
│ 25                                                                                     │ 
└────────────────────────────────────────────────────────────────────────────────────────┘

date_format #

Same as formatDateTime()

date_add #

Adds a time interval or date interval to the provided date or datetime.
Syntax

date_add(date, value) 
date_add(datetime, value [,timezone])

Parameter

• datevalue—Date or DateTime to which the interval is added. Date or DateTime.
• timezone The timezone parameter is only allowed when the first parameter is of DateTime type.

Return value
Date or DateTime obtained by adding value.
Type: Date or DateTime.
Example

SELECT date_add(toDate('2018-01-01'),3);

┌─date_add(toDate('2018-01-01'), 3)─┐ 
│ 2018-01-04                        │ 
└───────────────────────────────────┘

SELECT date_add(toDateTime('2018-01-01 00:00:00'), 3, 'UTC');

┌─date_add(toDateTime('2018-01-01 00:00:00'), 3, 'UTC')─┐ 
│ 2018-01-03 16:00:00                                   │ 
└───────────────────────────────────────────────────────┘

date_sub #

Subtracts a time interval or date interval from the provided date or datetime.
Syntax

date_sub(date, value) 
date_sub(datetime, value [,timezone])

Parameter

• datevalue—Date or DateTime from which the interval is subtracted. Date or DateTime.
• timezone The timezone parameter is only allowed when the first parameter is of DateTime type.

Return value
Date or DateTime obtained by subtracting value.
Type: Date or DateTime.
Example

SELECT date_sub(toDate('2018-01-01'),3);

┌─date_sub(toDate('2018-01-01'), 3)─┐ 
│ 2017-12-29                        │ 
└───────────────────────────────────┘

SELECT date_sub(toDateTime('2018-01-01 00:00:00'), 3, 'UTC');

┌─date_sub(toDateTime('2018-01-01 00:00:00'), 3, 'UTC')─┐ 
│ 2017-12-28 16:00:00                                   │ 
└───────────────────────────────────────────────────────┘

dateTrunc #

Truncates date and time values to the specified date part.
Syntax

dateTrunc(unit, datetime[, timezone])

alias: DATE_TRUNC
Parameter

• unit — Interval type for the truncation result. unit parameter is case-insensitive. String type. Compatibility description for different unit values is as follows:

  | | | \

  Unit	Compatibility
  		\
  nanosecond	Compatible only with DateTime64
  		\
  microsecond	Compatible only with DateTime64
  		\
  millisecond	Compatible only with DateTime64
  		\
  second	Compatible with DateTime64, DateTime
  		\
  minute	Compatible with DateTime64, DateTime
  		\
  hour	Compatible with DateTime64, DateTime
  		\
  day	Compatible with DateTime64, DateTime, Date
  		\
  week	Compatible with DateTime64, DateTime, Date
  		\
  month	Compatible with DateTime64, DateTime, Date
  		\
  quarter	Compatible with DateTime64, DateTime, Date
  		\
  year	Compatible with DateTime64, DateTime, Date

• datetime — Date and time. Date or Date32 or DateTime or DateTime64 type.

• timezone — Optional, name of the timezone for the returned datetime. If not specified, the function will use the timezone of the datetime parameter. String type.

Return value
Returns the truncated date and time values.

| | | | \

Example
Truncation without timezone
Query:

SELECT now(), dateTrunc('hour', now());

Output:

┌───────────────now()─┬─dateTrunc('hour', now())──┐ 
│ 2020-09-28 10:40:45 │       2020-09-28 10:00:00 │ 
└─────────────────────┴───────────────────────────┘

Truncation with specified timezone
Query:

SELECT now(), dateTrunc('hour', now(), 'Asia/Istanbul');

Output:

┌───────────────now()─┬─dateTrunc('hour', now(), 'Asia/Istanbul')──┐ 
│ 2020-09-28 10:46:26 │                        2020-09-28 13:00:00 │ 
└─────────────────────┴────────────────────────────────────────────┘

#

day/dayOfMonth #

Returns the day of the month from the date.
Syntax

DAY(expr) 
DAYOFMONTH(expr)

Parameter type

• expr
  • Date/Date32
  • Datetime/Datetime64
  • Time
  • String

Return type

• UInt8

Example
Example 1:

SELECT DAYOFMONTH('2022-01-22 12:23:09');

┌─DAYOFMONTH('2022-01-22 12:23:09')─┐ 
│                                22 │ 
└───────────────────────────────────┘

Example 2:

SELECT DAYOFMONTH(TIME '17:01:10');

-- use current date 
┌─DAYOFMONTH(toTimeType('17:01:10'))─┐ 
│                                 25 │ 
└────────────────────────────────────┘

Example 3:

SELECT DAY('2022-01-22');

┌─DAY('2022-01-22')─┐ 
│                22 │ 
└───────────────────┘

dayname #

dayname Returns the name of the weekday corresponding to the date.
Syntax

dayname(expr)

Parameter type

• expr
  • Datetime/Datetime64
  • Date/Date32
  • String

Return type

• String

Example
Example 1:

SELECT dayname(DATETIME '2023-04-21 03:01:00');

┌─dayname(toDateTime('2023-04-21 03:01:00'))─┐ 
│ Friday                                     │ 
└────────────────────────────────────────────┘

Example 2:

SELECT dayname('2023-04-21 03:01:00');

┌─dayname('2023-04-21 03:01:00')─┐ 
│ Friday                         │ 
└────────────────────────────────┘

dayOfWeek #

dayofweek Returns the index value of the day of the week for the given date, with Sunday as 1 as the starting point.
Syntax

dayofweek(expr)

Parameter type

• expr
  • Datetime/Datetime64
  • Date/Date32
  • String

Return type

• UInt8

Example
Example 1:

SELECT dayofweek(DATETIME '2024-04-10 20:30:00');

┌─DAYOFWEEK(toDateTime('2024-04-10 20:30:00'))─┐ 
│                                            4 │ 
└──────────────────────────────────────────────┘

Example 2:

SELECT dayofweek(DATE '2022-01-30');

┌─DAYOFWEEK(toDate('2024-01-30'))─┐ 
│                               3 │ 
└─────────────────────────────────┘

Example 3:

SELECT dayofweek('2024-10-21');

┌─DAYOFWEEK('2024-10-21')─┐ 
│                       2 │ 
└─────────────────────────┘

dayOfYear

dayofyear Returns the number of days in the current year for the specified date, ranging from [1, 366].
Syntax

dayofyear(expr)

Parameter type

• expr
  • Datetime/Datetime64
  • Date/Date32
  • String

Return type

• UInt64

Example
Example 1:

SELECT dayofyear(DATETIME '2024-07-21 13:13:13');

┌─DAYOFYEAR(toDateTime('2024-07-21 13:13:13'))─┐ 
│                                          203 │ 
└──────────────────────────────────────────────┘

Example 2:

SELECT dayofyear('2024-07-21');

┌─DAYOFYEAR('2024-07-21')─┐ 
│                     203 │ 
└─────────────────────────┘

extract #

extract Returns the individual part of the date or time, specified by unit, such as year, month, day, hour, minute, and others.
Syntax

extract(unit FROM date)

Parameter type

• unit
  • Time unit
  • unit can be: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, year_month.
• date
  • Date or time expression for extracting part
  • Date/Date32/Datetime/Datetime64/Time
  • String

Return type

• Int64

Example
Example 1:

SELECT extract(second FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│     14 │ 
└────────┘

Example 2:

SELECT extract(minute FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│     13 │ 
└────────┘

Example 3:

SELECT extract(hour FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│     12 │ 
└────────┘

Example 4:

SELECT extract(day FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│     21 │ 
└────────┘

Example 5:

SELECT extract(month FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│      7 │ 
└────────┘

Example 6:

SELECT extract(year FROM timestamp '2024-07-21') as result;

┌─result─┐ 
│   2024 │ 
└────────┘

Example 7:

SELECT extract(year FROM datetime '2024-07-21') as result;

┌─result─┐ 
│   2024 │ 
└────────┘

Example 8:

SELECT extract(year FROM time '12:13:14') as result;

┌─result─┐ 
│   2024 │ 
└────────┘

Example 9:

SELECT extract(minute_second FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│   1314 │ 
└────────┘

Example 10:

SELECT extract(hour_second FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│ 121314 │ 
└────────┘

Example 11:

SELECT extract(hour_minute FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│   1213 │ 
└────────┘

Example 12:

SELECT extract(day_second FROM '2024-07-21 12:13:14') as result;

┌───result─┐ 
│ 21121314 │ 
└──────────┘

Example 13:

sql 
Copy code 
SELECT extract(day_minute FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│ 211213 │ 
└────────┘

Example 14:

SELECT extract(day_hour FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│   2112 │ 
└────────┘

Example 15:

SELECT extract(year_month FROM '2024-07-21 12:13:14') as result;

┌─result─┐ 
│ 202407 │ 
└────────┘

from_days #

from_days Returns the corresponding Date value based on the specified number of days expr.
Syntax

expr

Parameter type

• expr
  • String
  • UInt/Int

Return type

• DATE

Example
Example 1:

SELECT from_days(739453);

┌─from_days(739453)─┐ 
│        2024-07-21 │ 
└───────────────────┘

Example 2:

SELECT from_days('739453');

┌─from_days(739453)─┐ 
│        2024-07-21 │ 
└───────────────────┘

from_unixtime

from_unixtime Returns the value in the specified format based on the Unix timestamp. The format follows the format in the date_format function.
Syntax

from_unixtime(expr[, format])

Parameter type

• expr
  • String
  • UInt/Int
  • Float/Double
• format
  • String

Return type

• String

Example
Example 1:

SELECT from_unixtime('1704733565', '%Y %M %h:%i:%s');

┌─FROM_UNIXTIME('1704733565', '%Y %M %h:%i:%s')─┐ 
│ 2024 January 01:06:05                         │ 
└───────────────────────────────────────────────┘

Example 2:

SELECT from_unixtime(1704733565);

┌─FROM_UNIXTIME(1704733565)─┐ 
│       2024-01-09 01:06:05 │ 
└───────────────────────────┘

formatDateTime #

Formats time according to the given format string. The format is a constant expression, so a single result column cannot have multiple formats.
Syntax

formatDateTime(Time, Format[, Timezone])

Parameter

• Time date/dateTime
• Format format pattern

Replacement fields
By using replacement fields, you can define a pattern for the result string. The "Example" column displays the formatted result of 2018-01-02 22:33:44.

| | | | \

Return value
Returns the Time and Date value according to the confirmed Format.
Example

SELECT formatDateTime(toDate('2010-01-04'), '%d');

┌─formatDateTime(toDate('2010-01-04'), '%d')─┐ 
│ 04                                         │ 
└────────────────────────────────────────────┘ 

--from_unixtime 
--The function converts a Unix Timestamp to a calendar Date and Time of day. When there is only one Integer Type parameter, its behaviour is the same as toDateTime and returns DateTime Type. - 

--Example 
SELECT FROM_UNIXTIME(423543535);

┌─FROM_UNIXTIME(423543535)─┐ 
│      1983-06-04 10:58:55 │ 
└──────────────────────────┘

When there are two parameters: the first is Integer or DateTime, the second is a constant Format String — its behaviour is the same as formatDateTime and returns String Type.
For example:

SELECT FROM_UNIXTIME(1234334543, '%Y-%m-%d %R:%S') AS DateTime;

┌─DateTime────────────┐ 
│ 2009-02-11 14:42:23 │ 
└─────────────────────┘

hour #

Returns the hour in the Date Time, same as toHour().

last_day #

last_day Returns the last day of the corresponding month in the Date or Date Time.
Syntax

last_day(expr)

Type

• expr
  • Datetime/Datetime64
  • Date/Date32
  • String

Return Type

• Date

Example
Example 1:

SELECT last_day('2024-07-21');

┌─toLastDayOfMonth('2024-07-21')─┐ 
│                     2024-07-31 │ 
└────────────────────────────────┘

Example 2:

SELECT last_day(DATETIME '2024-07-21 10:00:00');

┌─toLastDayOfMonth(toDateTime('2024-07-21 10:00:00'))─┐ 
│                                          2024-07-31 │ 
└─────────────────────────────────────────────────────┘

Example 3:

SELECT last_day('2024-07-21 10:00:00');

┌─toLastDayOfMonth('2024-07-21 10:00:00')─┐ 
│                              2024-07-31 │ 
└─────────────────────────────────────────┘

localtime/localtimestamp #

Returns the current Date and Time, same as now().

makedate #

makedate Returns a Date according to the parameters year and dayofyear.
Syntax

makedate(year, dayofyear)

Type

• year
  • UInt
  • String
• dayofyear
  • UInt
  • String

Return Type

• Date

Example
Example 1:

SELECT makedate(2024, 202), makedate(2024, 203);

┌─makeDate(2024, 202)─┬─makeDate(2024, 203)─┐ 
│          2024-07-20 │          2024-07-21 │ 
└─────────────────────┴─────────────────────┘

Example 2:

SELECT makedate('2024', '202'), makedate('2024', '203');

┌─makeDate('2024', '202')─┬─makeDate('2024', '203')─┐ 
│              2024-07-20 │              2024-07-21 │ 
└─────────────────────────┴─────────────────────────┘

maketime #

maketime Returns a Time according to the parameters hour, minute, and second.
Syntax

maketime(hour, minute, second)

Type

• hour
  • UInt
  • String
• minute
  • UInt
  • String
• second
  • UInt
  • String

Return Type

• Time

Example
Example 1:

SELECT maketime(07, 21, 10);

┌─makeTime(7, 21, 10)─┐ 
│        07:21:10.000 │ 
└─────────────────────┘

Example 2:

SELECT maketime('07', '21', '30');

┌─makeTime('07', '21', '30')─┐ 
│               07:21:30.000 │ 
└────────────────────────────┘

minute #

Returns the minute in the Date Time, same as toMinute().

month #

Returns the month in the Date Time, same as toMonth().

monthname #

monthname Returns the full English name of the month in the Date.
Syntax

monthname(date)

Type

• date
  • Datetime/Datetime64
  • Date/Date32
  • String

Return Type

• String

Example
Example 1:

SELECT monthname(DATETIME '2024-07-21 00:00:00');

┌─monthName(toDateTime('2024-07-21 00:00:00'))─┐ 
│ July                                         │ 
└──────────────────────────────────────────────┘

Example 2:

SELECT monthname(DATE'2024-07-21');

┌─monthName(toDate('2024-07-21'))─┐ 
│ July                            │ 
└─────────────────────────────────┘

Example 3:

SELECT monthname('2024-07-21');

┌─monthName('2024-07-21')─┐ 
│ July                    │ 
└─────────────────────────┘

now #

Returns the current Date and Time.
Syntax

now()

Return value

• Current Date and Time.

Type: Date Time.
Example

SELECT now();

┌─now()───────────────┐ 
│ 2021-08-11 14:35:47 │ 
└─────────────────────┘

period_add #

period_add Returns expr1 in Date format and then pushes expr2 months forward.
Syntax

period_add(expr1, expr2)

Type

• expr1
  • UInt
  • String
• expr2
  • UInt
  • String

Return Type

• UInt

Example
Example 1:

SELECT period_add(202401, 6);

┌─period_add(202401, 6)─┐ 
│                202407 │ 
└───────────────────────┘

Example 2:

SELECT period_add('202401', '6');

┌─period_add('202401', '6')─┐ 
│                    202407 │ 
└───────────────────────────┘

period_diff #

period_diff Calculates the difference in months between expr1 and expr2.

Syntax

period_diff(expr1, expr2)

Type

• expr1
  • UInt
  • String
• expr2
  • UInt
  • String

Return Type

• UInt

Example
Example 1:

SELECT period_diff(202407, 202301);

┌─period_diff(202407, 202301)─┐ 
│                          18 │ 
└─────────────────────────────┘

Example 2:

SELECT period_diff('202407', '202301');

┌─period_diff('202407', '202301')─┐ 
│                              18 │ 
└─────────────────────────────────┘

quarter #

Returns the quarter in the Date Time, same as toQuarter().

sec_to_time #

sec_to_time converts a number of seconds to Time format.
Syntax

sec_to_time(seconds)

Type

• seconds
  • UInt/Int
  • String

Return type

• Time

Example
Example 1:

SELECT sec_to_time(7321);

┌─sec_to_time(7321)─┐ 
│      02:02:01.000 │ 
└───────────────────┘

Example 2:

SELECT sec_to_time('7321');

┌─sec_to_time('7321')─┐ 
│        02:02:01.000 │ 
└─────────────────────┘

second #

Returns the seconds in the DateTime, same as toSecond().

str_to_date #

str_to_date converts a String to Date or DateTime type according to the specified Date or Time Format. The Format follows the format in the date_format function.
Syntax

str_to_date(str, format)

Type

• str
  • String to be converted
  • String
• format
  • Format
  • String

Return type

• Datetime

Example
Example 1:

SELECT str_to_date('2024-07-21 12:13:14', '%Y-%m-%d %H:%i:%s') AS result;

┌─────────────────────result─┐ 
│ 2024-07-21 12:13:14.000000 │ 
└────────────────────────────┘

subtractDays #

The function subtracts a date or datetime interval from a date or datetime, then returns the date or datetime.
Syntax

subtractDays(date, interval) 
subtractDays(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractDays(date, 1) AS subtract_days_with_date, 
    subtractDays(date_time, 1) AS subtract_days_with_date_time

┌─subtract_days_with_date─┬─subtract_days_with_date_time─┐ 
│ 2018-12-31              │ 2018-12-31 00:00:00          │ 
└─────────────────────────┴──────────────────────────────┘

subtractHours #

The function subtracts a date or datetime interval from a date or datetime, then returns the date or datetime.
Syntax

subtractHours(date, interval) 
subtractHours(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractHours(date, 1) AS subtract_hours_with_date, 
    subtractHours(date_time, 1) AS subtract_hours_with_date_time

┌─subtract_hours_with_date─┬─subtract_hours_with_date_time─┐ 
│ 2002-11-25               │ 2018-12-31 23:00:00           │ 
└──────────────────────────┴───────────────────────────────┘

subtractMinutes #

The function subtracts a date or datetime interval from a date or datetime, then returns the date or datetime.
Syntax

subtractMinutes(date, interval) 
subtractMinutes(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractMinutes(date, 1) AS subtract_minutes_with_date, 
    subtractMinutes(date_time, 1) AS subtract_minutes_with_date_time

┌─subtract_minutes_with_date─┬─subtract_minutes_with_date_time─┐ 
│ 2012-08-04                 │ 2018-12-31 23:59:00             │ 
└────────────────────────────┴─────────────────────────────────┘

subtractMonths #

The function subtracts a date or datetime interval from a date or datetime, then returns the date or datetime.
Syntax

subtractMonths(date, interval) 
subtractMonths(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractMonths(date, 1) AS subtract_months_with_date, 
    subtractMonths(date_time, 1) AS subtract_months_with_date_time

┌─subtract_months_with_date─┬─subtract_months_with_date_time─┐ 
│ 2018-12-01                │ 2018-12-01 00:00:00            │ 
└───────────────────────────┴────────────────────────────────┘

subtractQuarters #

The function subtracts a date or datetime interval from a date or datetime, then returns the date or datetime.
Syntax

subtractQuarters(date, interval) 
subtractQuarters(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractQuarters(date, 1) AS subtract_quarters_with_date, 
    subtractQuarters(date_time, 1) AS subtract_quarters_with_date_time

┌─subtract_quarters_with_date─┬─subtract_quarters_with_date_time─┐ 
│ 2018-10-01                  │ 2018-10-01 00:00:00              │ 
└─────────────────────────────┴──────────────────────────────────┘

subtractSeconds #

The function subtracts a date or datetime interval from a date or datetime, then returns the date or datetime.
Syntax

subtractSeconds(date, interval) 
subtractSeconds(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractSeconds(date, 1) AS subtract_seconds_with_date, 
    subtractSeconds(date_time, 1) AS subtract_seconds_with_date_time

┌─subtract_seconds_with_date─┬─subtract_seconds_with_date_time─┐ 
│ 2012-10-02                 │ 2018-12-31 23:59:59             │ 
└────────────────────────────┴─────────────────────────────────┘

subtractWeeks #

The function subtracts a date or datetime interval from a date or datetime, then returns the date or datetime.
Syntax

subtractWeeks(date, interval) 
subtractWeeks(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractWeeks(date, 1) AS subtract_weeks_with_date, 
    subtractWeeks(date_time, 1) AS subtract_weeks_with_date_time

┌─subtract_weeks_with_date─┬─subtract_weeks_with_date_time─┐ 
│ 2018-12-25               │ 2018-12-25 00:00:00           │ 
└──────────────────────────┴───────────────────────────────┘

subtractYears #

The function subtracts a date or DateTime interval from a date or DateTime, then returns a date or DateTime.
Syntax

subtractYears(date, interval) 
subtractYears(datetime, interval)

Param

• date/datetimeDate/DateTime
• intervalDate/DateTime

Return value

• Date/DateTime

Example

WITH 
    toDate('2019-01-01') AS date, 
    toDateTime('2019-01-01 00:00:00') AS date_time 
SELECT 
    subtractYears(date, 1) AS subtract_years_with_date, 
    subtractYears(date_time, 1) AS subtract_years_with_date_time

┌─subtract_years_with_date─┬─subtract_years_with_date_time─┐ 
│ 2018-01-01               │ 2018-01-01 00:00:00           │ 
└──────────────────────────┴───────────────────────────────┘

subtime #

subtime Returns the time after expr1 minus expr2.
Syntax

subtime(expr1, expr2)

Type

• expr1
  • Expression from which Time will be subtracted
  • Datetime
• expr2
  • Time to subtract
  • String

Return type

• Datetime

Example
Example 1:

SELECT subtime(date '2024-07-21', '0:1:1');

┌─subtime(toDate('2024-07-21'), '0:1:1')─┐ 
│                2024-07-20 23:58:59.000 │ 
└────────────────────────────────────────┘

Example 2:

SELECT subtime(datetime '2024-07-21 12:13:14', '0:1:1');

┌─subtime(toDateTime('2024-07-21 12:13:14'), '0:1:1')─┐ 
│                             2024-07-21 12:12:13.000 │ 
└─────────────────────────────────────────────────────┘

Example 3:

SELECT subtime(time '12:13:14', '0:1:1');

┌─subtime(toTimeType('12:13:14'), '0:1:1')─┐ 
│                             12:12:13.000 │ 
└──────────────────────────────────────────┘

Example 4:

SELECT subtime('2024-07-21 12:13:14', '0:1:1');

┌─subtime('2024-07-21 12:13:14', '0:1:1')─┐ 
│                 2024-07-21 12:12:13.000 │ 
└─────────────────────────────────────────┘

sysdate #

Returns the current system time, same as now().

time #

Returns the time in expr.
Syntax

time(expr[, scale])

Type

• expr
  • Expression indicating the Time to extract
  • String
  • Datetime/Datetime64
• scale
  • Indicates the decimal precision of the Goal Time
  • UInt8

Return type

• Time

Example
Example 1:

SELECT time('2024-07-21 12:13:14');

┌─TIME('2024-07-21 12:13:14')─┐ 
│                12:13:14.000 │ 
└─────────────────────────────┘

Example 2:

SELECT time('2024-07-21 12:13:14', 0);

┌─TIME('2024-07-21 12:13:14', 0)─┐ 
│                       12:13:14 │ 
└────────────────────────────────┘

Example 3:

SELECT time(Datetime64'2024-07-21 12:13:14.123456', 3);

┌─TIME(toDateTime64('2024-07-21 12:13:14.123456'), 3)─┐ 
│                                        12:13:14.123 │ 
└─────────────────────────────────────────────────────┘

timeDiff #

Returns the time difference between two inputs, same as subtime().

timestamp #

timestamp Returns the date or DateTime represented by expr.
Syntax

timestamp(expr)

Type

• expr
  • Expression to convert to Timestamp
  • Date/Date32
  • String

Return type

• Datetime

Example
Example 1:

SELECT timestamp(date '2024-07-21');

┌─timestamp(toDate('2024-07-21'))─┐ 
│      2024-07-21 00:00:00.000000 │ 
└─────────────────────────────────┘

Example 2:

SELECT timestamp('2024-07-21');

┌────timestamp('2024-07-21')─┐ 
│ 2024-07-21 00:00:00.000000 │ 
└────────────────────────────┘

timestampAdd #

timestampadd Adds interval to the date or DateTime expression datetime_expr. The unit of interval is specified by unit. The value of unit can be: second, minute, hour, day, week, month, quarter, or year.
Syntax

timestampadd(unit, interval, datetime_expr)

Type

• unit
  • Time unit
• interval
  • Time interval
  • String
  • UInt64
• datetime_expr
  • Date or DateTime Expression
  • String
  • Date/Date32/Datetime/Datetime64/Time

Return type

• Datetime

Example
Example 1:

SELECT timestampadd(second, '1', datetime '2024-07-21 12:13:14') AS result;

┌──────────────result─┐ 
│ 2024-07-21 12:13:15 │ 
└─────────────────────┘

Example 2:

SELECT timestampadd(second, '1', date '2024-07-21') AS result;

┌──────────────result─┐ 
│ 2024-07-21 00:00:01 │ 
└─────────────────────┘

timestampDiff #

timestampdiff Returns the result after datetime_expr2 minus datetime_expr1 for date or DateTime expression, with the unit specified by unit. The value of unit can be: second, minute, hour, day, week, month, quarter, or year.
Syntax

timestampdiff(unit, datetime_expr1, datetime_expr2)

Type

• unit
  • Time unit
  • String
• datetime_expr1
  • Start date or DateTime Expression to calculate
  • Date/Date32/Datetime/Datetime64
• datetime_expr2
  • End date or DateTime Expression to calculate
  • Date/Date32/Datetime/Datetime64
  • String

Return type

• Int64

Example
Example 1:

SELECT timestampdiff(second, datetime '2024-07-21 10:12:13', datetime '2024-07-21 12:13:14') AS result;

┌─result─┐ 
│   7261 │ 
└────────┘

Example 2:

SELECT timestampdiff(minute, '2024-07-21 10:12:13', '2024-07-21 12:13:14');

┌─timestampdiff('minute', '2024-07-21 10:12:13', '2024-07-21 12:13:14')─┐ 
│                                                                   121 │ 
└───────────────────────────────────────────────────────────────────────┘

timeSlot #

Round the Time to the nearest half hour.
This function is unique to Yandex.Metrica, because if the tracking tag shows that the consecutive page views of a single user differ by strictly more than half an hour in time, then half an hour is the minimum time for splitting a session into two sessions. This means that you can search for the page views contained in the corresponding session using the triplet (tag ID, user ID, and time frame).
Syntax

timeSlot(datetime)

Param

• datetime

Return value

• DateTime

Example

SELECT timeSlot(toDateTime('2018-01-01 01:01:01'))

┌─timeSlot(toDateTime('2018-01-01 01:01:01'))─┐ 
│ 2018-01-01 01:00:00                         │ 
└─────────────────────────────────────────────┘

timeSlots #

For a time interval starting from "Start Time" and lasting "Duration" seconds, it returns an array of time points consisting of points within that interval, rounded to "Size" (in seconds). "Size" is an optional param: a constant UInt32, default setting is 1800.
This is necessary for searching page views in the corresponding session.
Syntax

timeSlots(StartTime, Duration,[, Size])

Param

• StartTime: start time
• Duration: duration (in seconds), UInt32
• Size: size of the interval.

Return value

• Array of time frames.

Example

SELECT timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))

┌─timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))─┐ 
│ [2012-01-01 12:00:00, 2012-01-01 12:30:00]                  │ 
└─────────────────────────────────────────────────────────────┘

Day of the month
Converts a date or datetime to a UInt8 number representing the day of the month (1-31).
Syntax

toDayOfMonth(date) 
toDayOfMonth(datetime) 
sql

Param

• date/datetime date/datetime

Return value

• A day in the month. UInt8

Example

SELECT toDayOfMonth(toDate('2021-08-12')) 
sql

┌─toDayOfMonth(toDate('2021-08-12'))─┐ 
│ 12                                 │ 
└────────────────────────────────────┘

SELECT toDayOfMonth(toDateTime('2021-08-12 00:00:00')) 
sql

┌─toDayOfMonth(toDateTime('2021-08-12 00:00:00'))─┐ 
│ 12                                              │ 
└─────────────────────────────────────────────────┘

time_format #

time_format displays the time time as a string according to the format specified by format. format follows the format in the date_format function.
Syntax

time_format(time, format)

Type

• time
  • Time to format
  • String
  • Date/Date32/Datetime/Datetime64/Time
• format
  • Format
  • String

Return type

• String

Example
Example 1:

SELECT time_format('12:00:00', '%H %k %h %I %l');

┌─TIME_FORMAT('12:00:00', '%H %k %h %I %l')─┐ 
│ 12 12 12 12 12                            │ 
└───────────────────────────────────────────┘

Example 2:

SELECT time_format(datetime '2024-07-21 23:00:00', '%H %k %h %I %l') AS result;

┌─result─────────┐ 
│ 23 23 11 11 11 │ 
└────────────────┘

Example 3:

SELECT time_format(time '23:00:00', '%H %k %h %I %l');

┌─TIME_FORMAT(toTimeType('23:00:00'), '%H %k %h %I %l')─┐ 
│ 23 23 11 11 11                                        │ 
└───────────────────────────────────────────────────────┘

Example 4:

SELECT time_format(date '2024-07-21', '%H %k %h %I %l');