Monday, November 19, 2012

Dates, Times and Timestamps in Teradata


Dates, times, and timestamps in Teradata can be a little tricky. This document explains how to do various things that you may want to do. It is current through TD 12.

Preliminaries

The examples below use Teradata syntax, so they assume that you are running in BTEQ. If you are using SQL Assistant (Queryman), be sure to uncheckthe option box "Allow use of ODBC SQL Extensions in queries". Changing the Date Time Format in your ODBC data source to AAA or IIA may also help.
datecol means a column defined as DATE.

Defining columns and formats

Times and timestamps can be defined with any number of decimal places from 0 to 6 (time(0), timestamp(6), etc.). As it turns out, however, MP-RAS on Intel doesn’t keep track of anything beyond 2 decimal places (hundredths of seconds), so in many cases it is useless to define columns with more than 2 decimal places. If, however, you are loading data from another server, that data could have digits down to the microsecond.
The formats for all columns are described in the SQL Reference: Data Types and Literals, Chapter 9. In V2R5 and later releases, you can specify many different formats for time and timestamp fields.


Dates

Literals

Date literals can be specified in many ways:
-- in the same format as the column they are being compared to
where infamy_date = '12/07/1941'
-- with an explicit format
where infamy_date = '07Dec1941' (date, format 'DDMMMYYYY')
-- in ANSI standard form (must be preceded by the keyword DATE)
where infamy_date = date '1941-12-07'
-- in numeric form (not recommended)
where infamy_date = 411207

Observe in the examples above that the word DATE must be used to specify the data type. To get the system date, therefore, CURRENT_DATE is a better choice than DATE.

Number of days between two dates

datecol - datecol will return the number of days between two dates.
select date '2003-08-15' - date '2003-01-01';

(2003-08-15-2003-01-01)
-----------------------
                    226


Adding or subtracting months

Generally speaking, you should use the ADD_MONTHS function to add months to a date (or to subtract months). Your project may require adding a number of days, but if calendar months are required, ADD_MONTHS is the way to go.
select add_months(current_date, 3);

ADD_MONTHS(Date, 3)
-------------------
         2005-10-19


select add_months(current_date, -2);

ADD_MONTHS(Date, -2)
--------------------
          2005-05-19


/*** Last day of the month is still the last day ***/

select add_months(date '2002-01-31', 1);

ADD_MONTHS(2002-01-31, 1)
-------------------------
               2002-02-28


Computing the day of the week

Computing the day of the week for a given date is not easy in SQL. If you need a weekday, I recommend that you look it up in the view sys_calendar.calendar (or join to it), thus:
select day_of_week
   from sys_calendar.calendar
   where calendar_date = date '2003-05-01';

day_of_week
-----------
          5  [i.e. Thursday]


Computing the first day of a month

select datecol - extract(day from datecol) + 1
This subtracts the number of days since the beginning of the month, taking you to "day 0", or the day before the first of the month; then adds 1.

Computing the last day of a month

select add_months((datecol - extract(day from datecol)+1),1)-1
Same idea, but this computes the first day of the following month and then subtracts 1.

Times

Literals

The easiest way to specify a time literal is in ANSI form with a 24-hour clock:
time '15:09:17'


But in V2R5 you can use another format as long as you tell Teradata what it is:
'03-09-17PM' (time, format 'HH-MI-SST')


Changing floats to times

Some Data Dictionary tables have time columns that are defined as FLOAT rather than TIME. Here’s how to convert them to TIMEs, believe it or not:
select cast(cast(cast(TimeFld as format '99:99:99.99')
   as char(11)) as time(6))
   from DBC.EventLog ...


Adding or truncating decimal places

At first it would seem that these expressions should work:
cast(time '01:02:33' as time(6))    /* adding */
cast(time '01:02:33.456' as time(0))  /* truncating */

Alas, the first one works but the second one does not:
select cast(time '01:02:33' as time(6));

       01:02:33
---------------
01:02:33.000000

select cast(time '01:02:33.456' as time(0));
 *** Failure 7454 DateTime field overflow.

