如何在MongoDB中按分钟查询日期时间?

huangapple go评论84阅读模式
英文:

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

问题

我正在尝试存储每分钟的股票价格,这样我就可以根据每分钟的日期时间轻松返回结果,并存储历史数据,以便可以查询最近24小时、最近30天等(如果这种方法不正确,请告诉我)。

例如,如果我使用fmt.Println("time now: ", time.Now())检查当前时间,我会得到以下日期时间:2022-01-29 11:47:02.398118591 +0000 UTC m=+499755.770119738

所以我想要的是只获取到分钟级别的时间,这样我就可以按分钟存储。
所以我想使用这个日期时间:2022-01-29 11:47:00 +0000 UTC
我希望使用UTC时间,这样我可以坚持使用这个通用时区来存储和检索数据。

每一行将是一个包含多个股票价格数据的列表。
我是否需要_id字段?我不确定,所以只是寻求最佳实践的帮助。

数据库名称:"stock-price-db"
集合名称:"stock-price"

想象一下,只是举个例子

[
    {
        "_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"
                }
            ]
    }
]

首先,这种方式存储数据在MongoDB中是否正确?然后,我应该如何构建模型以便按分钟间隔存储数据,以便可以按分钟间隔查询数据?

英文:

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?

答案1

得分: 2

你的设计有几个缺点:

  1. 不要使用动态键 - 这样会导致使用额外的聚合管道。
  2. 将日期存储在静态键字段中,例如 time:ISODate()。
  3. 最好存储所有可用的时间单位,直到毫秒级,这将有助于处理未来的需求变化。
  4. 如果股票变动过多,这个设计就不具备可扩展性。
  5. 如果你想要查找某只股票的历史数据,提供的设计可能会有性能问题。
  6. 你可能会在分片方面遇到问题。

其他的替代方案:

  1. 并不是所有的用例都可以通过一个设计来解决。
  2. 如果这个用例纯粹是用于时间序列,我建议你使用时间序列设计/时间序列数据库,例如 InfluxDB、TSDB。
  3. 如果你需要涵盖所有的用例,可以进行规范化并使用 GQL。
英文:

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.

huangapple
  • 本文由 发表于 2022年1月29日 20:30:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/70905379.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定