td_time_bucket_number and td_gettimebucket provide different results

Database
Enthusiast

td_time_bucket_number and td_gettimebucket provide different results

Hi everyone, 

where are using the funcitons mentioned above in a sp in this way:

- For an input timestamp the time-bucket number is retrieved 

with cte_ts as (
select column_A, column_B, column_C, td_time_bucket_number(DATE '2019-01-01', in_ts, MINUTES(15)) as input_bucket, from my_table_A
)

- Then this table is join with the correspondent PTI Table

select column_A, column_B, Column C
    from cte_ts as cte
    left outer join my_pti_table as pti on pti.join_col_A = cte.join_col_A and td_gettimebucket(pti.td_timecode) = cte.input_bucket

The idea is of course to speed-up searching only in the bucket corrsponding to the input time stamp.

 

This is the ddl of the PTI Table:

CREATE MULTISET TABLE my_pti_table ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
      TD_TIMECODE TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN,
      join_column_A INTEGER NOT NULL,
      column_B VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
      column_C DECIMAL(18,4),
      created_on TIMESTAMP(6))
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2019-01-01', MINUTES(15), COLUMNS(join_column_A, column_B), NONSEQUENCED);

This used to work properly, but since last week td_time_bucket_number and td_gettimebucket are providing different results:

Example:

select td_time_bucket_number(DATE '2019-01-01', timestamp '2019-07-03 16:30:00+01:00', MINUTES(15)) as input_bucket;
-- time_bucket2 = 17635

select td_gettimebucket(td_timecode) as time_bucket, td_timecode, column_C 
from my_pti_table
where join_column_A = '100005' 
and td_timecode = timestamp '2019-07-03 16:30:00+01:00'
-- time_bucket2 = 17627

Our Teradata Version: 

16.20.24.01

 

What could be the problem?

 

Any comment will be highly appreciated.

 

BR. Paul

Kind regards,
Paul Hernandez

Accepted Solutions
Ambassador

Re: td_time_bucket_number and td_gettimebucket provide different results


According to this info, it seems like the function td_gettimebucket  is buggy.

Why do you think it's buggy, the problem is caused by using a DATE, which is converted to a Timestamp based on the session time zone:

td_time_bucket_number(DATE '2019-01-01', in_ts, MINUTES(15)) as input_bucket

If you want to return the same value (17627) use a matching TIMESTAMP instead:

td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', in_ts, Minutes(15)) AS input_bucket

 

 

1 ACCEPTED SOLUTION
13 REPLIES 13
Ambassador

Re: td_time_bucket_number and td_gettimebucket provide different results

This is related to different session time zones.

You created the table using DATE '2019-01-01', which is resolved to a Timestamp with Time Zone when the table is created (check dbc.PartitioningConstraintsV for the exact value).

 

SELECT td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', TIMESTAMP '2019-07-03 16:30:00+01:00', Minutes(15)) AS "-01:00"
      ,td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00+01:00', TIMESTAMP '2019-07-03 16:30:00+01:00', Minutes(15)) AS "+01:00";

-01:00 +01:00 17627 17635

 

Teradata Employee

Re: td_time_bucket_number and td_gettimebucket provide different results

Hi Paul,

 

It seems the function td_time_bucket_number is time zone dependant :

set time zone 'Europe Central';
select td_time_bucket_number(DATE '2019-01-01', timestamp '2019-07-03 16:30:00+01:00', MINUTES(15)) as input_bucket;
-- 17635
set time zone 'GMT'; select td_time_bucket_number(DATE '2019-01-01', timestamp '2019-07-03 16:30:00+01:00', MINUTES(15)) as input_bucket;
-- 17631

 Where td_gettimebucket seems "anchored" at GMT :

set time zone 'GMT';
select td_gettimebucket(td_timecode) as time_bucket, td_timecode
  from my_pti_table
 where join_column_A = '100005' 
   and td_timecode   = timestamp '2019-07-03 16:30:00+01:00';

time_bucket  TD_TIMECODE
-----------  --------------------------
      17631  2019-07-03 15:30:00.000000

set time zone 'Europe Central';
select td_gettimebucket(td_timecode) as time_bucket, td_timecode
  from my_pti_table
 where join_column_A = '100005' 
   and td_timecode   = timestamp '2019-07-03 16:30:00+01:00';

time_bucket  TD_TIMECODE
-----------  --------------------------
      17631  2019-07-03 17:30:00.000000

 

-- Edit after dnoeth message, it's the anchor part of the td_time_bucket_number which is TZ dependant, whereas it's not in the create table part.

Enthusiast

Re: td_time_bucket_number and td_gettimebucket provide different results

