Timestamp functions are of two types:

1. Timestampdiff function returns the total number of specified intervals between two timestamps. Passing a null timestamp to this function results in a null return value.

This function first determines the timestamp component that corresponds to the specified interval parameter, and then looks at the higher order components of both timestamps to calculate the total number of intervals for each timestamp.

For example, if the specified interval corresponds to the month component, the function calculates the total number of months for each timestamp by adding the month component and twelve times the year component.

Then the function subtracts the first timestamp’s total number of intervals from the second timestamp’s total number of intervals.

The TimestampDiff function rounds up to the next integer whenever fractional intervals represent a crossing of an interval boundary. For example, the difference in years between ‘1999-12-31’ and ‘2000-01-01′ is 1 year because the fractional year represents a crossing from one year to the next (such as 1999 to 2000).

Syntax

TimestampDiff(interval, timestamp1, timestamp2)

Where:

The specified interval valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.

timestamp1 is Any valid timestamp.

timestamp2 is Any valid timestamp.

Examples:

Select {TimestampDiff(SQL_TSI_DAY, TIMESTAMP’1998-07-31 23:35:00′, TIMESTAMP’2000-04-01 14:24:00’)}

From Employee where employeeid = 2;

In the above example, the query asks for a difference in days between timestamps ‘1998-07-31 23:35:00’ and ‘2000-04-01 14:24:00’.

It returns a value of 610. Notice that the leap year in 2000 results in an additional day.

2. TimestampAdd function adds a specified number of intervals to a specified timestamp, and returns a single timestamp.

Passing a null intExpr or timeExpr to this function results in the return of a null value.

In the simplest scenario, this function simply adds the specified integer value to the appropriate component of the timestamp, based on the interval.

Adding a week translates to adding seven days, and adding a quarter translates to adding three months.

A negative integer value results in a subtraction (such as going back in time).

Syntax

TimestampAdd(interval, intExpr, timestamp)

Where

The specified interval valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.

intExpr is Any expression that evaluates to an integer value.

timestamp is Any valid timestamp

Examples:

Select {TimestampAdd(SQL_TSI_DAY, 3, TIMESTAMP’2000-02-27 14:30:00′)}

From Employee where employeeid = 2;

In the above example, the query asks for the resulting timestamp when 3 days are added to ‘2000-02-27 14:30:00’.

Since February, 2000 is a leap year, the query returns a single timestamp of ‘2000-03-01 14:30:00’

Cubastion is among a very small number of specialized Siebel CRM Oracle Gold Partners in SME.

Cubastion has been a Gold Partner for Oracle Siebel since 2010