Ah, I see. On 3 August 2015 at 08:44, Jaime Crespo <[email protected]> wrote:
> MariaDB [(none)]> SELECT CAST(now() AS int); > +--------------------+ > | CAST(now() AS int) | > +--------------------+ > | 20150803094329 | > +--------------------+ > 1 row in set (0.00 sec) > > On Mon, Aug 3, 2015 at 9:34 AM, Golden Ring <[email protected]> > wrote: > >> Silly me, assuming that the subtraction operator would do the right >> thing. Er, any idea what the subtraction operator _does_ do? >> >> On 3 August 2015 at 05:33, Huji Lee <[email protected]> wrote: >> >>> You are seeing those gaps because the method you use to calculate >>> account age is not correct. Run this query: >>> >>> select user_id, user_registration, >>> (now() - str_to_date(user_registration, '%Y%m%d%H%i%s'))/86400 as >>> registered_time >>> from user >>> where user_id >= 25650026 >>> and user_id <= 25650027; >>> >>> And you will see that those two accounts which are created back to back, >>> one at the end of 6/30/2015 and one at the beginning of 7/1/2015 will >>> return an account age of ~2000 and ~1181 respectively (two numbers that are >>> not that close). >>> >>> Instead, run this: >>> >>> select user_id, user_registration, >>> datediff(now(), str_to_date(user_registration, '%Y%m%d%H%i%s')) as >>> acct_age >>> from user >>> where user_id >= 25650026 >>> and user_id <= 25650027; >>> >>> And you will get the account age in days (33 and 34 days in this >>> example). >>> >>> >>> On Sun, Aug 2, 2015 at 1:12 PM, Golden Ring <[email protected]> >>> wrote: >>> >>>> I'm hoping that someone here can explain some query weirdness to me. >>>> >>>> I'm trying to get a list of the n most-recently-registered users who >>>> have done page moves, and how many page moves they've done. To do this, >>>> I'm using this query: >>>> >>>> select >>>> user_id, >>>> user_name, >>>> (now() - str_to_date(user_registration, '%Y%m%d%H%i%s'))/86400 >>>> as registered_time, >>>> count(log_id) as moves >>>> from >>>> user, >>>> logging_userindex >>>> where >>>> user_id = log_user >>>> and (log_action = 'move' or log_action = 'move_redir') >>>> group by user_id >>>> order by user_id desc >>>> limit {}; >>>> >>>> For a limit of about 100 rows, this gives plausible-looking results. >>>> But increasing the limit to 1000 gives some pretty bizarre results. There >>>> are big gaps in the registered_time variable. The range of the >>>> registered_time variable is about 0 - 4,000 days, but there are no results >>>> with registered times between approx 350 - 1160 days, 1500 - 2300 days, or >>>> 2670 to 3480 days. >>>> >>>> July 2013 is about 700 days ago, but a query to find users with page >>>> moves who registered on July 15, 2013 shows that such users do exist: >>>> >>>> select count(*) from user, logging_userindex where user_id = log_user >>>> and log_action='move' and user_registration like '20130715%'; >>>> >>>> Result: 63 move actions from users registered on 20130715. >>>> >>>> What's going on here? I assume I've simply got the first query wrong >>>> in some way, but I can't see how. >>>> >>>> Note that all of the above is against the enwiki replica. >>>> >>>> Thanks for any help, >>>> GoldenRing >>>> >>>> >>>> _______________________________________________ >>>> Labs-l mailing list >>>> [email protected] >>>> https://lists.wikimedia.org/mailman/listinfo/labs-l >>>> >>>> >>> >>> _______________________________________________ >>> Labs-l mailing list >>> [email protected] >>> https://lists.wikimedia.org/mailman/listinfo/labs-l >>> >>> >> >> _______________________________________________ >> Labs-l mailing list >> [email protected] >> https://lists.wikimedia.org/mailman/listinfo/labs-l >> >> > > > -- > Jaime Crespo > <http://wikimedia.org> > > _______________________________________________ > Labs-l mailing list > [email protected] > https://lists.wikimedia.org/mailman/listinfo/labs-l > >
_______________________________________________ Labs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/labs-l
