Re: mysql query for current date accounting returns NULL
On 3/26/2016 4:36 PM, shawn l.green wrote:
On 3/25/2016 6:39 AM, JAHANZAIB SYED wrote:
I have Freeradius 2.x with MySQL 5.5 in Ubuntu.
I want to query user quota for current date. I am using following code
SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM
radacct where (acctstarttime between DATE_FORMAT(NOW(),'%Y-%m-%d')
AND NOW() AND acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-%d')
AND NOW()) AND radacct.username='%{User-Name}'
It works fine if there is acctstoptime value in table. but if user
have not disconnected yet (and have no previous session for today) it
returns NULL.
So how can i can get the value even if user acttstoptime is null?
Try this...(using an earlier suggestion to the thread)
SELECT
SUM(acctinputoctets + acctoutputoctets) AS Total
FROM radacct
WHERE
radacct.username='%{User-Name}'
AND acctstarttime BETWEEN CURDATE() AND NOW()
AND (
acctstoptime <= NOW()
OR acctstoptime IS NULL
)
But in reality, can you have an acctstarttime that is >= NOW()? If not,
then you can also simplify that term to just
oops! one too many AND's
AND AND acctstarttime >= CURDATE()
I meant to write
AND acctstarttime >= CURDATE()
and lose the BETWEEN comparison.
--
Shawn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: mysql query for current date accounting returns NULL
On 3/25/2016 6:39 AM, JAHANZAIB SYED wrote:
I have Freeradius 2.x with MySQL 5.5 in Ubuntu.
I want to query user quota for current date. I am using following code
SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM radacct where
(acctstarttime between DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND
acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND
radacct.username='%{User-Name}'
It works fine if there is acctstoptime value in table. but if user have not
disconnected yet (and have no previous session for today) it returns NULL.
So how can i can get the value even if user acttstoptime is null?
Try this...(using an earlier suggestion to the thread)
SELECT
SUM(acctinputoctets + acctoutputoctets) AS Total
FROM radacct
WHERE
radacct.username='%{User-Name}'
AND acctstarttime BETWEEN CURDATE() AND NOW()
AND (
acctstoptime <= NOW()
OR acctstoptime IS NULL
)
But in reality, can you have an acctstarttime that is >= NOW()? If not,
then you can also simplify that term to just
AND AND acctstarttime >= CURDATE()
and lose the BETWEEN comparison.
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: mysql query for current date accounting returns NULL
2016/03/25 06:39 ... JAHANZAIB SYED:
I want to query user quota for current date. I am using following code
SELECT SUM(acctinputoctets)+SUM(acctoutputoctets) AS Total FROM radacct where
(acctstarttime between DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND
acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND
username='%{User-Name}'
It works fine if there is acctstoptime value in table. but if user have not
disconnected yet (and have no previous session for today) it returns NULL.
That expression has problems. Not only it works only when both
acctstarttime and acctstoptime are good, but only if they are on the
same day, today.
> So how can i can get the value even if user acctstoptime is null?
Really, it is best to omit the test on "acctstoptime".
I don't like the form of the test, either. If "acctstarttime" is of
DATETIME (or TIMESTAMP) type I like this better:
acctstarttime BETWEEN CURDATE() AND NOW()
otherwise
CAST(acctstarttime AS DATETIME) BETWEEN CURDATE() AND NOW()
You are also not GROUPing BY anything, which, strictly speakind, with
SUM is bad SQL, but, of course, it works because only one value of
"username" is sought.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
