ADD_SECONDS
Description
ADD_SECONDS adds a given number of seconds 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, seconds_column -> INT
Output column type: DATE
If a calendar is specified and the start timestamp is not covered by the given calendar, ADD_SECONDS 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_SECONDS ( start_column, seconds_column [, calendar_specification [, calendar_id_column]])
start_column: Start timestamp
seconds_column: Number of seconds 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_SECONDS 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_SECONDS without a calendar. | |||||||||||||||||||
| |||||||||||||||||||
|
[2] In this example conversion from INT input to DATE output is demonstrated. The INT input is a count of seconds, and is interpreted as the offset to the Unix epoch (1970-01-01T00:00:00Z in ISO 8601 notation). In the query, the Unix epoch is represented by the DATE constant | ||||||||||||||
| ||||||||||||||
|
[3] A simple ADD_SECONDS example using a weekday calendar. The first of January 2018 is a Monday. | |||||||||||||
| |||||||||||||
|
[4] If the start timestamp is not covered by the given calendar, adding 0 seconds returns the first timestamp after the start that is covered by the calendar. | |||||||||||||
| |||||||||||||
|
[5] ADD_SECONDS can also be used with an intersection of different calendars. Only the timestamps that are covered by both calendars are taken into account. | |||||||||||||||||||||||
| |||||||||||||||||||||||
|
[6] It is also possible to use different calendars for different rows by specifying an additional calendar id column. | |||||||||||||||||||||||||
| |||||||||||||||||||||||||
|