'Date' column as a ShardKey


How can a ‘Date’ column be specified as a shardkey for a table? MapD Community Edition fails with the following error message(s) when this attempted:

CREATE TABLE underlyingpositionsnapshot1 ( tradedate date encoding fixed(32), …, SHARD KEY (tradedate) ) WITH (shard_count = 32)
FIXED(32) encoding:
Exception: Cannot shard on type DATE, encoding FIXED

No encoding specified:
Exception: Cannot shard on type DATE, encoding NONE



We do not support sharding on DATE

see https://www.mapd.com/docs/latest/mapd-core-guide/tables/?highlight=shard

Specifically Only integers and dictionary-encoded columns can be shard keys.

This is a current constraint, but we may lift it over time



@dwayneberry - we can get around this limitation by converting date to BigInt [using EXTRACT(EPOCH …)]) and sharding the resulting dataset.

How can we cast this value back to a Timestamp/Date? Is the following the best way -
SELECT TIMESTAMPADD(SECOND, , TIMESTAMP ‘1970-01-01 00:00:00’), from limit 1 ;