> > 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
