All Forums Analytics
kuldeepsingh 6 posts Joined 08/06
02 Aug 2006
Failure 7453 Interval field overflow

I have a teradata procedure -- Cheking that FULFILLMENT_LINE has finished updatingsel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROCfrom EDWPROD.ETL_Control_Table WHERE Process_Timestamp in (sel max(Process_Timestamp) from EDWPROD.ETL_Control_Table WHERE Target_Tablename = 'FULFILLMENT_LINE' ) and Target_Tablename = 'FULFILLMENT_LINE' AND extract(MINUTE from TIMEPROC) <= -60 AND extract(HOUR from TIMEPROC) = 0;.if errorcode <> 0 then .exit 8.if activitycount <> 0 then .exit 8that returns with Failure 7453 I am not sure what happened. Everything was fine until about two hours back. I am not sure how to proceed and fix this. I looked at the max and min for the process_timestamp and do not show anything abnormal. Any help would be appreciated. When I just do the first part of the sql,sel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROCfrom EDWPROD.ETL_Control_Table I get the same error,When I do,sel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROCfrom EDWPROD.ETL_Control_Table WHERE Process_Timestamp in (sel max(Process_Timestamp) from EDWPROD.ETL_Control_Table WHERE Target_Tablename = 'FULFILLMENT_LINE' )it returns,TIMEPROC -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000I am not sure why the criteria after that might be causing an issue. Any help would be appreciated.Thanks,

vinod_sugur 22 posts Joined 04/05
03 Aug 2006

I am able to re-produce the error with the below example:Table vinod_12 has column ts of type timestamp with following records:ts=================2006-05-01 02:14:262006-05-03 02:14:262006-08-03 02:14:242006-08-03 02:14:252006-08-03 02:14:262006-08-03 02:14:272006-10-01 02:14:26If i execute the below query the subject mentioned error is generated:sel ((Current_Timestamp - ts) hour(2) to second(6)) TIMEPROCfrom vinod_12WHERE ts in(sel max(ts)from vinod_12)as the current_timestamp - '2006-10-01 02:14:26' results in 4 digit hour, to overcome this error while casting to interval we need to always specify maximum digits i.e in above query replace hour(2) with hour(4) in above query.I hope this helps.~

kuldeepsingh 6 posts Joined 08/06
03 Aug 2006

I will try and report the errors/results.Thank you for your help.Kuldeep

dnoeth 4628 posts Joined 11/04
03 Aug 2006

Even better, use day(4) to second, this will cover up to 27 years difference :-)Dieter

Dieter

20 Mar 2013

Hi Diter,
Please see the below mentioned issue, we are using Teradata13.10
SELECT ( (TIMESTAMP  '2011-01-17 21:00:00.000000'  - TIMESTAMP'2011-01-10 23:00:00.000000') MINUTE(4))

-- 9960

 

SELECT ( (TIMESTAMP  '2011-01-17 22:00:00.000000'  - TIMESTAMP'2011-01-10 23:00:00.000000') MINUTE(4))

 

Error:-Interval field overflow

 

9999-->It will NOT be exceeded more than 9999
If I want to achieve more than 9999 minutes, what kind of code we need to write, Please help me out from this situation
Thanks in advance  

 

 

 

Thanx,
Mahesh

dnoeth 4628 posts Joined 11/04
20 Mar 2013

Hi Mahesh,
this is a TD13.10 SQL UDF i wrote for calculating the difference in seconds:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
   ts1 TIMESTAMP(6)
  ,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
