Re: mysql query for current date accounting returns NULL

2016-03-26 Thread shawn l.green 



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

2016-03-26 Thread shawn l.green 



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