I am using an HikVision Dual Lens People Counting Camera that tracks the amount of people that enter/exit a location by the hour. The data that is exported from the camera is automatically put into an excel file with the format being as follows:
| Time Entered | People Entered |
|---|---|
| 00:00:00.000 | 43 |
| 01:00:00.000 | 87 |
| 02:00:00.000 | 62 |
The file is then uploaded to my website and put into the database under the same format. However, it isn't good enough since I want to be able to query the data based on a specific day, for example how many people entered on every Monday, how many people entered from 2/10/2022 to 2/19/2022, etc. I have been trying to figure out a way for my table to have the date ALONG with the breakdown by hour, so for example:
2022-02-19 00:00:00
2022-02-19 01:00:00
2022-02-19 02:00:00
2022-02-19 03:00:00
My issue is, the software for the camera builds the excel sheet and conforms to the table above, and has no spot to include the date. I was wondering how I could get the date and the hourly breakdown of people entered all into one database so that I can query anything I need.
Sorry if this is a little incomprehensible, it's hard explaining my exact issue. I can provide updates if need be! Thank you so much for any and all help!!
CodePudding user response：
If you cannot act on the system that generates the records in your database, a good option could be to add a date field to your table in order to get the day when the records were inserted.
ALTER TABLE mytable
ADD day DATETIME NOT NULL DEFAULT NOW();