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