TD 12 Timestamp Partitioning... what about MLPPI?

Database
Teradata Employee

TD 12 Timestamp Partitioning... what about MLPPI?

I know Timestamp Partitioning kind of work in TD12. I need to provide my Offshore DBAs a document to help them putting it in place. Is there such documents/email/exemples out there?

A few months ago Timestamps Partitioning wasn't working with MLPPI in TD12. Does that still the case?

Thanks

Pat
3 REPLIES
Teradata Employee

Re: TD 12 Timestamp Partitioning... what about MLPPI?

As far as I know by doing an internal converision to DATE , its working:

CREATE SET TABLE sysdba.FOO
(
C1 DATE FORMAT 'yyyy-mm-dd',
C2 DECIMAL(15,0),
C3 TIMESTAMP(2),
C4 CHAR(1) )
PRIMARY INDEX ( C2 )
PARTITION BY ( RANGE_N(C3 (DATE) BETWEEN DATE '2010-01-01' AND DATE '2010-12-30' EACH INTERVAL '30' DAY ),
RANGE_N(C1 BETWEEN DATE '2010-01-01' AND DATE '2010-12-30' EACH INTERVAL '30' DAY ) );
Enthusiast

Re: TD 12 Timestamp Partitioning... what about MLPPI?

Ho Pat,

Did you find any details about Time stamp partitioning ? I am on Teradata 13. I tried partitioning Time stamp by casting as DATE as Vador above pointed but there is not partition elimination in queries.

I tried
PARTITION BY RANGE_N(COL_DTM BETWEEN TIMESTAMP '0001-01-01 00:00:00.000' AND TIMESTAMP '1999-12-31 23:59:59.999' EACH INTERVAL '1000' YEAR ,
TIMESTAMP '2000-01-01 00:00:00.000' AND TIMESTAMP '2016-12-31 23:59:59.999' EACH INTERVAL '1' MONTH ,
TIMESTAMP '2017-01-01 00:00:00.000' AND TIMESTAMP '9999-12-31 23:59:59.999' EACH INTERVAL '1000' YEAR)

but create table failed. 3732: the facility of a test value with a data type other than integer or DATE has not been implemented yet.
Enthusiast

Re: TD 12 Timestamp Partitioning... what about MLPPI?

TRY Cast it as DATE and add [AT TIME ZONE 0 ] 

PARTITION BY RANGE_N(CAST((C3) AS DATE AT TIME ZONE 0 )  BETWEEN ....

i don't know why it worked for me