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 < christian@quelltextlich.at> 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:research@dbstore1002.eqiad.wmnet [(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 jkatz@wikimedia.org 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: christian@quelltextlich.at 4293 Gutau, Austria Phone: +43 7946 / 20 5 81 Fax: +43 7946 / 20 5 81 Homepage: http://quelltextlich.at/
Analytics mailing list Analytics@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/analytics