WEEK_CEIL

Description

The week_ceil function rounds up an input datetime value to the nearest specified week interval start time. If origin is specified, it uses that as the reference; otherwise, it defaults to 0001-01-01 00:00:00.

Date calculation formula:

$$\begin{aligned} &\text{week\_ceil}(\langle\text{date\_or\_time\_expr}\rangle, \langle\text{period}\rangle, \langle\text{origin}\rangle) = \\ &\min\{\langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times \text{week} \mid \\ &k \in \mathbb{Z} \land \langle\text{origin}\rangle + k \times \langle\text{period}\rangle \times \text{week} \geq \langle\text{date\_or\_time\_expr}\rangle\} \end{aligned}$$

$k$ represents the number of periods needed from the reference time to reach the target time.

Syntax

WEEK_CEIL(<date_or_time_expr>)
WEEK_CEIL(<date_or_time_expr>, <origin>)
WEEK_CEIL(<date_or_time_expr>, <period>)
WEEK_CEIL(<date_or_time_expr>, <period>, <origin>)

Parameters

Parameter	Description
<date_or_time_expr>	The datetime value to round up, supports DATE/DATETIME/TIMESTAMP_NS/TIMESTAMPTZ types. Date type will be converted to the start time 00:00:00 of the corresponding date. For specific formats please see timestamptz conversion, and for datetime/date formats refer to datetime conversion and date conversion
<period>	Week interval value, type INT, representing the number of weeks in each interval
<origin>	Starting point for the interval, supports DATE/DATETIME/TIMESTAMP_NS types; defaults to 0001-01-01 00:00:00

Return Value

A TIMESTAMP_NS input returns TIMESTAMP_NS with fixed nine-digit fractional-second precision. Results are validated against the range of the return type; TIMESTAMP_NS uses its range of [1677-09-21 00:12:43.145224192, 2262-04-11 23:47:16.854775807].

When <origin> is omitted for a TIMESTAMP_NS input, the documented default origin is used only as an internal alignment reference; it does not need to fall within the storable TIMESTAMP_NS range.

Returns the temporal type selected from the input arguments, representing the rounded-up datetime value.

• If the input is non-NULL and <period> is non-positive (≤0), the function returns an error;
• If any parameter is NULL, returns NULL;
• If <datetime> is exactly at an interval start point (based on <period> and <origin>), returns that start point;
• If input is date type, returns date type
• If input is datetime type, returns datetime type with the same time portion as the origin time.
• If calculation result exceeds maximum datetime 9999-12-31 23:59:59, returns an error.
• If the <origin> date and time is after <date_or_time_expr>, it will still be calculated according to the above formula, but the period k will be negative.
• If date_or_time_expr has a scale, the result retains the corresponding precision. When origin includes fractional seconds, interval boundaries retain that fraction. In the Doris 4.1 series since 4.1.5, week boundary comparisons account for microseconds, correcting rounding when the input and origin differ only in fractional seconds.
• If the input is TIMESTAMPTZ type, it will first be converted to local_time (for example: 2025-12-31 23:59:59+05:00 represents local_time 2026-01-01 02:59:59 when the session variable is +08:00), and then perform CEIL calculation.
• If the input time values (<date_or_time_expr> and <origin>) contain both TIMESTAMPTZ and DATETIME types, the output is DATETIME type.

Example

-- 2023-07-13 is Thursday, rounds up to next interval start (1-week interval starts on Monday, so rounds to 2023-07-17 (Monday))
SELECT WEEK_CEIL(cast('2023-07-13 22:28:18' as datetime)) AS result;

+---------------------+
| result              |
+---------------------+
| 2023-07-17 00:00:00 |
+---------------------+

-- Specify 2-week interval
SELECT WEEK_CEIL('2023-07-13 22:28:18', 2) AS result;

+---------------------+
| result              |
+---------------------+
| 2023-07-24 00:00:00 |
+---------------------+

-- Input date type returns date type, date string returns datetime
SELECT WEEK_CEIL(cast('2023-07-13' as date));

+---------------------------------------+
| WEEK_CEIL(cast('2023-07-13' as date)) |
+---------------------------------------+
| 2023-07-17                            |
+---------------------------------------+

--input with decimal part 
SELECT WEEK_CEIL('2023-07-13 22:28:18.123', 2) AS result;

+-------------------------+
| result                  |
+-------------------------+
| 2023-07-24 00:00:00.000 |
+-------------------------+

-- Only with origin date and specified date
select week_ceil("2023-07-13 22:28:18", "2021-05-01 12:00:00");

+---------------------------------------------------------+
| week_ceil("2023-07-13 22:28:18", "2021-05-01 12:00:00") |
+---------------------------------------------------------+
| 2023-07-15 12:00:00                                     |
+---------------------------------------------------------+

-- Specify origin date
SELECT WEEK_CEIL('2023-07-13', 1, '2023-07-03') AS result;

+---------------------+
| result              |
+---------------------+
| 2023-07-17 00:00:00 |
+---------------------+

-- TimeStampTz sample, SET time_zone = '+08:00'
-- Convert to local_time (2026-01-01 02:59:59) and then perform WEEK_CEIL
SELECT WEEK_CEIL('2025-12-31 23:59:59+05:00');

+----------------------------------------+
| WEEK_CEIL('2025-12-31 23:59:59+05:00') |
+----------------------------------------+
| 2026-01-05 00:00:00+08:00              |
+----------------------------------------+

-- If parameters contain both TimeStampTz and Datetime types, output DateTime type
SELECT WEEK_CEIL('2025-12-31 23:59:59+05:00', '2025-12-15 00:00:00.123');

+-------------------------------------------------------------------+
| WEEK_CEIL('2025-12-31 23:59:59+05:00', '2025-12-15 00:00:00.123') |
+-------------------------------------------------------------------+
| 2026-01-05 00:00:00.123                                           |
+-------------------------------------------------------------------+

-- Invalid period (non-positive integer)
SELECT WEEK_CEIL('2023-07-13', 0) AS result;

ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[E-218]Operation week_ceil of 2023-07-13 00:00:00, 0 out of range

-- Parameter is NULL
SELECT WEEK_CEIL(NULL, 1) AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

An input exactly on an interval boundary is unchanged:

SELECT WEEK_CEIL('2023-07-24 00:00:00', 2);

2023-07-24 00:00:00

Week boundaries with a microsecond origin (corrected in the Doris 4.1 series since 4.1.5):

SELECT WEEK_CEIL(CAST('2024-01-08 00:00:00.600000' AS DATETIME(6)),
                   1, CAST('2024-01-01 00:00:00.500000' AS DATETIME(6)));

2024-01-15 00:00:00.500000