Hi Dieter, hi Waldar, 

 

thanks for your prompt and helpful answers.

According to this info, it seems like the function td_gettimebucket  is buggy. 

I will report it to Teradata support.

In the meantime I will test some different solutions.

This workaround is working:

select column_A, column_B, Column C
    from cte_ts as cte
    left outer join my_pti_table as pti on pti.join_col_A = cte.join_col_A and 
and td_gettimebucket(pti.td_timecode) >= cte.input_bucket -8
	and td_gettimebucket(pti.td_timecode) <= cte.input_bucket

Since database and input request are on "Europe Central" the maximal offset could be 2 hours with 15 Mnutes buckets = 8

I have to be careful with other solution since the time zone of the session is also relevant to rebuild the sp. 

BR.

Paul

Kind regards,
Paul Hernandez
Highlighted
Teradata Employee

Re: td_time_bucket_number and td_gettimebucket provide different results

I would do the opposite, forcing the anchor of  td_time_bucket_number to be at GMT like this, and an equijoin after  :

 

with cte_ts (column_A, column_B, column_C, input_bucket) as
(
select column_A, column_B, column_C
     , td_time_bucket_number(cast(date '2019-01-01' as timestamp(6) with time zone at 0), in_ts, MINUTES(15))
  from my_table_A
)
    select column_A, column_B, Column C
, ... from cte_ts as cte left join my_pti_table as pti on pti.join_col_A = cte.join_col_A and td_gettimebucket(pti.td_timecode) = cte.input_bucket ;
Ambassador

Re: td_time_bucket_number and td_gettimebucket provide different results


According to this info, it seems like the function td_gettimebucket  is buggy.

Why do you think it's buggy, the problem is caused by using a DATE, which is converted to a Timestamp based on the session time zone:

td_time_bucket_number(DATE '2019-01-01', in_ts, MINUTES(15)) as input_bucket

If you want to return the same value (17627) use a matching TIMESTAMP instead:

td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', in_ts, Minutes(15)) AS input_bucket

 

 

Enthusiast

Re: td_time_bucket_number and td_gettimebucket provide different results

Hi Dieter, 

Maybe buggy is not the right word. I just complain because to similar functions exhibit different behavior. According to @Waldar  tests:

td_time_bucket_number is time zone dependent

td_gettimebucket seems "anchored" at GMT

I expect both functions to behave exactly the same.

BR.

Paul

Kind regards,
Paul Hernandez
Enthusiast

Re: td_time_bucket_number and td_gettimebucket provide different results

Hi Dieter, 

 

is there any solution without using a -01:00 as you proposed:

td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', in_ts, Minutes(15)) AS input_bucket

I don't want to change my query twice a year when time zone swtiches from daylight saving to winter time.

 

Many thanks in advance,

 

Paul

Kind regards,
Paul Hernandez
Ambassador

Re: td_time_bucket_number and td_gettimebucket provide different results

Daylight saving will not affect this (and EU will stop using DST anyway Smiley Happy), because it includes the timezone:

SET TIME Zone -8;
SELECT TIMESTAMP '2019-07-03 16:31:00+01:00' AS in_ts,
td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', in_ts, Minutes(15)) AS input_bucket;

                    in_ts          input_bucket
-------------------------  --------------------
2019-07-03 16:31:00+01:00                 17627

SET TIME Zone +8; SELECT TIMESTAMP '2019-07-03 16:31:00+01:00' AS in_ts, td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', in_ts, Minutes(15)) AS input_bucket; in_ts input_bucket ------------------------- -------------------- 2019-07-03 16:31:00+01:00 17627
Enthusiast

Re: td_time_bucket_number and td_gettimebucket provide different results

Hi Dieter,

 

yes that's true, but I didn't mean the session time zone, I meant the timezero argument of the TD_TIME_BUCKET_NUMBER function:

td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', in_ts, Minutes(15)) AS input_bucket;

I cannot use minus one for all cases:

 

set time zone 'Europe Central';
select td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', timestamp '2019-03-10 16:30:00.000000+01:00', MINUTES(15)) as input_bucket
; -- time_bucket2 = 6587

set time zone 'Europe Central';
select td_gettimebucket(td_timecode) as time_bucket, td_timecode, reading_meas, is_meas
from ET_SDSP.meter_reading_1m
where meter_id = '100098' and trim(Meter_Reading_Type_CD) = '1-0:1.8.0*255'
and td_timecode = timestamp '2019-03-10 16:30:00.000000+01:00';
-- time_bucket2 = 6595

Maybe I am missunderstanding something Man Indifferent

Thanks and BR.

Paul

Kind regards,
Paul Hernandez