SQL question

ganninu · 11-25-2003, 04:59 AM

I have a software which monitors the number of bytes downloaded/uploaded. The value is updated every 60 seconds so that it can be graphed. A typical table would be:

day month year time totalbytes bytes
24 11 2003 10.00 20 20
24 11 2003 10.01 20 0
24 11 2003 10.02 30 10
.
.
.
31 11 2003 01.00 340 15
.
.
01 12 2003 03.45 500 25
.
.

,where 'totalbytes' depicts the total number of bytes since the very first time the software was used, and 'bytes' is the number of bytes downloaded for that monitoring period (of 60 seconds).

My aim is that everytime i log in, i know the total value of bytes i've downloaded during that month - I'm trying to do it in two ways:

(1) I subtract the 'totalbytes' value at the 1st day of the month from the current 'totalbytes' value. This is ok but has a big downside that it excludes the downloaded value of the first day (arithmetic-wise)

or

(2) I select all the 'bytes' values and add them up for that month. This works fine. But this incurs more load since I have to add thousands of values (one value for every minute, thus, for one month of 31 days, I have to add 44,640 values, which I'm trying to avoid!!)

My question is whether there is some SQL syntax which i'm missing, which could be used to tackle my somehow-looking-simple-problem in an efficient manner - i.e. knowing the totalbytes (which is an always cumulative value) since the first time i used the program, how can i get the number of bytes downloaded from the 1st day of that month till the date of making the query?

thanks in advance - ganninu

mfeat · 11-25-2003, 11:59 AM

Why not subtract the 'totalbytes' value for the last day of the previous month from the current totalbytes value?

ganninu · 11-25-2003, 12:19 PM

the only way i had to resolve it was by summing the column for one particular month. I couldn't do it the way mfeat proposed, because, as i have explained, the month's length is not always the same (varying from 28 to 31 days). So effectivly, i had to perform some 40,000 additions.

mfeat · 11-25-2003, 04:01 PM

If the sql server you are using does not have a function to get the the last day of the month you could use the decode function (assuming it has one) as in decode(month,1,31,2,28,3,31...12,31), of course leap years are a problem.

I don't know how you're getting the totalbytes for a given day, are you using a max function on the time to get the latest record for a specified day? Show the sql code that you are using, that would help a lot.

ganninu · 11-26-2003, 03:19 AM

the totalbytes for a given day and the cumulative totalbytes are generated by means of a metering software which is niterfaced to the mysql server. I don't know, and have no idea how to find out, whether mysql has the ability to get the last day of the month - if it has that ability, it would save a lot of hassle

mfeat · 11-26-2003, 10:41 AM

No need to know about the interface that loads the data, what I was asking for is the SQL code you are using to generate the report showing the time used for the month.

The following link shows documentation for the last_day function for MySQL:

http://www.mysql.de/doc/en/Date_and_time_functions.html

LAST_DAY(date)
Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

mysql> SELECT LAST_DAY('2003-02-05'), LAST_DAY('2004-02-05');
-> '2003-02-28', '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
-> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
-> NULL

ganninu · 01-06-2004, 07:38 AM

by the way, that function helped me a lot. Thanks!

vasudevadas · 01-06-2004, 11:46 AM

Personally I would do:

Code:

SELECT MAX(totalbytes) - MIN(totalbytes)
FROM whatever_your_table_name_is
WHERE year = whatever_year
AND month = whatever_month