Teradata Timestamp tips & tricks

Some tips which might be useful while working on date and timestamp. (queries run and verified on V2R6)

Tip 1: Create Timestamp from Date and Time

SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0));

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Tip 2: Convert timestamp field in Teradata into format 'MMM-YYYY'

select CURRENT_TIMESTAMP (FORMAT 'MMM-YYYY');

Current TimeStamp(6)

-------------------

Mar-2010

Tip 3: Inserting current_timestamp value into timestamp(0) column

Create table stats(record_time TIMESTAMP(0));

Whenever we try to populate the current_timestamp value into this column using

insert into stats(record_time) select current_timestamp ; we get following error.

7454: Date Time field overflow.

To avoid this, we have two solutions

Solutions:

1. Data type for CURRENT_TIMESTAMP is TIMESTAMP(6) and you are trying to insert a

TIMESTAMP(6) data to a TIMESTAMP(0) and that's why you are getting this error.

insert into stats(record_time)

Sel cast(CURRENT_DATE as TimeStamp(0))

+

((CURRENT_TIME - time '00:00:00') hour to second(0)) ;

2. insert into stats(record_time) select CURRENT_TIMESTAMP(0);

Tip 4: Add minutes to timestamp

How to add 10 minutes to current timestamp in TD?

SEL CURRENT_TIMESTAMP + INTERVAL '10' MINUTE.

The following example is taken straight from Teradata Functions and Operators reference manual.

Example 1: Subtract two TIMESTAMP Types

Consider the following table:

CREATE TABLE BillDateTime

(start_time TIMESTAMP(0),end_time TIMESTAMP(0));

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 is an Interval that looks like this:

5 16:49:20.340000

Tip 5: Convert varchar to timestamp

have a column in source strdate1 as VARCHAR(23) and the value being '20100310181010'.

SELECT CAST ('20100310181010' AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS');

Tip 6: Display Timestamp with AM /PM format

SELECT CAST(TIMESTAMP'2010-03-12 14:32:45' AS FORMAT 'yyyymmddbhh:mi:ssbt') (char(20));

Tip 7: Display timestamp in 22 Mar 2010 00:26:54.840  format

select cast(current_TIMESTAMP(3)  as timestamp(3) FORMAT 'ddbmmmbyyyybhh:mi:ss.s(3)')(char(25));

 Tip 8: Subtract two TIMESTAMP Types

Consider the following table:

CREATE TABLE BillDateTime (start_time TIMESTAMP(0),end_time TIMESTAMP(0) );

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 is an Interval that looks like this:

5 16:49:20.340000

 Tip 9: Extract function on  DATE & TIME  

Extracting From Current Date(March 20,2010)

·               Query                                                                                      Result

  SELECT DATE;                                                                 10/03/20 (Default format)

   SELECT EXTRACT(YEAR FROM DATE);               2010

   SELECT EXTRACT(MONTH FROM DATE);             03

   SELECT EXTRACT(DAY FROM DATE);                  20

   SELECT EXTRACT(YEAR FROM DATE + 365);      2011

   SELECT EXTRACT(MONTH FROM DATE + 30);     04

   SELECT EXTRACT(DAY FROM DATE + 12);          01

Extracting From Current Time ( 2:42 PM )

               Query                                                                           Result  

SELECT TIME;                                                                    14:42:32 (Default format)

SELECT EXTRACT(HOUR FROM TIME);                               14

SELECT EXTRACT(MINUTE FROM TIME);                            42