Truncating decimal places can be done in a UDF, but in SQL you must convert the time to a character string and back again:
select cast(cast(time '01:02:33.456' as char(8)) as time(0));

01:02:33.456
------------
    01:02:33

CURRENT_TIME, however, is an exception:
select cast(current_time as time(0));

Current Time(0)
---------------
       15:20:29


Time differences

First we shall address the case where your time data is defined as a number (FLOAT or DECIMAL, perhaps) in hhmmss form.
select ((time02 / 10000) * 3600 +
   (time02 / 100 MOD 100) * 60 +
   (time02 MOD 100)) -
   ((time01 / 10000) * 3600 +
   (time01 / 100 MOD 100) * 60 +
   (time01 MOD 100)) as time_diff

   from dttest;

    time_diff
-------------

        6432.  [in seconds -- about 1.8 hours]

If the earlier time could fall on one day and the later time on the next day, you may have to add 86,400 (the number of seconds in one day) to the later time, like so:
select case
   when time02 >= time01 then
      ((time02 / 10000) * 3600 +
      (time02 / 100 MOD 100) * 60 +
      (time02 MOD 100)) -
      ((time01 / 10000) * 3600 +
      (time01 / 100 MOD 100) * 60 +
      (time01 MOD 100))
   else /*** Midnight has passed ***/
      (((time02 / 10000) * 3600 +
      (time02 / 100 MOD 100) * 60 +
      (time02 MOD 100)) + 86400) -
      ((time01 / 10000) * 3600 +
      (time01 / 100 MOD 100) * 60 +
      (time01 MOD 100))
   end  as time_diff

   from dttest;

    time_diff
-------------

       18094.

Next we consider the case where your time data is defined as TIME(n). The usual way to take the difference of two times would be as follows:
select time02 - time01  hour(2) to second
   from dttest;

(time02 - time01) HOUR TO SECOND
--------------------------------
                 10:39:23.000000

The above result is an interval (INTERVAL HOUR TO SECOND, to be exact). If, however, you want to compute the difference in seconds, as above, you again have to split the times up:
select (extract(hour from time02) * 3600 +
   extract(minute from time02) * 60 +
   extract(second from time02)) -
   (extract(hour from time01) * 3600 +
   extract(minute from time01) * 60 +
   extract(second from time01)) as time_diff
   from dttest;

  time_diff
-----------
      38363


/*** After midnight ... ***/
select case
   when time02 >= time01 then
      (extract(hour from time02) * 3600 +
      extract(minute from time02) * 60 +
      extract(second from time02)) -
      (extract(hour from time01) * 3600 +
      extract(minute from time01) * 60 +
      extract(second from time01))
   else
      (extract(hour from time02) * 3600 +
      extract(minute from time02) * 60 +
      extract(second from time02) + 86400) -
      (extract(hour from time01) * 3600 +
      extract(minute from time01) * 60 +
      extract(second from time01))
   end  as time_diff

   from dttest;

  time_diff
-----------
      38363


Timestamps

Literals

The easiest way to specify a timestamp literal is in ANSI form:
timestamp '1994-12-25 23:46:29'
But in V2R5 you can use another format as long as you tell Teradata what it is:
'12/25/1994 11:46:29PM' (timestamp, format 'MM/DD/YYYYBHH:MI:SST')

Extracting the date or time portion of a timestamp

Extract the date or time portion of a timestamp thus:
select cast(ts01 as date) from dttest;

    ts01
--------
04/07/27


select cast(ts01 as time(6)) from dttest;

           ts01
---------------
10:24:37.739920


Adding or truncating decimal places

As in the case of time fields, you can add decimal places but cannot truncate them:
select cast(timestamp '2008-06-24 01:02:33' as timestamp(6));

       2008-06-24 01:02:33
--------------------------
2008-06-24 01:02:33.000000

select cast(timestamp '2008-06-24 01:02:33.456' as timestamp(0));
 *** Failure 7454 DateTime field overflow.

As before, truncating requires converting the timestamp to a character string and back again:
select cast(cast(timestamp '2008-06-24 01:02:33.456' as char(19))
  as timestamp(0));

2008-06-24 01:02:33.456
-----------------------
    2008-06-24 01:02:33

