How to display data in daily weekly monthly basis?-CodePudding

I am calling to an api and storing some data on postgresql database. The data I am storing looks like :

[
        {
            "uuid": "123",
            "prompt_tokens": 208,
            "completion_tokens": 53,
            "total_tokens": 261,
            "created": "2022-12-20T15:45:40.730Z"
        },
        {
            "uuid": "123",
            "prompt_tokens": 150,
            "completion_tokens": 177,
            "total_tokens": 327,
            "created": "2022-12-20T15:46:35.199Z"
        },
        {
            "uuid": "123",
            "prompt_tokens": 150,
            "completion_tokens": 177,
            "total_tokens": 327,
            "created": "2022-12-20T15:53:46.967Z"
        },
        {
            "uuid": "123",
            "prompt_tokens": 150,
            "completion_tokens": 176,
            "total_tokens": 326,
            "created": "2022-12-21T15:58:36.083Z"
        }
    ]

As I have multiple data for same dates, I want to display them as they are. I did that already but for weekly/monthly I have to integrate the same date data. I am using chartjs to display data. Is there a way to automatically display them in weekly/monthly basis? Or do I have to change my schema? Any suggestion would be appreciated.

CodePudding user response：

The following SQL statement groups entries by day/month/year and computes the sum of the token numbers per day/month/year group:

select sum(prompt_tokens) as sum_prompt_tokens,
       sum(completion_tokens) as sum_completion_tokens,
       sum(total_tokens) as sum_total_tokens,
       extract(day from timestamp created) as day,
       extract(month from timestamp created) as month,
       extract(year from timestamp created) as year
  from "<your table>"
 group by 
       extract(day from timestamp created),
       extract(month from timestamp created),
       extract(year from timestamp created)

If sequelize does not allow the extract expressions, you can alternatively use a data model where date and time are already separated:

{
  "uuid": "123",
  "prompt_tokens": 208,
  "completion_tokens": 53,
  "total_tokens": 261,
  "createdDate": "2022-12-20",
  "createdTime": "15:45:40.730"
}

Then the SQL statement becomes

select sum(prompt_tokens) as sum_prompt_tokens,
       sum(completion_tokens) as sum_completion_tokens,
       sum(total_tokens) as sum_total_tokens,
       createdDate
  from "<your table>"
 group by createdDate