Trying to create a table that has a PPI that partions by hour.

Database
Fan

Trying to create a table that has a PPI that partions by hour.

High I am trying to create a table with a PPI that partions by hour but keep getting a 3732: The facility of a test value with a data type other than integer or DATE has not been implemented yet.

I don't see why I should not be able to partion by hour.

SQL to define the PPI is
PARTITION BY RANGE_N(XYZ_Datetime BETWEEN TIMESTAMP '2006-02-10 00:00:01' AND TIMESTAMP '2011-02-10 23:59:59' EACH INTERVAL '1' HOUR );

what I am doing wrong here?
1 REPLY
Enthusiast

Re: Trying to create a table that has a PPI that partions by hour.

Depending on your version of Teradata, you can only partition by integer fields (or fields that can be expressed as integers, like dates). Timestamps don't qualify. I think that might be available in TD13.10, but I'm not sure.

There might be a workaround here though. If you're on TD12 or higher, you could attempt a multi-level partitioning scheme that takes the date portion as the top level partition and the hour portion as the sub-level partition:

PARTITIN BY RANGE_N(CAST(XYZ_Datetime AS DATE) BETWEEN '2006-02-10' AND '2011-02-10' EACH INTERVAL '1' DAY,
CAST(EXTRACT(HOUR FROM XYZ_Datetime) AS INTEGER) BETWEEN 0 AND 23 EACH 1);

Warning: My syntax may not be 100% correct on that.