Examples: TIMESTAMP Data Type - Teradata Vantage - Analytics Database

Example: TIMESTAMP Data Type

The following example shows TIMESTAMP used in a query.

SELECT item, quantity, saletime
FROM sales
WHERE saletime > TIMESTAMP '2000-08-25 10:14:59'
AND saletime < TIMESTAMP '2000-08-25 10:30:01';

Example: Difference Between Two TIMESTAMP Types

The difference between two TIMESTAMP types is an Interval type.

First define a table:

CREATE TABLE BillDateTime
(phone_no CHARACTER(10)
,start_time TIMESTAMP(0)
,end_time TIMESTAMP(0));

Now, determine the difference, specifying an Interval unit of DAY TO SECOND for the result:

SELECT (end_time - start_time) DAY(4) TO SECOND
FROM BillDateTime;

The DAY(4) specifies four digits of precision, and allows for a maximum of 9999 days, or approximately 27 years. The result looks like:

5 16:49:20.340000

Example: Comparison Between Two TIMESTAMP Values

The following example compares two TIMESTAMP numbers to find out if they are within 30 minutes of each other.

First define a table:

CREATE TABLE PhoneTime
(phone_no CHARACTER(10)
,start_time TIMESTAMP(0)
,end_time TIMESTAMP(0));

Note that the difference between two TIMESTAMP types is an Interval type:

SELECT phone_no
FROM PhoneTime
WHERE (end_time - start_time) DAY(4) TO MINUTE > INTERVAL '30' MINUTE;