CURRENT_TIMESTAMP is not an exception:
select cast(current_timestamp as timestamp(0));
 *** Failure 7454 DateTime field overflow.

select cast(cast(current_timestamp as char(19)) as timestamp(0));

Current TimeStamp(6)
--------------------
 2008-09-19 15:27:36


Combining date and time to make a timestamp

Thanks to Dieter Nöth for this tip. The most efficient way to combine a date and a time is to cast the date to a timestamp and add the time. But you can’t simply add a time, because TIME is a point in time and not a duration. So you have to transform it into an interval:
cast(Date_column as TimeStamp(6))
   + ((Time_column - time '00:00:00') hour to second(6))


Length of time between two timestamps

You can subtract one timestamp from another. The result will be an interval, and you must specify a precision for the interval, like so:
select ts02 - ts01 day(4) to second(6)
   from dttest;

(ts02 - ts01) DAY TO SECOND
---------------------------
        371 05:26:35.639649


select ts01 - ts02 day(4) to second(6)
   from dttest;

(ts01 - ts02) DAY TO SECOND
---------------------------
       -371 05:26:35.639649

You can also convert this interval to seconds or minutes like so:
/*** Difference in seconds ***/
select (ts02 - ts01 day(4) to second) as tsdiff,
   (extract(day from tsdiff) * 86400)
   + (extract(hour from tsdiff) * 3600)
   + (extract(minute from tsdiff) * 60)
   + extract(second from tsdiff) as sec_diff
   from dttest;

               tsdiff            sec_diff
---------------------   -----------------
  371 05:26:35.639649     32073995.639649


/*** Difference in minutes ***/
select (ts02 - ts01 day(4) to minute) as tsdiff,
   (extract(day from tsdiff) * 1440)
   + (extract(hour from tsdiff) * 60)
   + extract(minute from tsdiff) as min_diff
   from dttest;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

     tsdiff      min_diff
-----------   -----------
  371 05:26        534566


Number of days between two timestamps

If you just want the number of days between two timestamps and wish to ignore the time portion, either of the following two techniques will work, but note the differences. The first technique lops off the time portion of each timestamp, so it will be equivalent to subtracting the two days; the result is an integer. The second seems to do the same but returns an interval. If you use the second technique, be sure to allow enough digits for DAY.
sel cast(ts02 as date) - cast(ts01 as date)
   from dttest;

(ts02-ts01)
-----------
        371  -- [type of this result is INTEGER]


sel ts02 - ts01 day(4)
   from dttest;

(ts02 - ts01) DAY
-----------------
              371  -- [type of this result is INTERVAL DAY]


Other operations on timestamps


Operand 1
Operator
Operand 2		Result
Type
  Timestamp    + or -    Interval    Timestamp  
  Interval    +    Timestamp    Timestamp  
  Interval    + or -    Interval    Interval  
  Interval    * or /    Numeric    Interval  
  Numeric    *    Interval    Interval  




Intervals

Literals

There are many types of intervals; see the Data Types manual for details. The key point to remember is that an interval type cannot span months, because not all months are the same length.
/* 1 year, 2 months, 5 days? */
select interval '1-02-05'  year to day;

 *** Failure 3706 Syntax error: Invalid INTERVAL type definition.


select interval '05 01:02:03' day to second;

 5 01:02:03
-----------
 5 01:02:03

The fields in intervals are smallints, so the precision cannot be greater than 4.
select interval '1234' day;

 1234
-----
 1234


select interval '12345' day(5);

 *** Failure 3706 Syntax error: Invalid INTERVAL Literal.

Casting data to intervals

If you need to convert, say, a smallint in a table to an interval, you can cast it like so:
cast(smallint_field as interval second(4))

cast(smallint_field as interval minute(4))

You can also convert combinations of fields to longer intervals by first combining them into properly formatted character strings, then casting those to intervals.
cast(
 cast(
   (smallint_hours (format '999:')) || (smallint_minutes (format '99'))
     as char(7) )
   as interval hour to minute )

Other operations with intervals

Here are some examples of the other operations mentioned in the table above.
 /******** Timestamp + Interval = Timestamp ********/
