Сервер Oracle table предлагает встроенную функцию TRUNC(timestamp,'DY'). Эта функция преобразует любую временную метку в полночь в предыдущее воскресенье. Какой лучший способ сделать это в MySQL?
Oracle также предлагает TRUNC(timestamp,'MM') преобразовать временную метку в полночь в первый день месяца, в котором она происходит. В MySQL это просто:
TIMESTAMP(DATE_FORMAT(timestamp, '%Y-%m-01'))
Но этот трюк DATE_FORMAT не будет работать неделями. Я знаю о функции WEEK(timestamp), но мне действительно не нужен номер недели в течение года; этот материал предназначен для многолетней работы.
Разобрался... немного громоздко, но вот оно.
FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))
И, если ваши бизнес-правила говорят, что ваши недели начинаются по понедельникам, измените -1 на -2.
редактировать
Прошли годы, и я наконец-то нашел время написать это. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/
Вы можете использовать как YEAR(timestamp) и WEEK(timestamp), и использовать оба этих выражения в предложении SELECT и GROUP BY.
Не слишком элегантно, но функционально...
И, конечно, вы можете объединить эти две части даты в одном выражении, то есть что-то вроде
SELECT CONCAT(YEAR(timestamp), '/', WEEK(timestamp)), etc...
FROM ...
WHERE ..
GROUP BY CONCAT(YEAR(timestamp), '/', WEEK(timestamp))
Редактировать: Как указывает Мартин , вы также можете использовать YEARWEEK(mysqldatefield), хотя ее вывод не так YEARWEEK(mysqldatefield) глаз, как более длинная формула выше.
Отредактируйте 2 [3,5 года спустя!]:
YEARWEEK(mysqldatefield) с необязательным вторым аргументом ( mode), установленным в 0 или 2, вероятно, является наилучшим способом агрегирования по целым неделям (т.е. включая недели, которые колеблются после 1 января), если это то, что нужно. Подход YEAR()/WEEK() первоначально предложенный в этом ответе, позволяет разделить агрегированные данные для таких "колеблющихся" недель на две части: одну с предыдущим годом, одну с новым годом.
Ежегодно необходимо проводить чистую срезку за счет наличия до двух неполных недель, по одной на каждом конце, часто в бухгалтерском учете и т.д., И в этом случае подход YEAR()/WEEK() лучше.
Принятый ответ выше не работал у меня, потому что он заказывал недели по алфавиту, а не по хронологическому порядку:
2012/1
2012/10
2012/11
...
2012/19
2012/2
Здесь мое решение для подсчета и группировки по неделям:
SELECT CONCAT(YEAR(date), '/', WEEK(date)) AS week_name,
YEAR(date), WEEK(date), COUNT(*)
FROM column_name
GROUP BY week_name
ORDER BY YEAR(DATE) ASC, WEEK(date) ASC
Формирует:
YEAR/WEEK YEAR WEEK COUNT
2011/51 2011 51 15
2011/52 2011 52 14
2012/1 2012 1 20
2012/2 2012 2 14
2012/3 2012 3 19
2012/4 2012 4 19
Вы можете получить сцепленный номер года и недели (200945) с помощью функции YEARWEEK(). Если я правильно понимаю вашу цель, это должно позволить вам группировать многолетние данные.
Если вам нужна фактическая отметка времени для начала недели, она менее приятная:
DATE_SUB( field, INTERVAL DAYOFWEEK( field ) - 1 DAY )
Для ежемесячного заказа вы можете рассмотреть функцию LAST_DAY() - сортировка будет в последний день месяца, но это должно быть эквивалентно к сортировке в первый день месяца... не так ли?
Просто выберите это в списке:
DATE_FORMAT($yourDate, \'%X %V\') as week
И
group_by(week);
Если вам нужна дата окончания недели, это также сработает. Это будет подсчитывать количество записей за каждую неделю. Пример. Если между (включительно) были созданы три рабочих заказа (включительно) 1/2/2010 и 1/8/2010 и 5 между (включительно) 1/9/2010 и 1/16/2010, это вернет:
3 1/8/2010
5 1/16/2010
Мне пришлось использовать дополнительную функцию DATE(), чтобы усечь мое поле datetime.
SELECT COUNT(*), DATE_ADD( DATE(wo.date_created), INTERVAL (7 - DAYOFWEEK( wo.date_created )) DAY) week_ending
FROM work_order wo
GROUP BY week_ending;