Web lists-archives.com

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