select timestamp '2008-08-15 10:11:12' + interval '5:00:39' hour to second;

(2008-08-15 10:11:12+ 5:00:39)
------------------------------
           2008-08-15 15:11:51


 /******** Timestamp - Interval = Timestamp ********/
select timestamp '2008-08-15 10:11:12' - interval '5:00:39' hour to second;

(2008-08-15 10:11:12- 5:00:39)
------------------------------
           2008-08-15 05:10:33


 /******** Interval + Timestamp = Timestamp ********/
select interval '5:00:39' hour to second + timestamp '2008-08-15 10:11:12';

( 5:00:39+2008-08-15 10:11:12)
------------------------------
           2008-08-15 15:11:51


 /******** Interval + Interval = Interval ********/
select interval '5:00:39' hour to second + interval '2:10:11' hour to second;

( 5:00:39+ 2:10:11)
-------------------
            7:10:50


 /* The interval types can be different. */
select interval '5' hour + interval '10' minute;

( 5+ 10)
--------
    5:10


select interval '5:03' hour to minute + interval '14:12' minute to second;

( 5:03+ 14:12)
--------------
       5:17:12


 /* But, as always, you cannot span months. */
select interval '2' month + '5' day;
 *** Failure 3706 Syntax error: Invalid DateTime value expression.


 /******** Interval - Interval = Interval ********/
select interval '5:00:39' hour to second - interval '2:10:11' hour to second;

( 5:00:39- 2:10:11)
-------------------
            2:50:28


select interval '5' hour - interval '10' minute;

( 5- 10)
--------
    4:50


select interval '5:03' hour to minute - interval '14:12' minute to second;

( 5:03- 14:12)
--------------
       4:48:48


 /******** Interval * Numeric = Interval ********/
select interval '5' hour * 2;

( 5*2)
------
    10


select interval '4:05:06' hour to second * 3;

( 4:05:06*3)
------------
    12:15:18


select interval '1 02' day to hour * 4;

( 1 02*4)
---------
     4 08


 /******** Interval / Numeric = Interval ********/
select interval '5' hour / 2;

( 5/2)
------
     2


select interval '4:05:06' hour to second / 3;

( 4:05:06/3)
------------
     1:21:42


select interval '1 02' day to hour / 4;

( 1 02/4)
---------
     0 06


 /******** Numeric * Interval = Interval ********/
select 2 * interval '5' hour;

(2* 5)
------
    10


select 3 * interval '4:05:06' hour to second;

(3* 4:05:06)
------------
    12:15:18


select 4 * interval '1 02' day to hour;

(4* 1 02)
---------
     4 08

Posted by at No comments:

Wednesday, October 10, 2012

Time Difference in Hours in Teradata



In Tearadata time difference calculation is bit tricky compared to SQL Command DateDiff(HH, Date1, Date2)
So here is Syntax/ Code for calculating Time difference in Hours between Today's date and EventDate
Syntax:


SELECT
CAST( CURRENT_DATE  AS TIMESTAMP(6))   -- Today's Date

, EventDate -- Date you want to check with

,(CAST(EventDate AS TIMESTAMP(6) )- CAST(CURRENT_DATE AS TIMESTAMP(6) )) DAY(4) TO MINUTE AS DHM -- Time difference in Days Hours:Minutes

,24* EXTRACT(DAY FROM (DHM))   -- Hours calculation by Days

,EXTRACT(HOUR FROM (DHM)) -- extracting extra hours from  apart from complete day

,24* EXTRACT(DAY FROM (DHM))   + EXTRACT(HOUR FROM (DHM)) AS HoursDiff -- Exact Time difference in Hours

FROM Database.TableName





Posted by at 1 comment:
Labels: , , ,

Friday, October 5, 2012

Usage of NULL and NOT Operator

The word NULL is a reserved word in any SQL. As you know NULL represents nothing, more accurately missing data or empty cell values. A comparison with NULL always returns NULL. To find out NULL values we can either have a condition with a logical operator with two NOT operators or we can use COALESE function.
Usage of NOT operator

