SECONDS_BETWEEN
Description
SECONDS_BETWEEN computes the difference between two timestamps in seconds. If a calendar is specified, only the passed time in the calendar is considered.
Supported input column types: DATE
Output column type: FLOAT
If a calendar is specified and either timestamp is not covered by the given calendar, SECONDS_BETWEEN returns the same result as if the next timestamp covered by the calendar was used instead.
If a calendar is specified and either the start or end timestamp are outside of the scope of the calendar, NULL is returned. 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
SECONDS_BETWEEN ( start_column, end_column [, calendar_specification [, calendar_id_column]] )
start_column: Start timestamp
end_column: End timestamp
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 SECONDS_BETWEEN 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 SECONDS_BETWEEN example without a specified calendar. | |||||||||||||
| |||||||||||||
|
[2] In this example conversion from DATE input to FLOAT output is demonstrated. The FLOAT output is a count of seconds, which is 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 SECONDS_BETWEEN example using a weekday calendar. The first of January 2018 is a Monday. | |||||||||||||
| |||||||||||||
|
[4] If either input timestamp is not covered by the given calendar, the result is the same as if the next timestamp covered by the calendar was used instead. In the first row, the start is not during a shift. In the second row, the end date is not during a shift. | |||||||||||||
| |||||||||||||
|
[5] SECONDS_BETWEEN 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. | |||||||||||||||||||||||||
| |||||||||||||||||||||||||
|