Typical scenarios for post-processing the return values of scheduling parameters - DataWorks

In scheduled tasks, the standard time format returned by a scheduling parameter may not meet complex business requirements, such as obtaining the end of a month, a quarter, or a custom time offset. In these cases, you can post-process the return value using engine functions to generate the required time format. The methods include direct transformation within a compute node or pre-processing using an assignment node.

Business scenarios

In data development and extract, transform, and load (ETL) flows, task scheduling often depends on specific time dimensions. Examples include monthly settlements based on a financial cycle (the last day of the previous month) or incremental data synchronization using time windows at the hour or minute level. Because scheduling parameters return a standard time format by default, they cannot directly generate these complex, non-standard time strings. Therefore, their return values must be post-processed.

Solutions

There are two main ways to post-process scheduling parameters: direct processing within a node that supports function computation, such as an ODPS SQL node, or pre-processing with an assignment node before passing the result to a downstream node. The choice of method depends on whether the target node supports direct function transformations in its code.

Direct processing: In nodes that support function computation, such as ODPS SQL nodes, you can use engine functions directly in the code to transform the return values of scheduling parameters.
Indirect processing: When a target node (such as some offline sync nodes) does not support function transformations in its code, you can add an upstream assignment node. This assignment node performs the function transformation and passes its output as a context parameter to the descendant node.

Implementation steps

Assume that for all the following scenarios, the scheduled time (CYCTIME) of the task instance is 20250727000500.

Scenario 1: Directly process return values in an SQL node

Using an ODPS SQL node as an example, this node supports processing scheduling parameters directly in SQL statements with MaxCompute SQL functions. For other SQL nodes, refer to the function types supported by the specific engine.

Get the last day of the previous month

Obtain the last day of the previous month in `yyyymmdd` format. This is often used for partitioning or settling monthly data.

Parameter configuration: Configure a scheduling parameter to obtain the year and month of the current scheduled task.
last_month=$[yyyy-mm]
Processing logic: In the ODPS SQL code, first construct the first day of the current month. Then, use the DATEADD function to subtract one day to obtain the last day of the previous month. Use the REPLACE function to remove the separators.
```
SELECT REPLACE(DATEADD(date'${last_month}-01',-1,'dd'),'-','');
```
Example: The last_month parameter value is 2025-07. After function processing, the return value is 20250630.

Get the current quarter

Calculate the quarter (1, 2, 3, or 4) to which the scheduled time of the task belongs, based on the month.

Parameter configuration: Configure a scheduling parameter to obtain the month.
month=$[mm]
Processing logic: In the ODPS SQL code, divide the month by 3 and round up to the nearest integer to calculate the quarter.
```
SELECT CEIL(CAST('${month}' AS INT)/3);
```
Example: The month parameter value is 07. After function processing, the return value is 3.

Get the time components 15 minutes before the scheduled time

In scenarios that require precise time points for data segmentation, you can obtain any time offset relative to the scheduled time.

Parameter configuration: Directly obtain the year, month, day, hour, and minute from 15 minutes ago by performing the mathematical operation -15/24/60 in the scheduling parameter.
year=$[yyyy-15/24/60]
month=$[yyyymm-15/24/60]
day=$[yyyymmdd-15/24/60]
hour=$[hh24-15/24/60]
mi=$[mi-15/24/60]
Processing logic: Directly reference these pre-calculated parameters in the SQL statement.
```
select 'year=${year} month=${month} day=${day} hour=${hour} mi=${mi}';
```
Example: After the cross-day calculation, the return value is year=2025 month=202507 day=20250726 hour=23 mi=50.

Scenario 2: Generate a time range for incremental synchronization

Generate time parameters for batch sync tasks that require a specified time range. Assume these scenarios require the time format yyyymmddhh24miss and a left-open, right-closed interval.

Scheduling interval of one day

Obtain the time range from 00:00:00 yesterday to 00:00:00 today.

Parameter configuration: Obtain the dates for yesterday and today separately.
beginDateTime=$[yyyymmdd-1]
endDateTime=$[yyyymmdd]
Processing logic: In the `WHERE` clause for data filtering, concatenate strings to add the hour, minute, and second parts.
```
event_time > '${beginDateTime}000000' AND event_time <= '${endDateTime}000000'
```
Example: The start time of the return value is 20250726000000, and the end time is 20250727000000.

Scheduling interval of one hour

Obtain the time range from 00:00 of the previous hour to 00:00 of the current hour.

Parameter configuration: Use a mathematical operation to obtain the previous hour and the current hour.
beginDateTime=$[yyyymmddhh24-1/24]
endDateTime=$[yyyymmddhh24]
Processing logic: In the `WHERE` clause for data filtering, concatenate strings to add the minute and second parts.
```
event_time >= '${beginDateTime}0000' AND event_time < '${endDateTime}0000'
```
Example: The start time of the return value is 20250726230000, and the end time is 20250727000000.

Scenario 3: Pre-process parameters for downstream nodes using an assignment node

When a target node, such as some batch sync nodes, cannot directly process scheduling parameters using functions, you can use an assignment node for pre-processing. For example, when a batch sync task needs to use a timestamp field for incremental synchronization, follow these steps:

You can create an assignment node.
In the assignment node, use an engine function, such as unix_timestamp, to convert a standard scheduling parameter, for example, $[yyyymmddhh24miss], into a UNIX timestamp format.
Use the output from the assignment node (the converted timestamp) as an input parameter for the downstream offline sync node. For more information, see Configure and use node context parameters.