Saturday, August 28, 2010

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


6 comments:

  1. Hi,
    I am using following query to extract Date from the timestamp field.
    select CAST(load_time AS DATE format 'yyyymmdd' ) FROM TABLE

    still I am getting output as follows.

    load_time
    29/03/2012
    29/03/2012
    29/03/2012
    29/03/2012
    29/03/2012

    I want to get it in the format 'YYY-MM-DD' but irrespective of the format specified in th equery, it is showing output in the same format i.e. 'DD/MM/YYYY'

    ReplyDelete
  2. Hi,

    The problem is because you are running it in sql assistant . The same query works fine in bteq mode.

    Try the same query as follows
    select CAST(load_time AS DATE format 'yyyymmdd' )(char(8)) FROM TABLE

    ReplyDelete
  3. I love this site; outstanding examples and explanations! Johnathan

    ReplyDelete
  4. its really awesome

    ReplyDelete
  5. Thanks for your effort in creating the examples! Info es very clear!

    ReplyDelete
  6. Helped a lot ! Thanks from Poland ! Marcin

    ReplyDelete