0

I am planing to have a table to store the data based on every hour , in short I have a file with 24 hour data and I need to use this to populate a hourly bar/line graph.

Below is a sample 24hr data file

08/18/2014: 00:00 UTC,2108,259,39786,59,32,501
08/18/2014: 01:00 UTC,2461,358,50456,28,34,670
08/18/2014: 02:00 UTC,2929,501,65487,41,69,983
08/18/2014: 03:00 UTC,2903,545,68849,88,66,1004
08/18/2014: 04:00 UTC,2796,385,65972,32,59,917
08/18/2014: 05:00 UTC,2860,633,67756,16,59,976
08/18/2014: 06:00 UTC,3053,756,77406,136,74,1190
08/18/2014: 07:00 UTC,3889,757,100002,57,114,1657
08/18/2014: 08:00 UTC,4389,808,120638,75,130,1916
08/18/2014: 09:00 UTC,4243,1029,141171,108,139,2198
08/18/2014: 10:00 UTC,4132,1118,135581,104,132,2079
08/18/2014: 11:00 UTC,3964,784,120932,78,112,1903
08/18/2014: 12:00 UTC,3893,842,123153,125,101,1844
08/18/2014: 13:00 UTC,4537,939,141358,51,149,2263
08/18/2014: 14:00 UTC,4897,1067,169224,105,136,2693
08/18/2014: 15:00 UTC,5068,1276,183193,199,140,2827
08/18/2014: 16:00 UTC,4816,1028,177811,333,151,2590
08/18/2014: 17:00 UTC,4359,890,154355,257,121,2159
08/18/2014: 18:00 UTC,4204,792,147255,238,88,1988
08/18/2014: 19:00 UTC,4858,894,148851,306,119,2113
08/18/2014: 20:00 UTC,4371,835,147524,280,133,2014
08/18/2014: 21:00 UTC,4531,702,136845,308,112,1861
08/18/2014: 22:00 UTC,3685,654,107627,174,68,1475
08/18/2014: 23:00 UTC,3170,468,86662,226,70,1073

And my table structure is

CREATE TABLE hourly_data
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL,
    hour INT NOT NULL,
    f1 INT NOT NULL,
    f2 INT NOT NULL,
    f3 INT NOT NULL,
    f4 INT NOT NULL,
    f5 INT NOT NULL,
    f6 INT NOT NULL,
    inserted timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Would this be a good approach ? I will need to retrieve data between two timestamp, data on a given hour etc. I was initially thinking to have a column for each hr, so that I will just have one row for a given day instead of 24 rows.

Suggestions/improvements are welcome.

1

Your entities are based on hours, not days, so you should go with one row per hour, just like you did, not a full day in a row.

I did not understand if when you say data between two timestamps, if timestamp refers to the "inserted" column, or if it refers to the date and times on the data file. If it is the latter one, then having a datetime column for your date and hour, instead of two separate columns, would help you doing your comparisons. You would place an index on this datetime column and when you'd want to get data between two datetimes, or even one specific datetime, it would give you the results ultra-fast.

  • My query will be based on hours, eg. data between 10 and 12. So merging date and hour columns to a datetime would be a good idea. – Govind Kailas Aug 20 '14 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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