(CAST((CAST(ts2 AS DATE AT 0)- CAST(ts1 AS DATE AT 0)) AS DECIMAL(18,6)) * 86400)
      + ((EXTRACT(  HOUR FROM ts2) - EXTRACT(  HOUR FROM ts1)) * 3600)
      + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
      +  (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
; 

 
Dieter

Dieter

21 Mar 2013

Dear Diter,
Thanks for your quick reply.
i have been created UDF which you have posted for me, it is giving the results in seconds format as you said.
after that i need to divided by 60, so that exactly it will be given in minutes.
 

SELECT TimeStamp_Diff_Seconds (TIMESTAMP  '2011-01-10 23:00:00' ,TIMESTAMP '2011-01-17 22:00:00' ) --601200 Second

SELECT 601200/60=10020--MINUTES

 
Can i have any UDF which will give directly in minutes. OBIEE 11g(Reporting tool ) is accessing objects from td and will be generated the query as per business requirement. If I have UPF which will be given results in minutes.so that I can incorporate this UDF into OBIEE level.So that whenever business users requires for differ in minutes obviously it will be pointed to our UDF (MINUTES). Please help me
thanks for advance

Thanx,
Mahesh

21 Mar 2013

Hi Diter,
We are awaiting for your valuable response, Any help would be appreciate. 
 
 

Thanx,
Mahesh

21 Mar 2013

Hi Diter,
 
Finally we could create UDF for MINUTES for same.Please find the logic for sam which will be given in minutes
Let me know if i'm not wrong for same
 
 

REPLACE FUNCTION  olap_Dev.TimeStamp_Diff_Minutes

(

   Start_Ts TIMESTAMP(6)

  ,End_Ts TIMESTAMP(6)

)

RETURNS DECIMAL(18,6)

LANGUAGE SQL

CONTAINS SQL

DETERMINISTIC

SQL SECURITY DEFINER

COLLATION INVOKER

INLINE TYPE 1

RETURN

-- Get Days Difference in Minutes (* 24 hours per day * 60 minutes per hour)

(CAST(End_Ts AS DATE) - CAST(Start_Ts AS DATE)) * (24 * 60)

+

-- Get Hours Difference in Minutes ( * 60 minutes per hour)

(EXTRACT(HOUR FROM End_Ts) - EXTRACT(HOUR FROM Start_Ts)) * 60

+

-- Get Minutes Difference

(EXTRACT(MINUTE FROM End_Ts) - EXTRACT(MINUTE FROM Start_Ts));

;

 

SELECT olap_dev. TimeStamp_Diff_Minutes (TIMESTAMP  '2011-01-17 22:00:00' ,TIMESTAMP '2011-01-17 23:00:00' ) 

 

 

Thanx,
Mahesh

dnoeth 4628 posts Joined 11/04
22 Mar 2013

Hi Mahesh,
if it's correct depends on your definition, between TIMESTAMP  '2011-01-10 23:01:59' and TIMESTAMP  '2011-01-10 23:02:00' there is one second, but the result will be 1 minute, i.e. the same result as "end_ts - start_ts minute".
When you create it based on the seconds calculation you might get:
fractional minutes = timestamp_diff_seconds(start_ts, end_ts) / 60
truncated minutes = cast(timestamp_diff_seconds(start_ts, end_ts) / 60 as bigint)
 
rounded minutes = cast(timestamp_diff_seconds(start_ts, end_ts) / 60 as decimal(10,0))

You can simple nest this calculation in another UDF instead of copying/modifying the source code.
Btw, i posted a wrong version of the Timestamp_Diff_Seconds, but you already noticed that :-)
This is the correct one:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
   ts1 TIMESTAMP(6)
  ,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 
