td_time_bucket_number and td_gettimebucket provide different results

Database
Ambassador

Re: td_time_bucket_number and td_gettimebucket provide different results

If you use a TIMESTAMP WITH TIME ZONE literal the session time zone is irrelevant.

Is this the same table or a different one?

They might have different timezeros, check dbc.PartitioningConstraintsV

Enthusiast

Re: td_time_bucket_number and td_gettimebucket provide different results

Hi everone, 

@dnoeth I finally got the point with the anchor at timestamp -01:00

After selecting the info from as suggested:

CHECK (/*01 08 00*/ RANGE_N(TD_TIMECODE  BETWEEN TIMESTAMP '2019-01-01 01:00:00.000000+01:00' AND * EACH INTERVAL '0.000001' SECOND ) /*1 251856000000000000+8971516036854775807*/ IS NOT NULL )

Since the table was created in a session with +1 time zone the the RANGE_N starts with TIMESTAMP '2019-01-01 01:00:00.000000+01:00' and then in theory if I select  the time bucket number with the -01:00 the result is adjusted to gmt (or something like this, maybe is not the best description)

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

What is still freaking me out is this inconsistence:

 Function  
Timestamptd_time_bucket_number (*)td_gettimebucketDiff
2019-07-10 16:30:00+01:0018299182990
2019-06-20 16:30:00+01:0016379163878
2019-05-20 16:30:00+01:0013403134118
2019-03-10 10:00:00+01:00656165698

 

(*) Using td_time_bucket_number(TIMESTAMP '2019-01-01 00:00:00-01:00', in_ts, Minutes(15)) 

 

As you can see, the same table is used and just for recent values the bucket using the functions above match.

Maybe is something wrong with teradata studio but I am not able to determine the error.

 

Kind regards, 

Paul

Kind regards,
Paul Hernandez
Highlighted
Ambassador

Re: td_time_bucket_number and td_gettimebucket provide different results

Now that's really strange, imho the difference should always be the same for the same table.

You should open an incident with Teradata support.

 

"since last week td_time_bucket_number and td_gettimebucket are providing different results":

Maybe there was a patch installed or some time zone related dbscontrol fields were changed...

Visitor

Re: td_time_bucket_number and td_gettimebucket provide different results

Hi Pul and others!

I did som tests with the both functions today and found out that they work properely, as long as the time bucket index is consistent in the whole table. In our case the time bucket was inconsistent. The first part of records had been written by a "reset" query into the table, which inserts the type "timestamp" into the target column of td_timecode type. Since then, further records are being  refreshed by a "refresh" query. The "refresh" query applied the function current_timestamp when defining the end of the upsert window. The current_timestamp generates "timestamp with time zone"(!) and this type seems to be incompatible with TD_TIMECODE. Although DB inserts the ts_with_tz into td_timecode, the bucket value is false (or vice versa, bucket might be correct while the timestamp is shifted)

In our case the following workaround helped: cast(current_timestamp as timestamp) -> TD_TIMECODE

I recall that this behaviour is independent on the session's time zone setup.

I suggest that TD better raises an exception against attempts like "insert ts_with_tz into td_timecode", at least for such cases when the user is not aware of doing it (e.g. current_timestamp).