Ahh. Rule 5! On 6 January 2015 at 17:07, Dan Andreescu <[email protected]> wrote: >> Huh; interesting! I remember running into a lot of problems with the >> revision table due to conversion. > > > You're not imagining things, Oliver. The explanation is too complicated to > mention here, and I always forget it. Whether you are ok quoting dates or > not depends on what indices are set on those fields and this varies from > labs to the private slaves. In short, you're better off using quoted dates > in all cases, just to keep it simple. > >> >> >> On 3 January 2015 at 08:05, Christian Aistleitner >> <[email protected]> wrote: >>> >>> Hi, >>> >>> On Mon, Dec 29, 2014 at 08:26:25PM -0500, Oliver Keyes wrote: >>> > MediaWiki timestamps are stored as character strings, not numeric >>> > values, >>> > because it allows for convenient alpha-sorting. You need to be using >>> > timestamp BETWEEN '2014110100000' AND..., with quotes, rather than >>> > treating >>> > them as numeric values. >>> >>> EventLogging stores timestamps as characters. True. But MySQL is good >>> at automatic type conversion. So one can use numeric values just fine: >>> >>> mysql:[email protected] [(none)]> SELECT timestamp FROM >>> log.MobileWebClickTracking_5929948 WHERE timestamp BETWEEN 20140101000000 >>> and 20140101000000 LIMIT 1; >>> +----------------+ >>> | timestamp | >>> +----------------+ >>> | 20140101000000 | >>> +----------------+ >>> 1 row in set (0.02 sec) >>> >>> So no real requirement to quote the values in the BETWEEN in this >>> case. But quoting them makes a few things easier (like allowing to >>> truncate values). >>> >>> >>> >>> The real issue with the original query in >>> >>> > On 29 December 2014 at 20:21, Jon Katz <[email protected]> wrote: >>> > > mysql [...] -e "Insert into staging.jkatz_clicktracking1 Select * >>> > > from log.MobileWebClickTracking_5929948 WHERE ('timestamp' between >>> > > 20141101000000 and 20141130000000) and wiki like 'enwiki';" >>> >>> is elsewhere in the WHERE part. The first condition is >>> >>> 'timestamp' between 20141101000000 and 20141130000000 >>> >>> which (due to the quotes) checks if the literal string “timestamp” >>> (and not the value of the column called timestamp) is between the >>> given values. >>> >>> This BETWEEN condition on the literal string “timestamp” is never >>> met. Hence, the SELECT gave the empty result set. >>> >>> Have fun, >>> Christian >>> >>> >>> >>> -- >>> ---- quelltextlich e.U. ---- \\ ---- Christian Aistleitner ---- >>> Companies' registry: 360296y in Linz >>> Christian Aistleitner >>> Kefermarkterstrasze 6a/3 Email: [email protected] >>> 4293 Gutau, Austria Phone: +43 7946 / 20 5 81 >>> Fax: +43 7946 / 20 5 81 >>> Homepage: http://quelltextlich.at/ >>> --------------------------------------------------------------- >>> >>> _______________________________________________ >>> Analytics mailing list >>> [email protected] >>> https://lists.wikimedia.org/mailman/listinfo/analytics >>> >> >> >> >> -- >> Oliver Keyes >> Research Analyst >> Wikimedia Foundation >> >> _______________________________________________ >> Analytics mailing list >> [email protected] >> https://lists.wikimedia.org/mailman/listinfo/analytics >> > > > _______________________________________________ > Analytics mailing list > [email protected] > https://lists.wikimedia.org/mailman/listinfo/analytics >
-- Oliver Keyes Research Analyst Wikimedia Foundation _______________________________________________ Analytics mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/analytics
