td_time_bucket_number and td_gettimebucket provide different results

Database
Highlighted
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
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