0

I am network admin at a municipality that has been tasked with creating a database to log information from our powerplant. I know I don’t have the skills to do this, but since I have some knowledge on website development (which isn’t much either) they asked if I would create it.

There are 15 columns, but I only care about 6 of them. 3 columns only use 0-99, but the other 2 columns are for temperatures. The last column is for the weather conditions e.g., cloudy, sunny, etc.

So far I have the information in the database, and can call back all the information and display it on a webpage. Not too shabby.

My problem is that I would like to show a days’ worth (24 hrs) of information only. The more data I enter into this database, the longer the webpage will be. I would like to be able to search for a specific day and display temps / power usage

Here is a small snippet of what it looks like currently. The third row was just a demonstration of data being read from the database.

Time Bid Load Real Time Avail MW MAX MW PD HR GEN HR Total Load LAG LEAD Temp FR WeatherCond
00:01:00 0 13 10 1 95 73 Cloudy
00:02:00 0 14 10 1 96 74 Cloudy
00:03:00 1 0 14 1 10 1 1 1 96 74 Cloudy

I am using MySQL and mostly writing with php.

Edit: How can I make this database searchable by date?

8
  • 2
    How do you know which dates the data correlates to?
    – J.D.
    Dec 27, 2022 at 19:04
  • 2
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Dec 27, 2022 at 19:29
  • @ J.D. Exactly. I'm not sure how to make this database searchable by date. Currently, there's just a bunch of data, but I stopped creating it because this is the newest hurdle. I'm not sure how to continue from here honestly.
    – cloudd2
    Dec 27, 2022 at 20:34
  • 1
    "I'm not sure how to make this database searchable by date." - Well unfortunately the problem is it's not possible, currently. Your table doesn't seem to store the date anywhere. Is any of the other 15 columns a date for when the data was logged?...if not, is there a way to store the date when this data gets saved to the database?
    – J.D.
    Dec 27, 2022 at 21:13
  • 1
    Hi, and welcome to dba.se! Sounds to me like you require a consultant to get you up and running. If you don't have the skillset necessary to do this, get a consultant in and learn from their implementation. Ensure that you (absolutely positively) have the source code for anything they write/install for you!
    – Vérace
    Dec 28, 2022 at 6:45

1 Answer 1

0

There will be some sort of submitting/editing button added later. Hourly employees will be entering this data in via website.

I'm assuming the day the employees enter the data is the day that's associated with the data being logged itself. So in your table you should have a column with a name like LoggedDateTime that is of the type DATETIME.

In the code behind the website, that submits the data to the database, you can set the LoggedDateTime to the current date / time when the employee presses the submit button via a native method of whatever application programming language you're using.

Then you can write a query like the following to get only the last 24 hours of data from the table:

SELECT * -- Don't actually use * here, list out only the columns you need instead 
FROM YourTable
WHERE LoggedDateTime >= DATE_SUB(NOW(), INTERVAL 1 DAY)

It's probably a good idea to have a LoggedByUser field in your table as well too. Usually it's helpful later on for debugging purposes, etc, to know who inputted the data.

3
  • 1
    Wow thank you! That's a really good idea to log who submitted data. I'm willing to change whatever I need to get this working the way I'd like it to! I appreciate your answer!
    – cloudd2
    Dec 28, 2022 at 15:13
  • @cloudd2 No problem! Not required, but feel free to upvote or accept the answer if you felt it was helpful, so others with similar questions see it verified as useful too.
    – J.D.
    Dec 28, 2022 at 15:20
  • I can't give your answer rep until I have 15 reputation. I promise I'll give you rep when I get there!
    – cloudd2
    Dec 29, 2022 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.