CODEWITHSUNDEEP
mongodbgomongo-go
uberrebu
uberrebu

Reputation: 4349

How Do I Structure MongoDB To Query By Date Time Per Minute?

I am trying to store the price of stocks price per minute, so I can easily return results based on the minute date time per minute interval and store historical data, so i can query like last 24 hours, last 30 days etc (please also let me know if this is wrong approach)

for example if i check current time with fmt.Println("time now: ", time.Now()) i get the following date time 2022-01-29 11:47:02.398118591 +0000 UTC m=+499755.770119738

so what i want is to only get up to minute level, so i can store per minute so i will liek to use this date time 2022-01-29 11:47:00 +0000 UTC I will like to UTC, so i can stick to that universal time zone to store and retrive data

Each row will be a list of multiple stock price data Do i need to have the _id field? Am not sure, so just looking for best practice as help.

database name: "stock-price-db"

collection name: "stock-price"

Thinking of something like this, just for example

[
    {
        "_id" : ObjectId("5458b6ee09d76eb7326df3a4"),
        "2022-01-29 11:48:00 +0000 UTC":
            [
                {
                    "stock": "TSLA",
                    "price": "859.83",
                    "marketcap": "8938289305",

                }, 
                {
                    "stock": "AAPL",
                    "price": "175.50",
                    "marketcap": "3648289305",

                },  
            ]
    },
    {
        "_id" : ObjectId("5458b6ee09d76eb7326df3a4"),
        "2022-01-29 11:47:00 +0000 UTC":
            [
                {
                    "stock": "TSLA",
                    "price": "855.50",
                    "marketcap": "8848289305",

                }, 
                {
                    "stock": "AAPL",
                    "price": "172.96",
                    "marketcap": "3638289305",

                }, 
            ]
    },
]

First, is this the right way to do store this type of data in mongodb and how do I structure the model to store the data this way so I can store the data per minute interval, so I can query per minute interval?

Upvotes: 0

Views: 637

Answers (1)

Gibbs
Gibbs

Reputation: 22974

There are few drawbacks in your design.

  1. Do not use dynamic keys - you will end up using few extra aggregation pipelines.
  2. Store the date in a static-key field i.e time:ISODate()
  3. Better store all the available time units, till milliseconds, it will be helpful to handle the future requirement changes
  4. If there are too many stocks changes, it is not a scalable design.
  5. If you want to find out historical data for a stock, provided design may have performance issues.
  6. You will end up with issues in sharding.

What other alternatives:

  1. Not all the use-cases can be solved by one design.
  2. If this use case is purely for time series use case, I would recommend you to use a time series design/ time series database i.e influx, tsdb.
  3. If you need to cover all the use-cases, normalise and use GQL.

Upvotes: 2

Related Questions