By using AWS re:Post, you agree to the Terms of Use

Timestream Historical Data (10s of years old)



(Quite new to timestream, old hat with SQL)

I've added Millions (80M+) rows of data to a timestream table around 3 days ago. The data is historical, and goes back to the late 1800s.

When querying it with:

SELECT * FROM <database>.<table> ORDER BY time ASC LIMIT 1

The earliest record it shows 'time' is in the year 2016 (this is actually around the date of the first record inserted into the table), there are however millions of records before this date (but these were added later to the table), I've confirmed all the records exist in the table by running:

SELECT count(id) FROM <database>.<table>

Which results in a count equal to all the rows I've added (80M+)

The table is setup to hold data in memory for 6 months, and in magnetic store for 200 years. Magnetic writes are also turned on.

Why does this behaviour happen? are query results or records cached/indexed by timestream in some way, if so is there a way to clear this cache or documentation about it? Is it the normal behaviour of the magnetic store, as its such a large dataset added recently, is magnetic store still organising it?



asked 6 months ago150 views
3 Answers

Hi James, I am Victor from the Timestream PM team, we would love to learn more about your issue, would you please file a customer support ticket so we can follow up from there?



answered 6 months ago
  • Hi Victor, actually I think I worked around this original issue, my fault as I hadn't noticed an error.. But it has lead to another related problem, very similar. When adding data from pre 1970 (epoch), the data is rejected, even if we try and add a time with a minus value, for example adding the time with value:

    -2169358250000 (Thu Apr 04 1901 16:29:10)

    We're trying to add historical (and live) precious metal prices to timestream..


Hi James, unfortunately Timestream as a service only supports seconds offset from Epoch. Are you planning to do time series analysis going all the way back to late 1800s?

answered 5 months ago
  • Hi Victor, we are yes, we are using it to store historical and current prices for gold and other precious metals..


Oh ok, I get it. Even when you could use multi-measure records since it provides support for having a timestamp as a measure, by doing so you will miss on most of the benefits of indexing on the time column offered by Timestream. Sadly there is not much we can do at this time, but if you file a customer support ticket we will be able to track it and reach out to you in the future if some work around becomes available. My apologies for not being able to help you at this time and thank you for reaching out.

answered 5 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions