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