Title: Historical Data in MySql
Tags: mysql
Question: Good day, I have a table of electrical test results, 3 - 6 - and 12 Month intervals. The client has decided he would like to keep trends on some of the data like current leakage over 5 yrs (drop down box or similar)to see if items are deteriorating. Is it possible to have 5yrs of data in the same field or do I need to create historic tables with a trigger? Thanks G Styles

Comment: I have a similar project where I use a stored procedure that returns aggregated data with min, max and average values for a given time interval, e.g. 1 hour or 2 days etc. By doing so I don't need another table.

Comment: with field you actually mean field or table ?


Here is another answer: 5 years of data does not mean much. What is important is the size of data, which will vary depending on what data you save for each interval.

My suggestion is to keep the data as is in the table, and if you have performance problems because of the size of data, take a look at MySQL Partitioning


Here is another answer: ```
  Is it possible to have 5yrs of data in the same field
```

Do you mean in the same table?  Then yes, of course.  For example, you could modify your data access layer to always perform inserts and never updates, and to always select the most recent record as the "current" record.  (So you'd need time stamps on the records to order them, of course.  Maybe even a view to make the selection easier.)  This would (should) be entirely transparent to the rest of the code.  Just remember that the table would be perpetually growing, so performance over time would be a consideration to make, depending on how often this data actually "changes" and how many records are being written.

```
  do I need to create historic tables with a trigger
```

A history or audit table is another perfectly valid approach.  Especially if performance over time is a potential concern, because the table can be indexed/optimized differently to accommodate more writes than reads, for example.  A trigger can certainly be used to maintain this, as could another simple change to the data access layer that's transparent to the rest of the code.  Either approach is fine, it's mostly a matter of how you organize and maintain your code and which one would be more obvious when supporting the code at a later time.

Comment for this answer: Thank you to all that have answered. I didn't mean the same table I meant the same field,ideally with a drop box or such so that the record would have ex.  0.11, 0.11,0.12, 0.13 etc. I suspect that this is not possible, I am not a MySql expert by any means.The Db is on a shared web server so I do not have access to some of the functions. Thanks G Styles
Comment for this answer: I use Fabrik to access the db in the website and under certain circumstances in the form view of the data drop boxes,radio buttons,check boxes etc are available to me. I take your point that data should be unique and will use an historic table for the changes I spoke about. No doubt I shall ask for help later on with the trigger. Thanks to all,G Styles.
Comment for this answer: @user1731603: It doesn't make any sense to store data "in the same field."  Any given field in any given record contains one value.  You can't store "records" in "fields".  And I'm not sure what "a drop box" has to do with MySQL.  MySQL is just a database for storing data, it's not a UI.
Comment for this answer: @user1731603: If you're going to be relying on a specific framework to manage the database for you then that's going to be *very* useful information in future questions :)  By itself, MySQL is perfectly capable of storing data in tables.  What some framework is capable of, that's another story...

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:01/19/15', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<mysql>', 'Title': 'Historical Data in MySql', 'ViewCount': 306, 'id': 28025019, 'user_AboutMe': None, 'user_AccountId': 1919810.0, 'user_DisplayName': 'user1731603', 'user_Reputation': 31.0, 'user_Views': 6.0, 'user_id': 1731603.0}}