Converting dates to a dimension


#1

I have stock market bid/offer data. I’ve read that dimensions can only be 64 ints. So my question is about how to best convert time to 64 ints. The finance examples I have seen on github use ms past the start of the day. Since the examples are only for one day, the issue of year-month-day is elided. On the forum, I’ve seen the recommendation that the y-m-d be converted to the number of days from some well known start date. My question is whether SciDB has a preferred conversion that it uses with its own temporal data types so that any conversion to a SciDB data type in the future would be easier. Dates can be tricky with the edge cases.

Also, would breaking year-month-day into 3 dimensions be efficient for manipulating stock data in SciDB?

Thanks.


#2

Hello!

One form we’re starting to see a lot is an integer-based date that simply concatenates YYYYMMDD. For example:
date =

20150629
20150630
20150701

And so you can have a schema with dimensions
[equity_id=0:…, date =0:,1,0, tm =0:*,…,0, …]

where the chunk size of 1 along date lets you quickly search by date. tm may be in your favorite units based on your feeds - millis, 10s of micros, etc. A fourth synthetic dimension may be used to collect all events that happen at the same “tm” mark. Again depends on the units you’re using. A few customers are using a variety of that schema.

You can also fairly easily define functions that convert a date string like “2015/06/14” to a number 20150614 to some “number of days since” value i.e. 521365. Some UDF examples are provided on our github labs page:
github.com/Paradigm4/superfunpa … unpack.cpp
github.com/Paradigm4/load_tools … ctions.cpp

Or, of course, contact P4 through the official sales channel for more help with your specific needs: paradigm4.com/try_scidb/#SciDB_Demonstration