(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
      + ((EXTRACT(  HOUR FROM ts2) - EXTRACT(  HOUR FROM ts1)) * 60*60)
      + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
      +  (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
;

Dieter

Dieter

david clough 2 posts Joined 12/09
31 Dec 2013

Never used these SQL Functions before.  Just tried your difference between Timestamps Function - love it !
This is my New Year's Resolution : create some SQL Functions !
 
Happy New Year, guys
 

mohammh 2 posts Joined 06/14
10 Jul 2015

Please help with this query. Which place is the interval overflowing? I am not able to correct this after several attempts.
 
select

 

x.str_nbr,

x.year_of_calendar,

x.week_of_year,

 

sum(case when rcvd_to_ready between 0 and 15 then 1 else 0 end) as pre_promised_w,

sum(case when rcvd_to_sold between 0 and 25 then 1 else 0 end) as pre_waiters,

sum(case when rcvd_to_sold between 0 and 25 then rcvd_to_ready else 0 end) as pre_waiters_time

 

from

 

(

SELECT

    sta.rx_nbr

    ,sta.str_nbr

    ,sta.dspn_fill_nbr

    ,sta.fill_type_cd

    ,sta.year_of_calendar,

        sta.week_of_year,

        CAST(sta.fill_enter_dttm AS DATE) AS fill_enter_dt

    ,CASE   WHEN sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'N' THEN EXTRACT(DAY FROM ((sta.fill_vrfy_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_vrfy_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_vrfy_dttm - sta.scan_dttm) DAY(4) TO MINUTE))

            WHEN (sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'C') OR (sta.dspn_fill_nbr > 1 AND sta.fill_type_cd = 'R') THEN EXTRACT(DAY FROM ((sta.fill_vrfy_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_vrfy_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_vrfy_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))

            ELSE NULL END AS rcvd_to_ready

    ,CASE   WHEN sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'N' THEN EXTRACT(DAY FROM ((sta.fill_sold_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_sold_dttm - sta.scan_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_sold_dttm - sta.scan_dttm) DAY(4) TO MINUTE))

            WHEN (sta.dspn_fill_nbr = '1' AND sta.fill_type_cd = 'C') OR (sta.dspn_fill_nbr > 1 AND sta.fill_type_cd = 'R') THEN EXTRACT(DAY FROM ((sta.fill_sold_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*24*60 + EXTRACT(HOUR FROM ((sta.fill_sold_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))*60 + EXTRACT(MINUTE FROM ((sta.fill_sold_dttm - sta.fill_enter_dttm) DAY(4) TO MINUTE))

            ELSE NULL END AS rcvd_to_sold

FROM

(

    SELECT

        pf.rx_nbr

        ,pf.str_nbr

        ,pf.dspn_fill_nbr

        ,pf.fill_type_cd

        ,n.year_of_calendar,

        n.week_of_year,

       

        

        MIN(CASE WHEN (p.scan_tm IS NULL AND p.pbr_ord_nbr IS NOT NULL) THEN e.erx_msg_rcvd_dttm ELSE CAST(p.scan_dt AS TIMESTAMP(0)) + (p.scan_tm - TIME '00:00:00' HOUR TO SECOND) END) AS scan_dttm

        ,MIN(CAST(pf.fill_enter_dt AS TIMESTAMP(0)) + (pf.fill_enter_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_enter_dttm

        ,MIN(CAST(pf.fill_data_review_dt AS TIMESTAMP(0)) + (pf.fill_data_review_tm - TIME '00:00:00' HOUR TO SECOND)) AS data_review_dttm

        ,MIN(CAST(pf.fill_print_dt AS TIMESTAMP(0)) + (pf.fill_print_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_print_dttm

        ,MIN(CAST(pf.filling_dt AS TIMESTAMP(0)) + (pf.filling_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_dttm

        ,MIN(CAST(pf.fill_vrfy_dt AS TIMESTAMP(0)) + (pf.fill_vrfy_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_vrfy_dttm

        ,MIN(CAST(pf.fill_sold_dt AS TIMESTAMP(0)) + (pf.fill_sold_tm - TIME '00:00:00' HOUR TO SECOND)) AS fill_sold_dttm

    FROM

        prdedwvwh.prescription_fill AS pf

    JOIN

        (

        SELECT

            pfs.rx_nbr

            ,pfs.str_nbr

            ,pfs.rx_create_dt

            ,pfs.dspn_fill_nbr

            ,sys.year_of_calendar,

                sys.week_of_year

        FROM

            prdedwvwh.prescription_fill_sold AS pfs

 

             inner join sys_calendar.calendar sys

                                 on pfs.fill_sold_dt=sys.calendar_date

 

                 and TO_CHAR(pfs.fill_sold_dt,'YYYYMMDD') BETWEEN '20150628' and '20150704'

                 

        ) AS n

    ON

        pf.rx_nbr = n.rx_nbr

        AND

        pf.str_nbr = n.str_nbr

        AND

        pf.rx_create_dt = n.rx_create_dt

        AND

        pf.dspn_fill_nbr = n.dspn_fill_nbr

    JOIN

        prdedwvwh.prescription AS p

    ON

        pf.rx_nbr = p.rx_nbr

        AND

        pf.str_nbr = p.str_nbr

        AND

        pf.rx_create_dt = p.rx_create_dt

    LEFT JOIN

        prdedwvwh.prescription_erx_msg_map AS e

    ON

        e.rx_nbr = pf.rx_nbr

        AND

        e.str_nbr = pf.str_nbr

        AND

        e.rx_create_dt = pf.rx_create_dt

    GROUP BY

        pf.rx_nbr

        ,pf.str_nbr

        ,pf.dspn_fill_nbr

        ,pf.fill_type_cd

        ,n.year_of_calendar,

        n.week_of_year

    ) AS sta

    group by 1,2,3,4,5,6,7,8,9

    ) x

        

    where rcvd_to_sold between 0 and 25

   

    group by 1,2,3

06 Oct 2015

I have been looking for an easy way to calculate the difference in seconds between two timestamps.   The TimeSTamp_Diff_Seconds function posted by Dieter on 22MAR2013 works great.

Carlos -

srinu_nistala 7 posts Joined 07/13
17 Nov 2015

Hi Friends,
We've a scenario of subtracting two timestamp fields in a procedure. The scenario is like below.
We've set a variable value as follows.
SET v_diff_tm= current_timestamp(3);

SET v_debug_message = sp_debug( CAST( (CURRENT_TIMESTAMP(1) (FORMAT 'HH:MI:SS') ) AS CHAR(21)) || ',' || CAST(((CURRENT_TIMESTAMP(0) - v_diff_tm) SECOND(3))AS CHAR(21))
Our procedure is executing successfully from the past one year in production but suddenly it has stared failing with 'Interval Field Overflow' issue at above statement.
1.Can you please let us know what could be the reason for this sudden failure.
2.We've identified it has failed due to the above casting operation to SECOND(3), the returned seconds value is more than the given range. What can be the fix to overcome this issue.As the max value for INTERVAL SECOND is 4 ,can we change the above INTERVAL from SECOND(3) to SECOND(4).
Will it solve the problem permenantly? We need your help at the earliest. Thanks in advance !!
Regards,
Subbu.
 
 

 
 
 
 

Regards
Subbu

You must sign in to leave a comment.