ScalarDB是否支持timestamp类型？存储方案及表结构有效性咨询

Great questions about ScalarDB! Let's break this down clearly:

ScalarDB doesn't natively include a timestamp data type in its core type system. While it adapts to the type systems of underlying storage engines (like Cassandra, DynamoDB, etc.), there's no unified timestamp type that works consistently across all supported backends.

For storing timestamps, here are your best options ranked by suitability:

• BigInt (Recommended)：Store the timestamp as a numeric value (e.g., milliseconds since the Unix epoch, or microseconds for higher precision). This is hands down the best choice for most use cases—numeric comparisons (like checking if a time falls within a range) are lightning fast, sorting works exactly as you'd expect chronologically, and it's space-efficient compared to string formats.
• Text (Alternative)：If human-readability is a top priority, use an ISO 8601 formatted string (e.g., 2024-05-20T14:30:00.000Z). This format sorts lexicographically in the correct chronological order, so range queries still work—but it uses more storage and is slightly slower than numeric types for large datasets.
• Blob (Not Recommended)：Avoid using blob for timestamps. Blobs are intended for arbitrary binary data, and you'll lose the ability to easily compare or sort timestamp values without extra processing, making this a poor choice for time-related queries.

The statement you provided won't work with ScalarDB because it doesn't recognize the timestamp data type. You'll need to replace expiration_time timestamp with one of the supported types we covered above.

Here's a corrected version using the recommended BigInt type:

CREATE TABLE mytable (
  token_id text PRIMARY KEY,
  email text,
  expiration_time bigint, -- Stores epoch milliseconds
  -- Add your other columns here...
);

If you prefer the readable text format, you could use this instead:

CREATE TABLE mytable (
  token_id text PRIMARY KEY,
  email text,
  expiration_time text, -- Stores ISO 8601 formatted timestamp string
  -- Add your other columns here...
);

内容的提问来源于stack exchange，提问作者Manu Chadha