выберите * из таблицы где дата и время в месяце (без коэффициента распада)
У меня есть набор строк с меткой времени (использующий тип данных 'даты и времени')
Я хочу выбрать все строки, которые имеют метку времени, которая является в течение конкретного месяца.
Столбец индексируется так, я не могу сделать МЕСЯЦА (метка времени) = 3, потому что это сделает этот индекс неприменимым.
Если у меня есть переменные года и месяца (в жемчуге), есть ли ужасающий бит SQL, как который я могу использовать:
timestamp BETWEEN DATE($year, $month, 0) AND DATE($year, $month, 31);
Но более хороший, и на самом деле работает?
5 ответов
Я бы согласился с предложенной вами идеей; возможно, с небольшой разницей:
select *
from your_table
where date_field >= '2010-01-01'
and date_field < '2010-02-01'
(Конечно, вы можете правильно использовать $ year и $ month )
Обратите внимание на <'2010-02 -01 ' часть: вам, возможно, придется учесть это, если у вас есть даты, которые включают время.
Например, если у вас есть строка с датой вроде '2010-01-31 12:53:12' , вы, вероятно, захотите, чтобы эта строка была выделена - и, по умолчанию, '2010-01-31' означает '2010-01-31 00:00:00' .
Возможно, это не выглядит «приятно» для глаза; но это сработает; и использовать индекс ... Это решение, которое я обычно использую, когда у меня возникают такие проблемы.
Если вам нужен один и тот же месяц каждого года, то имеющийся у вас индекс вам не поможет, и никакие хитрости синтаксиса SQL вам не помогут
С другой стороны, если вам нужен месяц определенного года, то любой запрос с диапазоном дат должен это сделать
. Это по существу , ответьте , но избегая необходимости явно знать, какой будет следующий год / месяц (так что вам не нужно увеличивать год и обертывать $ месяц , когда $ месяц = = 12 ):
my $sth = $mysql_dbh->prepare(<<__EOSQL);
SELECT ...
FROM tbl
WHERE ts >= ? AND ts < (? + INTERVAL 1 MONTH)
__EOSQL
my $yyyymm = $year . '-' . sprintf('%02d', $month);
$sth->execute($yyyymm, $yyyymm);
Для бонусных очков бегства вы также можете сделать это:
... WHERE ts BETWEEN ? AND (? + INTERVAL 1 MONTH - INTERVAL 1 SECOND)
То, что - ИНТЕРВАЛ 1 СЕКУНДА преобразует верхнюю границу с ДАТЫ в набор типов ДАТАВРЕМЯ / ВРЕМЯ / ВРЕМЯ до последней секунды дня, что, как указал Паскаль, является тем, что вы хотите на верхней границе.
Другой альтернативой является добавление дополнительного столбца в таблицу, в которой хранится предварительно вычисленный месяц. Это будет простой столбец типа int, и его легко проиндексировать. Если вы не имеете дело с kajillion строк, дополнительное пространство для беззнакового крошечного int ничтожно (один байт + накладные расходы db на строку).
Для синхронизации со столбцом отметок времени потребовалось бы немного дополнительной работы, но для этого нужны триггеры.
Как насчет WHERE MONTH(`date`) = '$month' AND YEAR(`date`) = '$year'