It is a wondering truth that sometimes in life, we do NOT want something. And to satisfy this need in SQL, there is a NOT operator provided. Just prefix this before any comparison operator and you will get what you do NOT want. Here is the syntax.

SELECT {*|<col1>}, <col2>, <col3>…
FROM <TABLE name>
WHERE <COLUMN name> NOT <OPERATOR> <another COLUMN name OR constant VALUE> 

Posted by at No comments:
Labels: , ,

IN, ALL, ANY Oerator in Teradata

IN operator to have multiple values in the ‘WHERE’ clause. This is an alternative use of having multiple OR. Here is the syntax.

SELECT {*|<col1>}, <col2>, <col3>…
FROM <TABLE name>
WHERE <COLUMN name> [NOT] IN (<list OF VALUES>)

For example, to select employees from a list of department numbers, use the following SQL.
SELECT first_name, last_name
FROM employee
WHERE dept_no IN (100,250,368)


Remember that a result set would be returned even if there is only on matching dept_no found. As mentioned before if you have a NULL operator in the list then there would be no rows returned. To find out rows not matching the list of values, prefix the NOT operator before IN as follows;

SELECT first_name, last_name
FROM employee
WHERE dept_no NOT IN (210,652,555,871)

If you want to use another alternative to this, you can go for ANY, but this has to be used with a comparison operator as follows.

SELECT {*|<col1>}, <col2>, <col3>…
FROM <TABLE name>
WHERE <COLUMN name> = ANY (<list OF VALUES>)

To negate you have to swap the word with ALL and prefix with the NOT operator before the equal sign,
 i.e. NOT = ALL <list of values>



Posted by at No comments:
Labels: , , ,

Rank vs Row_Number in Teradata


Rank vs Row_Number in Teradata
Anyone working on Teradata would be aware of the two most common OLAP functions in Teradata which are used for common use i.e.
1.      Rank()
2.      Row_Number()

  In one of my posts I have mentioned about how to generate sequence number (ex. Surrogate Key) using Row_Number.   Today let us find out the difference in usage of rank and row number functions by taking following scenario on tables (ProductSales)


SELECT    Product, DateOfSale, SalesAmount,
RANK() OVER (ORDER  BY SalesAmount DESC)  ,  -- RANK function
ROW_NUMBER() OVER (ORDER     BY SalesAmount DESC) -- ROW_NUMBER function
FROM   ProductSales

The result set is as follows:



In the result set, we can observe the following,  
1.       Rows 1 and 2 have same rank assigned  since that have same SalesAmount value
2.       Rows 1 and 2 have different row numbers assigned even though same SalesAmount value
3.       Row 3 has rank “3” assigned as ranking positions

Hence , rows are always assigned an incremented sequence number with ROW_NUMBER.
Rows are always assigned the same number with RANK function.

Posted by at No comments:
Labels: , ,

List of tables and columns in Teradata database

How to extract list of columns, Tables in database (using Teradata SQL)
Syntax:



SELECT DatabaseName, TableName, ColumnName, ColumnFormat, ColumnType, ColumnLength, Nullable             
,(CASE
WHEN columntype='CV' THEN 'VARCHAR'
WHEN columntype='DA' THEN 'DATE'
WHEN columntype='I' THEN 'INTEGER'
WHEN columntype='CF' THEN 'CHAR'
WHEN columntype='F' THEN 'FLOAT'
WHEN columntype='I1' THEN 'BYTEINT'
WHEN columntype='I2' THEN 'SMALLINT'
WHEN columntype='I8' THEN 'BIGINT'
WHEN columntype='BO' THEN 'BLOB'
WHEN columntype='CO' THEN 'CLOB'
WHEN columntype='TS' THEN 'TIMESTAMP'
WHEN columntype='BF' THEN 'BYTE'
WHEN columntype='SZ' THEN 'TIMESTAMP W/ZONE'
WHEN columntype='BV' THEN 'VARBYTE'
WHEN columntype='DM' THEN 'INVERVAL DAY TO MINUTE'
WHEN columntype='D' THEN 'DECIMAL'
ELSE columntype END) 
AS DataType

FROM DBC.COLUMNS
WHERE databasename = 'DatabaseName'

Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)