LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 11-25-2003, 04:59 AM   #1
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Rep: Reputation: 30
SQL question - total bytes per month


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
 
Old 11-25-2003, 11:59 AM   #2
mfeat
Member
 
Registered: Aug 2003
Location: Akron, OH
Distribution: Fedora Core 3
Posts: 185

Rep: Reputation: 30
Why not subtract the 'totalbytes' value for the last day of the previous month from the current totalbytes value?
 
Old 11-25-2003, 12:19 PM   #3
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
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.
 
Old 11-25-2003, 04:01 PM   #4
mfeat
Member
 
Registered: Aug 2003
Location: Akron, OH
Distribution: Fedora Core 3
Posts: 185

Rep: Reputation: 30
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.
 
Old 11-26-2003, 03:19 AM   #5
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
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
 
Old 11-26-2003, 10:41 AM   #6
mfeat
Member
 
Registered: Aug 2003
Location: Akron, OH
Distribution: Fedora Core 3
Posts: 185

Rep: Reputation: 30
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
 
Old 01-06-2004, 07:38 AM   #7
ganninu
Member
 
Registered: Jul 2003
Distribution: RH 7.3/8.0/9.0, Debian Stable 3.0, FreeBSD 5.2, Solaris 8/9/10,HP-UX
Posts: 340

Original Poster
Rep: Reputation: 30
by the way, that function helped me a lot. Thanks!
 
Old 01-06-2004, 11:46 AM   #8
vasudevadas
Member
 
Registered: Jul 2003
Location: Bedford, UK
Distribution: Slackware 11.0, LFS 6.1
Posts: 519

Rep: Reputation: 30
Personally I would do:

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


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
transform month number to month name in php ALInux Programming 1 11-09-2005 11:45 AM
Multicount in PHP , displays visits on current day, week, month, year and total visit xbaez Programming 1 04-24-2005 03:50 AM
Tracking Total Bandwidth Usage for Month Chibo *BSD 3 08-08-2004 02:56 AM
Starting day of month, month length chrisk5527 Programming 2 03-03-2004 05:03 PM
bits and bytes and files basics question Bert Linux - Software 2 01-16-2003 09:50 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 07:23 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration