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(a)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(a)quelltextlich.at
4293 Gutau, Austria Phone: +43 7946 / 20 5 81
Fax: +43 7946 / 20 5 81
Homepage:
http://quelltextlich.at/
---------------------------------------------------------------