Time series with epoc timestamp

Help
1

Hi,
I have a customer who has a table that stores trades from digital currency. The traded date is stored as an epoc. From documentation time series needs a date / timestamp in order to use time_bucket. Table is very large (25B rows)

CREATE TABLE tablename (
...
  TokenId int NOT NULL,
  Price decimal(20,10) NOT NULL,
... more fields
  TStampTraded decimal(25,10) NOT NULL,
.... more fields
 )

Solution 1
Keep table name as is and use another table with TStampTraded series timestamp and only needed columns. This will require data to be copied over.

Solution 2
Modify existing table and add a persistent computed column TStampTraded series timestamp

ALTER TABLE tablename ADD COLUMN DateTradedTS AS from_unix(TStampTraded) PERSISTED series timestamp ;

I’m not sure if solution 2 is possible. I still think Solution 1 provides better performance.

Thoughts?

2

Solution 2 is not currently possible. You can’t alter a table to add a persisted computed column that is a SERIES TIMESTAMP – the syntax doesn’t allow it.

So you’ll have to copy the data over. Or, add a regular column that is a series timestamp, then update that column in place, in small batches, in a loop, until you’re done. That might do a lot of logging and be slower than just creating a new table though. Creating a new table and dropping the old one seems safest.

You can always keep naming the appropriate time column in your queries as an argument to first/last/time_bucket. But I guess you’re trying to get away from that.

3

Hi ,
Is it possible to create a table that already has a persisted computed column with time series?
Or it’s just not supported?
I cannot replace epoc with time series. Have to have both, at least EPOC in the first table.