Timestamp 1

[devendrap]

Hi,

I am trying to find difference in minutes from two timestamp column.

Column1 type timestamp(0)
colimn2 type timestamp(0)

Select Extract ( minute from (column1 - column2 ))

This is not working.

I would really appreciate your help.

Thank you

 

[tdatgod]

Hi,
ANSI SQL defines

timestamp - timestamp == an interval.

The problem it doesn't define what the default UNIT for interval. interval units can be...

year ( number of years )

month ( number of months )

year to month ( number of years + remaninder months )

day ( number of days Maximum 9999 days ( 27 years ))

day to hour ( number of days + remainder hours )

day to minute ( number of days + remaninder hour:minutes )

day to second ( number of days + remainder hour:minute:second.sec)

hour ( number of hours maximum 9999 hours ( 13 months ))

hour to minute ( number of hours + remainder minutes )

hour to second ( number of hours + remainder minute:seconds.sec)

minute ( number of minutes 9999 minutes ( 1 week ))

minute to second ( number of minutes + remainder seconds )

second ( number of seconds )

Note that all these are singular and not plural ( hour not hours ). Again this is ANSI SQL requirement.


therefore your SQL should be more like....

sel column1 - column2 minute(4)
from blort;

(4) asks it to use 4 digits format 9999. the Maxium precision is 6 for seconds ( which is actually the decimal portion of seconds ) and the maximum number of digits for the others is 4 ( or 9999 ).

Again that is ANSI.


9999 minutes is about 7 days.

If you exceed the maximum value you will get a

*** Failure 7453 Interval field overflow.


you might want to use

sel column1 - column2 day(4) to Second
from blort;

which will allow 9999 days or roughly 27 years. the answer will look like...

135 16:49:20.340000


if you want to combine this with EXTRACT....

sel EXTRACT ( Minute from ( column1 - column2 day(4) to Second ))
from blort;

this would return from the above example

49

But 49 minutes isn't eqivelent to 195409 minutes

(((( 135 * 24 ) + 16 ) * 60 ) + 49)
days hours minutes


So which value were you trying to obtain?



If you would like more information on interval please see....

http://www.info.ncr.com/sd/sd-1.cfm?page=td

click on

SQL reference

then select

Teradata RDBMS SQL Reference - Volume 3 Data Types and Literals

chapter 4 DateTime and Interval Data Types

or

Teradata RDBMS SQL Reference - Volume 5 Functions and Operators

Chapter 5 DateTime and Interval Functions and Expressions

 

[devendrap]

Thank you, That will help...Trying to get around the problem...

Our test data has difference between two dates is about 30 years...So Above SQL is giving error of "Interval Overflow"

Do you have any other way to solve this problem with out any limit...

 

[wes23]

I use the code below to get the diff in seconds.
I just added the '/ 60' to give minutes for your benefit.

I think the cast to dec(10,0) should take care of the overflow error. Or make in dec(15,0) that's a bigger number.

I hope you have trashy test data and you really don't need to know in minutes the diff of 2 dates 30 years apart.

Have fun.....

select phone_no,
cast(360 * EXTRACT (HOUR FROM cast((cdr_end_dttm - cdr_start_dtmt hour to
second) as interval hour to second)) +
60 * EXTRACT (MINUTE FROM cast((cdr_end_dttm - cdr_start_dtmt hour to
second) as interval hour to second)) +
EXTRACT (second FROM cast((cdr_end_dttm - cdr_start_dtmt hour to second)
as interval hour to second)) as dec(10,0)) / 60 as SEC
from test_dttm
with SUM(SEC) (title 'Total Minutes'),
AVG(SEC) (title 'Average Minutes')
by phone_no
with SUM(SEC) (title 'Grand Tot Minutes'),
AVG(SEC) (title 'Grand Average Minutes')

order by 1

 

[tdatgod]

Hi,
This 4 DIGIT precision ( 9,999 days ) is a limitiation in our implementation. I will talk to our developers and see if we can change this.

What vesion of Teradata are you running since I will probably only be able to get this fixed in the most recent 4.1.x version.

 

[tdatgod]

Hi,
In the mean time I have come up with the following SQL based upon our teradata SYS_CALENDAR database which contains 3 views and 1 table which contains every date from 1900-2100.

Using this table you can calculate the number of days since 1900 to any date in question.

then since they are both based upon a fixed starting point you can subtract the 2 answers and you get the number of days between 2 dates and there is no limitation to 10,000 days ( or 27 years )

You can change the names of yourcolumns, yourdate1, yourdate2, and yourtable to match your particular needs

--

sel yourcolumns, (a.day_of_calendar - b.day_of_calendar )
from
yourtable,
SYS_CALENDAR.calendar a,
SYS_CALENDAR.calendar b
where
a.calendar_date = yourdate1
b.calendar_date = yourdate2;


Hope this helps...