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