<?php
$divisor = 60;
$seconds = '00';
$statement = "SELECT s.symbol,
s.symbol64 symbol_id,
s.multiply,
s.currency,
((t.ctm - unix_timestamp(:date_begin)) DIV %s) as ctm_range,
date_format(from_unixtime((substring_index(group_concat(cast(t.ctm AS CHAR) ORDER BY t.ctm), ',', 1))), '%%Y-%%m-%%dT%%H:%%i:%s') time,
(trim(TRAILING '.' FROM(cast(trim(TRAILING '0' FROM
substring_index(group_concat(cast(t.bid AS CHAR) ORDER BY t.ctm), ',', 1)
) AS char)))) open,
(trim(TRAILING '.' FROM(cast(trim(TRAILING '0' FROM
max(t.bid)
) AS char)))) high,
(trim(TRAILING '.' FROM(cast(trim(TRAILING '0' FROM
min(t.bid)
) AS char)))) low,
(trim(TRAILING '.' FROM(cast(trim(TRAILING '0' FROM
substring_index(group_concat(cast(t.bid AS CHAR) ORDER BY t.ctm DESC), ',', 1)
) AS char)))) close,
round(avg(abs(t.bid * s.multiply - t.ask * s.multiply))) average_spread
FROM ticks t
JOIN symbols s ON s.symbol64 = t.symbol
WHERE s.symbol = :symbol
AND t.ctm BETWEEN unix_timestamp(:date_begin) AND unix_timestamp(:date_end)
GROUP BY s.symbol, ctm_range
ORDER BY t.ctm";
$statement = sprintf($statement, $divisor, $seconds);
echo $statement;