Skip to main content

Celonis Product Documentation

ADD_MINUTES
Description

ADD_MINUTES adds a given number of minutes to a given timestamp. If a calendar is specified, only the passed time in the calendar is considered.

Supported input column types: start_column -> DATE, minutes_column -> INT

Output column type: DATE

If a calendar is specified and the start timestamp is not covered by the given calendar, ADD_MINUTES returns the same result as if the start timestamp was the first timestamp after the start that is covered by the calendar.

If a result of type DATE is outside the interval from the year 1400 CE (including) to the year 10000 CE (excluding), it will be mapped to NULL. Additionally if a calendar is specified, NULL is returned, if the start timestamp is outside of the scope of the calendar or if the result would lie outside of the scope. For the factory calendar the scope contains the complete year containing the first defined shift (so also all days before the first shift in that year), the complete year containing the last defined shift and all years in between.

Syntax
  ADD_MINUTES ( start_column, minutes_column [, calendar_specification [, calendar_id_column]])
 
  • start_column: Start timestamp

  • minutes_column: Number of minutes which are added to the start timestamp (can be negative)

  • calendar_specification: One of WEEKDAY_CALENDAR, FACTORY_CALENDAR, WORKDAY_CALENDAR, or INTERSECT

  • calendar_id_column: Column to create a mapping between the respective activities and their used calendar specification. This is mandatory when using multiple calendar specifications. For more details, please take a look at the respective documentation of the DateTime Calendar.

NULL handling

If any parameter is NULL, the result is NULL as well.

Exceptions

The ADD_MINUTES operator does not work if there are any gaps in the years covered by the respective WORKDAY_CALENDAR. An error will be returned if gaps are found in the workday calendar configuration.

Examples

[1]

A simple example for ADD_MINUTES without a calendar.

Query

Column1

         ADD_MINUTES ( "Table1"."Timestamps" , "Table1"."Minutes" )
        

Input

Output

Table1

Timestamps : date

Minutes : int

Mon Jan 01 2018 00:00:00.000

60

Mon Jan 01 2018 00:00:00.000

1

Mon Jan 01 2018 00:00:00.000

0

Mon Jan 01 2018 00:00:00.000

-1

Result

Column1 : date

Mon Jan 01 2018 01:00:00.000

Mon Jan 01 2018 00:01:00.000

Mon Jan 01 2018 00:00:00.000

Sun Dec 31 2017 23:59:00.000

[2]

A simple ADD_MINUTES example using a weekday calendar. The first of January 2018 is a Monday.

Query

Column1

         ADD_MINUTES ( "TimestampTable"."Timestamps" , "TimestampTable"."Minutes" , WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 ) )
        

Input

Output

TimestampTable

Timestamps : date

Minutes : int

Mon Jan 01 2018 10:00:00.000

61

Tue Jan 02 2018 09:00:00.000

-5

Result

Column1 : date

Mon Jan 01 2018 11:01:00.000

Mon Jan 01 2018 16:55:00.000

[3]

If the start timestamp is not covered by the given calendar, adding 0 minutes returns the first timestamp after the start that is covered by the calendar.

Query

Column1

         ADD_MINUTES ( "TimestampTable"."Timestamps" , "TimestampTable"."Minutes" , WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 SATURDAY 09:00 - 17:00 ) )
        

Input

Output

TimestampTable

Timestamps : date

Minutes : int

Mon Jan 01 2018 05:00:00.000

0

Mon Jan 01 2018 09:00:00.000

0

Result

Column1 : date

Mon Jan 01 2018 09:00:00.000

Mon Jan 01 2018 09:00:00.000

[4]

ADD_MINUTES can also be used with an intersection of different calendars. Only the timestamps that are covered by both calendars are taken into account.

Query

Column1

         ADD_MINUTES ( "TimestampTable"."Timestamps" , "TimestampTable"."Minutes" , INTERSECT ( WEEKDAY_CALENDAR ( MONDAY 09:00 - 17:00 TUESDAY 09:00 - 17:00 WEDNESDAY 09:00 - 17:00 THURSDAY 09:00 - 17:00 FRIDAY 09:00 - 17:00 ) , FACTORY_CALENDAR ( "FactoryTable"."START" , "FactoryTable"."END" ) ) )
        

Input

Output

FactoryTable

START : date

END : date

Tue Jan 02 2018 09:00:00.000

Tue Jan 02 2018 12:00:00.000

Tue Jan 02 2018 13:00:00.000

Tue Jan 02 2018 16:00:00.000

Wed Jan 03 2018 09:00:00.000

Wed Jan 03 2018 12:00:00.000

Wed Jan 03 2018 13:00:00.000

Wed Jan 03 2018 16:00:00.000

TimestampTable

Timestamps : date

Minutes : int

Tue Jan 02 2018 11:00:00.000

62

Wed Jan 03 2018 09:10:00.000

-20

Result

Column1 : date

Tue Jan 02 2018 13:02:00.000

Tue Jan 02 2018 15:50:00.000

[5]

It is also possible to use different calendars for different rows by specifying an additional calendar id column.

Query

Column1

         ADD_MINUTES ( "TimestampTable"."Timestamps" , "TimestampTable"."Minutes" , FACTORY_CALENDAR ( "FactoryTable" ) , "TimestampTable"."Ident" )
        

Input

Output

FactoryTable

IDENT : string

START : date

END : date

'DE'

Tue Jan 02 2018 08:00:00.000

Tue Jan 02 2018 17:00:00.000

'US'

Tue Jan 02 2018 14:00:00.000

Tue Jan 02 2018 23:00:00.000

TimestampTable

IDENT : string

Timestamps : date

Minutes : int

'DE'

Tue Jan 02 2018 00:00:00.000

61

'US'

Tue Jan 02 2018 00:00:00.000

62

Result

Column1 : date

Tue Jan 02 2018 09:01:00.000

Tue Jan 02 2018 15:02:00.000