Huh; interesting! I remember running into a lot of problems with the revision table due to conversion.
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
