Pt-query-digest date ranges do not work as expected
Preserve the correct sequence

I finally figured out the problem. The way how I extract all the gzipped log files and add them to a consolidated log file did not preserve the correct sequence and therefore pt-query-digest did not find the queries in the order it was expecting them.

The manual even says:


  Events are assumed to be in chronological: older events at the beginning of the log and newer events at the end of the log


Solution

The solution is to process the logrotate files in reverse order in order to end up with a logfile that has the oldest logs first and the newest ones at the end of the file.

ls mysql-slow.log* |sort -nr -t . -k 3,3 | xargs zcat -f &gt; MySQL-All-Slowlogs.log


The command above does the following things: 


ls: list all files starting with the file name mysql-slow.log.*
sort -nr -t: sort based on a numeric literal (otherwise the sequence number of the file will be interpreted as string alphabetically, sorting 111 before 2. -r reverses the order. -t uses "." as field seperator. k specifies the key to sort on.
xargs: For each line cat the zipped or unzipped slow log file to the culmulative file.


Then you can use the following comand to obtain 

 pt-query-digest --since '2017-04-06 12:00:00' --until '2017-04-09 12:00:00'  MySQL-All-Slowlogs.log  &gt; output.txt