LucidDbAppLib DERIVE EFFECTIVE TO TIMESTAMP

From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Parameters 4 Example 5 Source Code

Syntax

select * from table(
    applib.derive_effective_to_timestamp(
        cursor(select id, effective_from_timestamp from ... order by id, effective_from_timestamp),
        time_units_to_subtract,
        time_unit_type
    )
);

Purpose

From a list of exchange rates IDs and their effective from timestamps, return the effective to timestamps.

Parameters

Input:

• one table of 2 columns: ID (varchar), EFFECTIVE_FROM_TIMESTAMP (timestamp), presorted on ID, EFFECTIVE_FROM_TIMESTAMP
• <TIME UNITS TO SUBTRACT> positive integer value: 0, 1, 2, ...
• <TIME UNIT TYPE> string value. Legal values are 'SECOND', 'MINUTE', 'HOUR', 'DAY', 'WEEK', 'MONTH', 'YEAR'.

Output:

• one table of 3 columns: ID (varchar), EFFECTIVE_FROM_TIMESTAMP (timestamp), EFFECTIVE_TO_TIMESTAMP (timestamp)

Calculation:

The EFFECTIVE_TO_TIMESTAMP to be returned is calculated as follows:

• If rows n and n+1 have the same value in the ID column, the EFFECTIVE_TO_TIMESTAMP for row n is calculated as the EFFECTIVE_FROM_TIMESTAMP of row n+1 minus <TIME UNITS TO SUBTRACT> timeunits of type <TIME UNIT TYPE>.
• If rows n and n+1 have different values in the ID column, or n is the last row of the table, the EFFECTIVE_TO_TIMESTAMP for row n will be NULL.

Exceptions:

An exception is thrown under any of the following conditions:

• Incorrect number of input columns
• First input column can't be fetched via ResultSet.getString
• Second input column can't be fetched via ResultSet.getTimestamp
• Input table not presorted correctly

Example

select * from table(
    applib.derive_effective_to_timestamp(
        cursor(SELECT id, effective_from_timestamp FROM rates_table ORDER BY id, effective_from_timestamp),
        1,
        'DAY'
    )
);

Input

ID	EFFECTIVE_FROM_TIMESTAMP
USD~GBP~Corporate	2006-09-03
USD~GBP~Corporate	2006-09-04
USD~GBP~Corporate	2006-09-28
USD~GBP~Corporate	2006-09-30
USD~JPY~Corporate	2002-06-10
USD~JPY~Corporate	2002-06-18
USD~KRW~Corporate	2005-06-01

Output

ID	EFFECTIVE_FROM_TIMESTAMP	EFFECTIVE_TO_TIMESTAMP
USD~GBP~Corporate	2006-09-03	2006-09-03
USD~GBP~Corporate	2006-09-04	2006-09-27
USD~GBP~Corporate	2006-09-28	2006-09-29
USD~GBP~Corporate	2006-09-30	NULL
USD~JPY~Corporate	2002-06-10	2002-06-17
USD~JPY~Corporate	2002-06-18	NULL
USD~KRW~Corporate	2005-06-01	NULL

Source Code

http://p4web.eigenbase.org/open/dev/luciddb/src/com/lucidera/luciddb/applib/datetime/DeriveEffectiveToTimestampUdx.java