Ниже представлен пример, в котором используются функции даты. Приведенный запрос выбирает все записи с величиной date_col в течение последних 30 дней:
Mysql> SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
Возвращает индекс дня недели для аргумента date (0 =понедельник, 1 = вторник, ... 6 = воскресенье):
Mysql> SELECT WEEKDAY("1998-02-03 22:23:00"); -> 1 mysql> SELECT WEEKDAY("1997-11-05"); -> 2
DAYOFMONTH(date)
Возвращает порядковый номер дня месяца для аргумента date в диапазоне от 1 до 31:
Mysql> SELECT DAYOFMONTH("1998-02-03"); -> 3
Возвращает порядковый номер дня года для аргумента date в диапазоне от 1 до 366:
Mysql> SELECT DAYOFYEAR("1998-02-03"); -> 34
Возвращает порядковый номер месяца в году для аргумента date в диапазоне от 1 до 12:
Mysql> SELECT MONTH("1998-02-03"); -> 2
Возвращает название дня недели для аргумента date:
Mysql> SELECT DAYNAME("1998-02-05"); -> "Thursday"
Возвращает название месяца для аргумента date:
Mysql> SELECT MONTHNAME("1998-02-05"); -> "February"
Возвращает номер квартала года для аргумента date в диапазоне от 1 до 4:
Mysql> SELECT QUARTER("98-04-01"); -> 2
WEEK(date) , WEEK(date,first)
При наличии одного аргумента возвращает порядковый номер недели в году для date в диапазоне от 0 до 53 (да, возможно начало 53-й недели) для регионов, где воскресенье считается первым днем недели. Форма WEEK() с двумя аргументами позволяет уточнить, с какого дня начинается неделя - с воскресенья или с понедельника. Результат будет в пределах 0-53 или 1-52 .
Вот как работает второй аргумент:
Mysql> SELECT WEEK("1998-02-20"); -> 7 mysql> SELECT WEEK("1998-02-20",0); -> 7 mysql> SELECT WEEK("1998-02-20",1); -> 8 mysql> SELECT WEEK("1998-12-31",1); -> 53
Примечание: в версии 4.0 функция WEEK(#,0) была изменена с целью соответствия календарю США.
Заметьте, если неделя является последней неделей прошлого года, MySQL вернет 0 если вы не указали 2 или 3 как опциональный аргумент:
Mysql> SELECT YEAR("2000-01-01"), WEEK("2000-01-01",0); -> 2000, 0 mysql> SELECT WEEK("2000-01-01",2); -> 52
Можно считать, что MySQL должен вернуть 52 , так как данная дата и является 52-ой неделей года 1999. Мы решили возвращать 0 , так как мы хотим, чтобы функция давала "номер недели в указанном году". Это делает функцию WEEK() более надежной при использовании совместно с другими функциями, которые вычисляют части дат.
Если вам все же важно уточнить корректную неделю в году, тогда вы можете использовать 2 или 3 как опциональный аргумент или использовать YEARWEEK()
Mysql> SELECT YEARWEEK("2000-01-01"); -> 199952 mysql> SELECT MID(YEARWEEK("2000-01-01"),5,2); -> 52
Возвращает год для аргумента date в диапазоне от 1000 до 9999:
Mysql> SELECT YEAR("98-02-03"); -> 1998
YEARWEEK(date) , YEARWEEK(date,first)
Возвращает год и неделю для аргумента date . Второй аргумент в данной функции работает подобно второму аргументу в функции WEEK() . Следует учитывать, что год может отличаться от указанного в аргументе date для первой и последней недель года:
Mysql> SELECT YEARWEEK("1987-01-01"); -> 198653
Обратите внимание, что номер недели отличается от того, который возвращает функция WEEK() (0), будучи вызванной с опциональным аргументом 0 или 1 . Это потому, что WEEK() возвращает номер недели именно в указанном году.
Возвращает час для аргумента time в диапазоне от 0 до 23:
Mysql> SELECT HOUR("10:05:03"); -> 10
Возвращает количество минут для аргумента time в диапазоне от 0 до 59:
Mysql> SELECT MINUTE("98-02-03 10:05:03"); -> 5
Возвращает количество секунд для аргумента time в диапазоне от 0 до 59:
Mysql> SELECT SECOND("10:05:03"); -> 3
Добавляет N месяцев к периоду P (в формате YYMM или YYYYMM). Возвращает величину в формате YYYYMM . Следует учитывать, что аргумент периода P не является значением даты:
Mysql> SELECT PERIOD_ADD(9801,2); -> 199803
PERIOD_DIFF(P1,P2)
Возвращает количество месяцев между периодами P1 и P2 . P1 и P2 должны быть в формате YYMM или YYYYMM . Следует учитывать, что аргументы периода P1 и P2 не являются значениями даты:
Mysql> SELECT PERIOD_DIFF(9802,199703); -> 11
DATE_ADD(date,INTERVAL expr type) , DATE_SUB(date,INTERVAL expr type) , ADDDATE(date,INTERVAL expr type) , SUBDATE(date,INTERVAL expr type)
Данные функции производят арифметические действия над датами. Обе являются нововведением версии MySQL 3.22. Функции ADDDATE() и SUBDATE() - синонимы для DATE_ADD() и DATE_SUB() . В версии MySQL 3.23 вместо функций DATE_ADD() и DATE_SUB() можно использовать операторы + и - , если выражение с правой стороны представляет собой столбец типа DATE или DATETIME (см. пример ниже). Аргумент date является величиной типа DATETIME или DATE , задающей начальную дату.
Выражение expr задает величину интервала, который следует добавить к начальной дате или вычесть из начальной даты. Выражение expr представляет собой строку, которая может начинаться с - для отрицательных значений интервалов. Ключевое слово type показывает, каким образом необходимо интерпретировать данное выражение. Вспомогательная функция EXTRACT(type FROM date) возвращает интервал указанного типа (type) из значения даты. В следующей таблице показана взаимосвязь аргументов type и expr:
| Значение Type | Ожидаемый формат expr |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| MONTH | MONTHS |
| YEAR | YEARS |
| MINUTE_SECOND | "MINUTES:SECONDS" |
| HOUR_MINUTE | "HOURS:MINUTES" |
| DAY_HOUR | "DAYS HOURS" |
| YEAR_MONTH | "YEARS-MONTHS" |
| HOUR_SECOND | "HOURS:MINUTES:SECONDS" |
| DAY_MINUTE | "DAYS HOURS:MINUTES" |
| DAY_SECOND | "DAYS HOURS:MINUTES:SECONDS" |
В MySQL формат выражения expr допускает любые разделительные знаки. Разделители, представленные в данной таблице, приведены в качестве примеров. Если аргумент date является величиной типа DATE и предполагаемые вычисления включают в себя только части YEAR , MONTH , и DAY (т.е. не содержат временной части TIME), то результат представляется величиной типа DATE . В других случаях результат представляет собой величину DATETIME:
Mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; -> 1998-01-01 00:00:00 mysql> SELECT INTERVAL 1 DAY + "1997-12-31"; -> 1998-01-01 mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND; -> 1997-12-31 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND); -> 1998-01-01 00:00:00 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 DAY); -> 1998-01-01 23:59:59 mysql> SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND); -> 1998-01-01 00:01:00 mysql> SELECT DATE_SUB("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND); -> 1997-12-30 22:58:59 mysql> SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR); -> 1997-12-30 14:00:00 mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY); -> 1997-12-02
Если указанный интервал слишком короткий (т.е. не включает все части интервала, ожидаемые при заданном ключевом слове type), то MySQL предполагает, что опущены крайние слева части интервала. Например, если указан аргумент type в виде DAY_SECOND , то ожидаемое выражение expr должно иметь следующие части: дни, часы, минуты и секунды. Если в этом случае указать значение интервала в виде "1:10" , то MySQL предполагает, что опущены дни и часы, а данная величина включает только минуты и секунды. Другими словами, сочетание "1:10" DAY_SECOND интерпретируется как эквивалент "1:10" MINUTE_SECOND . Аналогичным образом в MySQL интерпретируются и значения TIME - скорее как представляющие прошедшее время, чем как время дня. Следует учитывать, что при операциях сложения или вычитания с участием величины DATE и выражения, содержащего временную часть, данная величина DATE будет автоматически конвертироваться в величину типа DATETIME:
Mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY); -> 1999-01-02 mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR); -> 1999-01-01 01:00:00
При использовании некорректных значений дат результат будет равен NULL . Если при суммировании MONTH , YEAR_MONTH или YEAR номер дня в результирующей дате превышает максимальное количество дней в новом месяце, то номер дня результирующей даты принимается равным последнему дню нового месяца:
Mysql> SELECT DATE_ADD("1998-01-30", INTERVAL 1 MONTH); -> 1998-02-28
Из предыдущего примера видно, что слово INTERVAL и ключевое слово type не являются регистро-зависимыми.
EXTRACT(type FROM date)
Типы интервалов для функции EXTRACT() используются те же, что и для функций DATE_ADD() или DATE_SUB() , но EXTRACT() производит скорее извлечение части из значения даты, чем выполнение арифметических действий.
Mysql> SELECT EXTRACT(YEAR FROM "1999-07-02"); -> 1999 mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03"); -> 199907 mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03"); -> 20102
функция возвращает номер дня для даты, указанной в аргументе date , (количество дней, прошедших с года 0):
Mysql> SELECT TO_DAYS(950501); -> 728779 mysql> SELECT TO_DAYS("1997-10-07"); -> 729669
Функция TO_DAYS() не предназначена для использования с величинами, предшествующими введению григорианского календаря (1582), поскольку не учитывает дни, утерянные при изменении календаря.
Возвращает величину DATE для заданного номера дня N:
Mysql> SELECT FROM_DAYS(729669); -> "1997-10-07"
Функция FROM_DAYS() не предназначена для использования с величинами, предшествующими введению григорианского календаря (1582), поскольку она не учитывает дни, утерянные при изменении календаря.
DATE_FORMAT(date,format)
Форматирует величину date в соответствии со строкой format . В строке format могут использоваться следующие определители:
| Определитель | Описание |
| %M | Название месяца (январь...декабрь) |
| %W | Название дня недели (воскресенье...суббота) |
| %D | День месяца с английским суффиксом (0st, 1st, 2nd, 3rd и т.д.) |
| %Y | Год, число, 4 разряда |
| %y | Год, число, 2 разряда |
| %X | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%V" |
| %x | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%v" |
| %a | Сокращенное наименование дня недели (Вс...Сб) |
| %d | День месяца, число (00..31) |
| %e | День месяца, число (0..31) |
| %m | Месяц, число (00..12) |
| %c | Месяц, число (0..12) |
| %b | Сокращенное наименование месяца (Янв...Дек) |
| %j | День года (001..366) |
| %H | Час (00..23) |
| %k | Час (0..23) |
| %h | Час (01..12) |
| %I | Час (01..12) |
| %l | Час (1..12) |
| %i | Минуты, число (00..59) |
| %r | Время, 12-часовой формат (hh:mm:ss M) |
| %T | Время, 24-часовой формат (hh:mm:ss) |
| %S | Секунды (00..59) |
| %s | Секунды (00..59) |
| %p | AM или PM |
| %w | День недели (0=воскресенье..6=суббота) |
| %U | Неделя (00..53), где воскресенье считается первым днем недели |
| %u | Неделя (00..53), где понедельник считается первым днем недели |
| %V | Неделя (01..53), где воскресенье считается первым днем недели. Используется с " %X " |
| %v | Неделя (01..53), где понедельник считается первым днем недели. Используется с " %x " |
| %% | Литерал " % ". |
Все другие символы просто копируются в результирующее выражение без интерпретации:
Mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%W %M %Y"); -> "Saturday October 1997" mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%H:%i:%s"); -> "22:23:00" mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%D %y %a %d %m %b %j"); -> "4th 97 Sat 04 10 Oct 277" mysql> SELECT DATE_FORMAT("1997-10-04 22:23:00", "%H %k %I %r %T %S %w"); -> "22 22 10 10:23:00 PM 22:23:00 00 6" mysql> SELECT DATE_FORMAT("1999-01-01", "%X %V"); -> "1998 52"
В MySQL 3.23 символ " % " должен предшествовать символам определителя формата. В более ранних версиях MySQL символ " % " необязателен.
Причина того, что промежутки для месяца и дня начинаются с нуля заключается в том, что MySQL позволяет использовать неполные даты, такие как "2004-00-00" , начиная с MySQL 3.23.
TIME_FORMAT(time,format)
Данная функция используется аналогично описанной выше функции DATE_FORMAT() , но строка format может содержать только те определители формата, которые относятся к часам, минутам и секундам. При указании других определителей будет выдана величина NULL или 0 .
CURDATE() , CURRENT_DATE
Возвращает сегодняшнюю дату как величину в формате YYYY-MM-DD или YYYYMMDD , в зависимости от того, в каком контексте используется функция - в строковом или числовом:
Mysql> SELECT CURDATE(); -> "1997-12-15" mysql> SELECT CURDATE() + 0; -> 19971215
CURTIME() , CURRENT_TIME
Возвращает текущее время как величину в формате HH:MM:SS или HHMMS , в зависимости от того, в каком контексте используется функция - в строковом или числовом:
Mysql> SELECT CURTIME(); -> "23:50:26" mysql> SELECT CURTIME() + 0; -> 235026
NOW() , SYSDATE() , CURRENT_TIMESTAMP
Возвращает текущую дату и время как величину в формате YYYY-MM-DD HH:MM:SS или YYYYMMDDHHMMSS , в зависимости от того, в каком контексте используется функция - в строковом или числовом:
Mysql> SELECT NOW(); -> "1997-12-15 23:50:26" mysql> SELECT NOW() + 0; -> 19971215235026
Заметьте, что NOW() вычисляется только единожды для каждого запроса, а именно - в начале его выполнения. Это позволяет быть уверенным в том, что множественные ссылки на NOW() в рамках одного запроса дадут одно и то же значение.
UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)
При вызове данной функции без аргумента она возвращает временную метку UNIX_TIMESTAMP (секунды с 1970-01-01 00:00:00 GMT) как беззнаковое целое число. Если функция UNIX_TIMESTAMP() вызывается с аргументом date , она возвращает величину аргумента как количество секунд с 1970-01-01 00:00:00 GMT. Аргумент date может представлять собой строку типа DATE , строку DATETIME , величину типа TIMESTAMP или число в формате YYMMDD или YYYYMMDD местного времени:
Mysql> SELECT UNIX_TIMESTAMP(); -> 882226357 mysql> SELECT UNIX_TIMESTAMP("1997-10-04 22:23:00"); -> 875996580
При использовании функции UNIX_TIMESTAMP в столбце TIMESTAMP эта функция будет возвращать величину внутренней временной метки непосредственно, без подразумеваемого преобразования строки во временную метку (``string-to-unix-timestamp""). Если заданная дата выходит за пределы допустимого диапазона, то функция UNIX_TIMESTAMP() возвратит 0 , но следует учитывать, что выполняется только базовая проверка (год 1970-2037, месяц 01-12, день 01-31). Если необходимо выполнить вычитание столбцов UNIX_TIMESTAMP() , результат можно преобразовать к целым числам со знаком. See Раздел 6.3.5, «Функции приведения типов» .
FROM_UNIXTIME(unix_timestamp)
Возвращает представление аргумента unix_timestamp как величину в формате YYYY-MM-DD HH:MM:SS или YYYYMMDDHHMMSS , в зависимости от того, в каком контексте используется функция - в строковом или числовом:
Mysql> SELECT FROM_UNIXTIME(875996580); -> "1997-10-04 22:23:00" mysql> SELECT FROM_UNIXTIME(875996580) + 0; -> 19971004222300
FROM_UNIXTIME(unix_timestamp,format)
Возвращает строковое представление аргумента unix_timestamp , отформатированное в соответствии со строкой format . Строка format может содержать те же определители, которые перечислены в описании для функции DATE_FORMAT() :
Mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), "%Y %D %M %h:%i:%s %x"); -> "1997 23rd December 03:43:30 1997"
SEC_TO_TIME(seconds)
Возвращает аргумент seconds, преобразованный в часы, минуты и секунды, как величину в формате HH:MM:SS или HHMMSS , в зависимости от того, в каком контексте используется функция - в строковом или числовом:
Mysql> SELECT SEC_TO_TIME(2378); -> "00:39:38" mysql> SELECT SEC_TO_TIME(2378) + 0; -> 3938
TIME_TO_SEC(time)
Возвращает аргумент time , преобразованный в секунды:
Mysql> SELECT TIME_TO_SEC("22:23:00"); -> 80580 mysql> SELECT TIME_TO_SEC("00:39:38"); -> 2378
Эти функции также предназначены для работы с календарными типами данных. Рассмотрим их подробнее.
- DATE_FORMAT(date, format)
форматирует дату date в соответствии с выбранным форматом formate.
Эта функция очень часто используется. Например, в MySQL дата имеет формат представления YYYY-MM-DD (год-месяц-число),
а нам привычнее формат DD-MM-YYYY (число-месяц-год). Поэтому для привычного нам отображения даты ее необходимо переформатировать.
Давайте сначала приведем запрос, а затем разберемся, как задавать формат:
SELECT DATE_FORMAT(CURDATE(), "%d.%m.%Y");
Теперь дата выглядит для нас привычно. Для задания формата даты используются специальные определители. Для удобства перечислим их в таблице.
Опред Описание %a Сокращенное наименование дня недели (Mon - понедельник, Tue - вторник, Wed - среда, Thu - четверг, Fri - пятница, Sat - суббота, Sun - воскресенье). Пример:
SELECT DATE_FORMAT(CURDATE(), "%a");
Результат:
%b Сокращенное наименование месяцев (Jan - январь, Feb - февраль, Mar - март, Apr - апрель, May - май, Jun - июнь, Jul - июль, Aug - август, Sep - сентябрь, Oct - октябрь, Nov - ноябрь, Dec - декабрь). Пример:
SELECT DATE_FORMAT(CURDATE(), "%b");
Результат:
%c Месяц в числовой форме (1 - 12). Пример:
SELECT DATE_FORMAT(CURDATE(), "%с");
Результат:
%d День месяца в числовой форме с нулем (01 - 31). Пример:
SELECT DATE_FORMAT(CURDATE(), "%d");
Результат:
%D День месяца в английском варианте (1st, 2nd...). Пример:
SELECT DATE_FORMAT(CURDATE(), "%D");
Результат:
%e День месяца в числовой форме без нуля (1 - 31). Пример:
SELECT DATE_FORMAT(CURDATE(), "%e");
Результат:
%H Часы с ведущим нулем от 00 до 23. Пример:
SELECT DATE_FORMAT("2011-04-15 23:03:20", "%H");
Результат:
%h Часы с ведущим нулем от 00 до 12. Пример:
SELECT DATE_FORMAT("2011-04-15 23:03:20", "%h");
Результат:
%i Минуты от 00 до 59. Пример:
SELECT DATE_FORMAT("2011-04-15 23:03:20", "%i");
Результат:
%j День года от 001 до 366. Пример:
SELECT DATE_FORMAT("2011-04-15 23:03:20", "%j");
Результат:
%k Часы c ведущим нулем от 0 до 23. Пример:
SELECT DATE_FORMAT("2011-12-31 01:03:20", "%k");
Результат:
%l Часы без ведущим нуля от 1 до 12. Пример:
SELECT DATE_FORMAT("2011-04-15 00:03:20", "%l");
Результат:
%M Пример:
Результат:
%M Название месяца без сокращения. Пример:
SELECT DATE_FORMAT("2011-04-15 00:03:20", "%M");
Результат:
%m Месяц в числовой форме с ведущим нулем (01 - 12). Пример:
SELECT DATE_FORMAT("2011-04-15 00:03:20", "%m");
Результат:
%p АМ или РМ для 12-часового формата. Пример:
SELECT DATE_FORMAT("2011-04-15 00:03:20", "%p");
Результат:
%r Время в 12-часовом формате. Пример:
SELECT DATE_FORMAT("2011-04-15 00:03:20", "%r");
Результат:
%s Секунды от 00 до 59. Пример:
SELECT DATE_FORMAT("2011-04-15 00:03:20", "%s");
Результат:
%T Время в 24-часовом формате. Пример:
SELECT DATE_FORMAT("2011-04-15 21:03:20", "%T");
Результат:
%u Неделя (00 - 52), где первым днем недели считается понедельник. Пример:
SELECT DATE_FORMAT("2011-04-17 21:03:20", "%u");
Результат:
%U Неделя (00 - 52), где первым днем недели считается воскресенье. Пример:
SELECT DATE_FORMAT("2011-04-17 21:03:20", "%U");
Результат:
%W Название дня недели без сокращения. Пример:
SELECT DATE_FORMAT("2011-04-17 21:03:20", "%W");
Результат:
%w Номер дня недели (0 - воскресенье, 6 - суббота). Пример:
SELECT DATE_FORMAT("2011-04-17 21:03:20", "%w");
Результат:
%Y Год, 4 разряда. Пример:
SELECT DATE_FORMAT("2011-04-17 21:03:20", "%Y");
Результат:
%y Год, 2 разряда. Пример:
SELECT DATE_FORMAT("2011-04-17 21:03:20", "%y");
Результат:
- STR_TO_DATE(date, format)
функция обратная предыдущей, она принимает дату date в формате format,
а возвращает дату в формате MySQL.
SELECT STR_TO_DATE("17.04.2011 23:50", "%d.%m.%Y %H:%i");
- TIME_FORMAT(time, format)
функция аналогична функции DATE_FORMAT(), но используется только для
времени:
SELECT TIME_FORMAT("22:38:15", "%H-%i-%s");
- GET_FORMAT(date, format)
функция возвращает строку форматирования, соответствующую одному
из пяти форматов времени:
EUR - европейский стандарт
USA - американский стандарт
JIS - японский индустриальный стандарт
ISO - стандарт ISO (международная организация стандартов)
INTERNAL - интернациональный стандартЭту функцию хорошо использовать совместно с предыдущей - DATE_FORMAT(). Посмотрим на примере:
SELECT GET_FORMAT(DATE, "EUR"), DATE_FORMAT("2011-04-17", GET_FORMAT(DATE, "EUR"));
Как видите, сама функция GET_FORMAT() возвращает формат представления, а вместе с функцией DATE_FORMAT() выдает дату в нужном
формате. Сделайте сами запросы со всеми пятью стандартами и посмотрите на разницу.
Описание диапазона величин для каждого типа и возможные форматы представления даты и времени приведены в разделе section 6.2.2 Типы данных даты и времени .
Ниже представлен пример, в котором используются функции даты. Приведенный запрос выбирает все записи с величиной date_col в течение последних 30 дней:
Mysql> SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) DAYOFWEEK(date) Возвращает индекс дня недели для аргумента date (1 = воскресенье, 2 = понедельник, ... 7 = суббота). Эти индексные величины соответствуют стандарту ODBC: mysql> SELECT DAYOFWEEK("1998-02-03"); -> 3 WEEKDAY(date) Возвращает индекс дня недели для аргумента date (0 =понедельник, 1 = вторник, ... 6 = воскресенье): mysql> SELECT WEEKDAY("1997-10-04 22:23:00"); -> 5 mysql> SELECT WEEKDAY("1997-11-05"); -> 2 DAYOFMONTH(date) Возвращает порядковый номер дня месяца для аргумента date в диапазоне от 1 до 31: mysql> SELECT DAYOFMONTH("1998-02-03"); -> 3 DAYOFYEAR(date) Возвращает порядковый номер дня года для аргумента date в диапазоне от 1 до 366: mysql> SELECT DAYOFYEAR("1998-02-03"); -> 34 MONTH(date) Возвращает порядковый номер месяца в году для аргумента date в диапазоне от 1 до 12: mysql> SELECT MONTH("1998-02-03"); -> 2 DAYNAME(date) Возвращает название дня недели для аргумента date: mysql> SELECT DAYNAME("1998-02-05"); -> "Thursday" MONTHNAME(date) Возвращает название месяца для аргумента date: mysql> SELECT MONTHNAME("1998-02-05"); -> "February" QUARTER(date) Возвращает номер квартала года для аргумента date в диапазоне от 1 до 4: mysql> SELECT QUARTER("98-04-01"); -> 2 WEEK(date) WEEK(date,first) При наличии одного аргумента возвращает порядковый номер недели в году для date в диапазоне от 0 до 53 (да, возможно начало 53-й недели) для регионов, где воскресенье считается первым днем недели. Форма WEEK() с двумя аргументами позволяет уточнить, с какого дня начинается неделя - с воскресенья или с понедельника. Неделя начинается с воскресенья, если второй аргумент равен 0, и с понедельника - если 1: mysql> SELECT WEEK("1998-02-20"); -> 7 mysql> SELECT WEEK("1998-02-20",0); -> 7 mysql> SELECT WEEK("1998-02-20",1); -> 8 mysql> SELECT WEEK("1998-12-31",1); -> 53 Примечание: в версии 4.0 функция WEEK(#,0) была изменена с целью соответствия календарю США. YEAR(date) Возвращает год для аргумента date в диапазоне от 1000 до 9999: mysql> SELECT YEAR("98-02-03"); -> 1998 YEARWEEK(date) YEARWEEK(date,first) Возвращает год и неделю для аргумента date . Второй аргумент в данной функции работает подобно второму аргументу в функции WEEK() . Следует учитывать, что год может отличаться от указанного в аргументе date для первой и последней недель года: mysql> SELECT YEARWEEK("1987-01-01"); -> 198653 HOUR(time) Возвращает час для аргумента time в диапазоне от 0 до 23: mysql> SELECT HOUR("10:05:03"); -> 10 MINUTE(time) Возвращает количество минут для аргумента time в диапазоне от 0 до 59: mysql> SELECT MINUTE("98-02-03 10:05:03"); -> 5 SECOND(time) Возвращает количество секунд для аргумента time в диапазоне от 0 до 59: mysql> SELECT SECOND("10:05:03"); -> 3 PERIOD_ADD(P,N) Добавляет N месяцев к периоду P (в формате YYMM или YYYYMM). Возвращает величину в формате YYYYMM . Следует учитывать, что аргумент периода P не является значением даты: mysql> SELECT PERIOD_ADD(9801,2); -> 199803 PERIOD_DIFF(P1,P2) Возвращает количество месяцев между периодами P1 и P2 . P1 и P2 должны быть в формате YYMM или YYYYMM . Следует учитывать, что аргументы периода P1 и P2 не являются значениями даты: mysql> SELECT PERIOD_DIFF(9802,199703); -> 11 DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type) ADDDATE(date,INTERVAL expr type) SUBDATE(date,INTERVAL expr type) Данные функции производят арифметические действия над датами. Обе являются нововведением версии MySQL 3.22. Функции ADDDATE() и SUBDATE() - синонимы для DATE_ADD() и DATE_SUB() . В версии MySQL 3.23 вместо функций DATE_ADD() и DATE_SUB() можно использовать операторы + и - , если выражение с правой стороны представляет собой столбец типа DATE или DATETIME (см. пример ниже). Аргумент date является величиной типа DATETIME или DATE , задающей начальную дату. Выражение expr задает величину интервала, который следует добавить к начальной дате или вычесть из начальной даты. Выражение expr представляет собой строку, которая может начинаться с - для отрицательных значений интервалов. Ключевое слово type показывает, каким образом необходимо интерпретировать данное выражение. Вспомогательная функция EXTRACT(type FROM date) возвращает интервал указанного типа (type) из значения даты. В следующей таблице показана взаимосвязь аргументов type и expr:
| Значение Type | Ожидаемый формат expr |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| MONTH | MONTHS |
| YEAR | YEARS |
| MINUTE_SECOND | "MINUTES:SECONDS" |
| HOUR_MINUTE | "HOURS:MINUTES" |
| DAY_HOUR | "DAYS HOURS" |
| YEAR_MONTH | "YEARS-MONTHS" |
| HOUR_SECOND | "HOURS:MINUTES:SECONDS" |
| DAY_MINUTE | "DAYS HOURS:MINUTES" |
| DAY_SECOND | "DAYS HOURS:MINUTES:SECONDS" |
| Определитель | Описание |
| %M | Название месяца (январь...декабрь) |
| %W | Название дня недели (воскресенье...суббота) |
| %D | День месяца с английским суффиксом (1st, 2nd, 3rd и т.д.) |
| %Y | Год, число, 4 разряда |
| %y | Год, число, 2 разряда |
| %X | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%V" |
| %x | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%v" |
| %a | Сокращенное наименование дня недели (Вс...Сб) |
| %d | День месяца, число (00..31) |
| %e | День месяца, число (0..31) |
| %m | Месяц, число (01..12) |
| %c | Месяц, число (1..12) |
| %b | Сокращенное наименование месяца (Янв...Дек) |
| %j | День года (001..366) |
| %H | Час (00..23) |
| %k | Час (0..23) |
| %h | Час (01..12) |
| %I | Час (01..12) |
| %l | Час (1..12) |
| %i | Минуты, число (00..59) |
| %r | Время, 12-часовой формат (hh:mm:ss M) |
| %T | Время, 24-часовой формат (hh:mm:ss) |
| %S | Секунды (00..59) |
| %s | Секунды (00..59) |
| %p | AM или PM |
| %w | День недели (0=воскресенье..6=суббота) |
| %U | Неделя (00..53), где воскресенье считается первым днем недели |
| %u | Неделя (00..53), где понедельник считается первым днем недели |
| %V | Неделя (01..53), где воскресенье считается первым днем недели. Используется с `%X" |
| %v | Неделя (01..53), где понедельник считается первым днем недели. Используется с `%x" |
| %% | Литерал `%" . |
User Comments
| Posted by on Thursday June 20 2002, @7:08am | [Delete ] [Edit ] |
When selecting a timestamp datatype from a table
and want adjust to a timezone (this example is
from pacific time to EST):
SELECT date_format(DATE_ADD(,
INTERVAL 3 HOUR), "%M %e, %Y at %H:%i EST") FROM
;
| Posted by Dan Allen on Tuesday June 25 2002, @12:50am | [Delete ] [Edit ] |
| Posted by on Wednesday December 18 2002, @5:28pm | [Delete ] [Edit ] |
I have to wonder why there is no function that
does basically what this does:
SELECT FLOOR((UNIX_TIMESTAMP(NOW()) -
UNIX_TIMESTAMP(date_of_birth))/60/60/24/364.25)
as age
It would make a lot of code look a lot
cleaner.
This is even of particular importance for use
on
storing o collecting data about people in the US,
as US law prohibits collecting personal
information about anyone under 13 years of age,
and this trick figures out their age in years.
It would be a lot nicer with an AGE(date
[,date2]
) function.
The closest thing in there is period_diff,
which
doesn"t accept a standard date string and returns
months, which are
useful
Actually, this doesn"t work in the case of pre-
epoch birthdates, worse yet. unix_timestamp
returns 0 for all pre-epoch dates.
I contend that that is a bug and really needs
to
be fixed.
Have to use period_diff/12 I guess.
| Posted by Matthew Mullenweg on Wednesday December 18 2002, @5:27pm | [Delete ] [Edit ] |
You bring up some important issues, but
dealing with ages really isn"t that
hard. For
example you could do something like this:
Mysql> SELECT DATE_FORMAT(
FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), "%Y")+0
AS age FROM people;
Where "dob" is obviously their date of birth.
It"ll also work with pre and post-epoch dates.
Please excuse the funky formatting as the
comment system seems to insist on inserting line
breaks into the code block. I
ran into this problem while working on some
genealogical things over at
href="http://www.mullenweg.com" >Mullenweg.com
, a family site. I hope this helps!
| Posted by Dan Allen on Wednesday December 18 2002, @5:31pm | [Delete ] [Edit ] |
Seems to be a real pain to get the days in the
month, but here is one way
select
DATE_FORMAT(CONCAT(YEAR("2002-05-05"), "-",
MONTH("2002-05-05" + INTERVAL 1 MONTH), "-01") -
INTERVAL 1 DAY, "%e") as numDays
I guess it would be nice if we could just have a
DATE_FORMAT entity for this
| Posted by Isaac Shepard on Wednesday December 18 2002, @5:31pm | [Delete ] [Edit ] |
If you"re looking for generic SQL queries that will
allow you to get the days, months, and years
between any two given dates, you might consider
using these. You just need to substitute date1 and
date2 with your date fields and mytable with your
table name.
Number of days between date1 and date2: SELECT TO_DAYS(date2) - |
Number of months between date1 and date2: SELECT PERIOD_DIFF |
Number of years between date1 and date2: SELECT (YEAR(date2) - YEAR |
Now for some comments about these.
1. These results return integer number of years,
months, and days. They are "floored." Thus, 1.4
days would display as 1 day, and 13.9 years would
display as 13 years.
2. Note that I use boolean expressions in some
cases. Because boolean expressions evaluate to 0
or 1, I can use them to subtract 1 from the total
based on a condition.
For example, to calculate the number of years
between to dates, first simply subtract the years.
The problem is that doing so isn"t always correct.
Consider the number of years between July 1, 1950
and May 1, 1952. Technically, there is only one full
year between them. On July 1, 1952 and later,
there will be two years. Therefore, you should
subtract one year in case the date hasn"t yet
reached a full year. This is done by checking the if
the second month-day is before the first month-
day. If so, this results in a value of 1, which is
subtracted from the total.
3. To get the month-day, I use MID. This is better
than using RIGHT, since it will work for both dates
and datetimes.
4. As mentioned in a previous post, PERIOD_DIFF
needs yearmonth format, but there is really no best
way to do this. To get this, I use DATE_FORMAT
(date1,"%Y%m").
5. Unlike many other solutions, these queries should
work with dates prior to 01/01/1970.
6. Feedback would be much appreciated. Since I"m
using this in my own applications, I would be happy if
you let me know if you discover an error in the logic.
| Posted by Jason Rust on Wednesday December 18 2002, @5:31pm | [Delete ] [Edit ] |
A couple other time between functions. This is
another way to calculate the months between two
dates which may come in handy at times as it is
linear:
Months Between 2002-02-15 and
2002-01-15
(((YEAR("2002-02-15") - 1) * 12 +
MONTH("2002-02-15")) - ((YEAR("2002-01-15") - 1) *
12 + MONTH("2002-01-15"))) - (MID("2002-01-15", 9,
2) < MID("2002-02-15", 9, 2))
The following is a weeks between function:
Weeks Between 2002-08-28 and
2002-08-21
FLOOR((TO_DAYS("2002-08-28") -
TO_DAYS("2002-08-21")) / 7)
| Posted by on Thursday September 12 2002, @6:22am | [Delete ] [Edit ] |
You can"t do DATE_ADD("15:30:00", INTERVAL 55
MINUTE), it will return NULL, the workaround I found
is:
DATE_FORMAT(DATE_ADD(CONCAT("2000-01-
01 ",`time_field`),INTERVAL "minutes" MINUTE), "%
H:%i:%s")
| Posted by Marcelo Celleri on Tuesday September 17 2002, @2:58pm | [Delete ] [Edit ] |
I have two datetime fields, (date_out, date_in) ,
they"re records of loggin and logout times and I
need to find the way to get the difference between
the two of them, and I tried this one: f_out -
f_in but it gave me an integer result that is
worthless for me, I need the difference in
seconds Could you please help me , cause I don"t
have an idea how to convert this answer to seconds
| Posted by Ricky Orea on Tuesday November 12 2002, @3:51pm | [Delete ] [Edit ] |
My user inputs a date in the format
of "MM/DD/YYYYY", how can I convert it to the
format "YYYY/MM/DD" before I save it on a mysql
table?
| Posted by Ram Narayan on Monday November 18 2002, @8:46pm | [Delete ] [Edit ] |
Hi All,
Adding to my friend Ricky Orea"s query, If my user
enters the date in dd mmm yyyy format(26 nov
2002), how should i insert into the mysql db.
| Posted by louis bennett on Thursday November 21 2002, @11:35am | [Delete ] [Edit ] |
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
Not to be pedantic, but there is never a day "0"
in a month...
| Posted by Twidi on Wednesday December 18 2002, @5:27pm | [Delete ] [Edit ] |
How to obtain number of days in a month:
Just take the first day of the month, add one
month (to get the first day of the next month) and
substract one day (to get the last day of the
previous month, that is the number of days), as
follow:
Select
dayofmonth(date_sub(date_add(concat(date_format(MYDATE,
"%Y-%m"), "-01"), interval 1 month), interval 1
day)) as number_of_days from MYTABLE;
(just replace MYDATE and MYTABLE)
Perhaps there"s an other way...
| Posted by on Sunday December 1 2002, @10:46am | [ on Wednesday January 8 2003, @5:25am | [Delete ] [Edit ] |
I am trying to write a MySQL script that will populate a table with records for each value between 2 given parameters. Without the loop structue available in
Stored Procedures, this is proving problematic. Does anyone have a solution?
Rough Example:-
Table "test" has a date field "test_date".
публикация данной статьи разрешена только со ссылкой на сайт автора статьи
Как известно, все даты хранятся в mysql в обратном порядке год-месяц-число(2008-10-18), иногда даже без разделителя(20081018).
Чтобы вывести дату, нужно ее преобразовать в нормальный читаемый вид.
Тут есть два способа преобразования, эффективный и не очень.
Не эффктивный способ это когда дату выводимую из mysql преобразуют с помощью php.
Сам лично так делал очень долгое время. Перед выводом на экран переворачивал каждую дату с помощью php функции.
Если количество преобразований не большое, то можно переворачивать дату и с помощью php, ничего плохого тут нет, но если нужно будет выдернуть десятки-сотни тысяч записей и в каждой преобразовать дату, то тут конечно намного быстрее будет преобразование дат с помощью mysql.
В mysql существует отличная функция DATE_FORMAT(), она очень похожа на php функцию date().
Вот пример использования
SELECT DATE_FORMAT("2008-11-19","%d.%m.%Y");
результат
Все очень просто и быстро, нет необходимости крутить даты с помощью php.
Вот список определителей этой функции
| Определитель | Описание |
| %M | Название месяца (январь...декабрь) |
| %W | Название дня недели (воскресенье...суббота) |
| %D | День месяца с английским суффиксом (0st, 1st, 2nd, 3rd и т.д.) |
| %Y | Год, число, 4 разряда |
| %y | Год, число, 2 разряда |
| %X | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%V" |
| %x | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с "%v" |
| %a | Сокращенное наименование дня недели (Вс...Сб) |
| %d | День месяца, число (00..31) |
| %e | День месяца, число (0..31) |
| %m | Месяц, число (00..12) |
| %c | Месяц, число (0..12) |
| %b | Сокращенное наименование месяца (Янв...Дек) |
| %j | День года (001..366) |
| %H | Час (00..23) |
| %k | Час (0..23) |
| %h | Час (01..12) |
| %I | Час (01..12) |
| %l | Час (1..12) |
| %i | Минуты, число (00..59) |
| %r | Время, 12-часовой формат (hh:mm:ss M) |
| %T | Время, 24-часовой формат (hh:mm:ss) |
| %S | Секунды (00..59) |
| %s | Секунды (00..59) |
| %p | AM или PM |
| %w | День недели (0=воскресенье..6=суббота) |
| %U | Неделя (00..53), где воскресенье считается первым днем недели |
| %u | Неделя (00..53), где понедельник считается первым днем недели |
| %V | Неделя (01..53), где воскресенье считается первым днем недели. Используется с `%X" |
| %v | Неделя (01..53), где понедельник считается первым днем недели. Используется с `%x" |
| %% | Литерал `%". |
Комментарии
27.11.2008 ----
Ай шайтан!!!
Сам занимаюсь лет пять php и mysql, и все время переделывал дату в php...
Даже в голову не приходило, что проще использовать втроенную функцию mysql
28.11.2008 Жека
Аналогично! Всегда пользовался своей функцией на php
03.12.2008 Сергей
Ну а вообще, кто то хоть пользуется таким подходом?
Или все используют php для переворота даты?
Я лично никогда не переворачивал дату в mysql, до сих пор все в php делаю
28.06.2009 Илья
К сожалению, ничего не получилось:(
08.07.2009 Виталий
Круто, спасибо за функцию. Интересно, а ёщё какие нибуть загогулины есть?
14.07.2009 DSaint
Спасибо, очень помогло. Осталось только название месяца на русском отобразить)
28.07.2009 Влад
mysql=>PHP
select unix_timestamp(start_date) as start_date_php
php-code
date("d.m.Y",$row["start_date_php"])
PHP=>MySQL
update table set start_date=DATE_FORMAT(STR_TO_DATE("19.12.2009 18:35:22","%d.%m.%Y %H:%i"),"%Y.%m.%d %H:%i")
18.08.2009 Гость
2: DSaint
Есть такая чудная функция:
ELT(MONTH("2004-04-10"), "Янв.","Фев.","Март","Апр.","Май","Июнь","Июль","Авг.","Сен.","Окт.","Ноя.","Дек.")
Пользуйся. :-)
29.10.2009 Владимир
Спасибо, про ELT(MONTH("2004-04-10"), "Янв.","Фев.","Март","Апр.","Май","Июнь","Июль","Авг.","Сен.","Окт.","Ноя.","Дек.")
я не слыхал.
07.10.2010 Евгений
А что быстрее работает? Преобразование в запросе, или в результате работы php-функции?
07.10.2010 defender
Как минимум меньше памяти на обработку меньше вызовов функций меньше выделений памяти... Постоянно подобным пользуюсь только не в mysql а в postgresql.
08.10.2010 Админ
Евгений, вот defender правильно сказал, через базу это преобразование должно сработать красивее, но конечно если речь идет об извлечении огромного количества данных.
Если вы дергаете 10-20 записей, то нет никакой разницы как преобразовать дату, по сравнению с остальными нагрузками это мелочь.
27.01.2011 pcemma
ухх спасибо аффтору (: отпала нужда юзать свою мега крутую функцию для преобразования (:
13.04.2011 Xes
кАК ЕЕ ПОЛЬЗОВАТЬ В ПХП
while ($sqlr=mysql_fetch_array($sql))
{
echo ($sqlr["comadd"]." ".$sqlr["comment"]."
");
$sqlr["comadd"] - Надо в нормальной форме представитт?
}
14.04.2011 Виталий
У меня в базе дата записана в формате 19.11.2008 тип таблицы VARCHAR, как ее перезаписать в базу в формате 2008-11-19?
Руками просто очень долго...
Спасибо.
15.04.2011 админ
Xes, это функция MySQL, использовать ее нужно в запросе sql, который судя по вашему коду находится где-то выше. В данном участке кода ее нельзя использовать.
15.04.2011 админ
Виталий, просто измените тип ячейки на DATE, mysql автоматически переведет все данные в этой ячейке именно в формат 2008-11-19.
Но на всякий случай перед изменением типа ячейки сделайте дамп этой таблицы, так как вдруг база что то не то сделает и таблица сломается вообще.
Если вам важно оставить тип поля varchar, то после того, как вы установите тип DATE, установите обратно varchar...
Это самый простой вариант, но не совсем правильный, но я проверил, он работает.
14.05.2011 DDD
date("d-m-Y",strtotime("$myrow"));
24.05.2011 Константин
а я всегда беру SELECT *,UNIX_TIMESTAMP(created) AS created FROM...
а мотом могу хоть в каком формате. Хоть только день, хоть только время...
и сравнивать, что больше 14.05.2011 или 14.05.2010...
А записываю так:
...date=".gmdate("Y-m-d H:i:s",$created)...
и вобщем-то, не вижу причин менять привычки
24.05.2011 сергей
Константин, да я и сам использую для вывода php date(), просто тут рассмотрен вариант преобразования даты не через php, а через mysql.
Я бы сказал, что это просто обзор mysql функции и она конечно имеет право на существование...
Для начала, хочу затронуть тему о том, в каком формате лучше хранить даты в базе: TIMESTAMP
или DATETIME
. Этот вопрос неоднократно поднимался и поднимается на форумах, блогах и т.д. Но дабы не отправлять вас сразу же в поисковики, я попробую простыми словами и на примере показать разницу. Тип DATETIME
- хранит значение даты в формате "YYYY-MM-DD HH:MM:SS" и не зависит от часового пояса. TIMESTAMP
- хранит метку времени, т.е. количество секунд, прошедших с 1 января 1970-го года. Эти значение, MySQL преобразует с учётом текущего часового пояса как при записи в базу, так и при выводе из неё. Что сие значит...
К примеру, вы только что добавили в базу статью, на календаре у вас первое января 2014 года, а на часах - 01:00. Если поле даты имеет тип DATETIME, то все, кто бы ни зашёл на сайт, увидят именно эту дату и время, не зависимо от их места проживания. Вроде бы всё нормально, но у пользователя (назовём его "Билл Г"
), проживающего где-нибудь в Нью-Йорк, еще не наступило первое января - у него 31 декабря 2013 года и часы показывают 19:00. У него лёгкое недоумение, т.к. праздновать новый год он ещё не начал, а уже мерещиться "статья из будущего" ;) С типом TIMESTAMP этого не произойдёт, т.к. при выводе будет учитываться его часовой пояс.
"Всё ясно!", - скажете вы и быстренько измените все поля для дат на тип TIMESTAMP, а Билл Г вздохнет с облегчением, но ненадолго. При регистрации на вашем сайте, Билл указал дату и время своего рождения. Путешествуя по миру, он обязательно заглядывает на ваш сайт и с ужасом обнаруживает, что время, а иногда и дата его рождения, всегда разные, т.к. выводятся с учетом часового пояса, в котором он находится в данный момент. Да, в этом случае, тип TIMESTAMP сыграл злую шутку.
Делаем вывод - для определённых задач, нужно выбирать соответствующий тип поля или контролировать запись/вывод в зависимости от желаемого результата.
Переходим к популярным задачам и вариантам их решения. Выбрать записи в диапазоне указанных дат, т.е. за определенный период времени.
SELECT * FROM `table_name` WHERE `date_field` BETWEEN "2014-07-05" AND "2014-07-15" ORDER BY `date_field`;
Выберутся все записи, где даты в поле "date_field", будут в диапазоне от 5 июля 2014 года до 15 июля 2014, включая указанные даты. Нужно не забывать, что по умолчанию даты в MySQL хранятся в формате "ГГГГ-ММ-ДД ЧЧ:ММ:СС" и соответственно маска формата - "%Y-%m-%d %H:%i:%s" (стандарт ISO ). А как решать вопрос, если дата приходит не в таком формате? Отбросим варианты с PHP и посмотрим, как это можно сделать в самом запросе. А для таких целей, нам пригодится функция STR_TO_DATE() . Синтаксис: STR_TO_DATE(str, format) , где "str " - строка даты и "format " - соответствующий ей формат. Протестируем:
SELECT STR_TO_DATE("31.12.2013", "%d.%m.%Y"); /* "2013-12-31" */ SELECT STR_TO_DATE("31/12/13 13:50", "%d/%m/%y %H:%i"); /* "2013-12-31 13:50:00" */
Результат выполнения - дата в формате, который используется в MySQL по умолчанию. То есть, нам надо указать не формат, в котором мы хотим получить дату на выходе, а формат, в котором мы предоставляем дату для обработки. Используя такой способ, наша запись выше, могла бы выглядеть даже так:
SELECT * FROM `table_name` WHERE `date_field` BETWEEN STR_TO_DATE("05.07.2014", "%d.%m.%Y") AND STR_TO_DATE("July 15, 2014", "%M %d,%Y") ORDER BY `date_field`;
Раз уж затронули вопрос форматирования дат, то давайте разберем то, как получать дату при выборке в нужном нам формате, т.к. многим гораздо привычней видеть "31.12.2014" или "31 декабря 2014", чем "2014-12-31". Для таких целей используют функцию DATE_FORMAT() . Синтаксис: DATE_FORMAT(date, format) , где "date " - строка даты и "format " - формат, в который необходимо преобразовать "date ". В отличии от функции STR_TO_DATE(), мы сами указываем желаем формат на выходе, а вот дату нужно указывать в формате ISO, т.е. "ГГГГ-ММ-ДД ЧЧ:ММ:СС". Проверяем:
SELECT DATE_FORMAT("2014-12-31", "%d.%m.%Y"); // 31.12.2014 SELECT DATE_FORMAT("2014-12-31", "%d %M %Y"); // 31 December 2014
Если бы мы общались с вами в реальном времени, то в этом месте, скорее всего, что сразу последовал бы вопрос: "А как выводить месяц на другом языке: украинском, русском или китайском, в конце концов? " Очень просто - установить необходимую локаль . А сделать это можно или же в конфигурационном файле MySQL (my.cnf), или же просто запросом из PHP, после подключения к базе и перед основным запросов:
SET lc_time_names = ru_RU; SELECT DATE_FORMAT("2014-12-31", "%d %M %Y"); // результат: 31 Декабря 2014 // при желании, можно добавить еще и "г." или "года" SELECT DATE_FORMAT("2014-12-31", "%d %M %Y года"); // результат: 31 Декабря 2014 года
Красотища! ;) И еще несколько примеров запросов, которые так же бывают часто нужны, но вызывают ступор у новичков.
// Выбрать записи за текущий день SELECT * FROM `table_name` WHERE `date_field` >= CURDATE(); // Все записи за вчерашний день SELECT * FROM `table_name` WHERE `date_field` >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND `date_field` NOW() - INTERVAL 30 DAY; // Выбрать всё за определенный месяц текущего года (например, за май месяц) SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = 5; // или за май месяц, но в 2009 году SELECT * FROM `table_name` WHERE YEAR(`date_field`) = 2009 AND MONTH(`date_field`) = 5;
Подробно описывать используемые в примерах функции MySQL - я смысла не вижу, т.к. они интуитивно-понятны и для человека, хоть немного знакомого с английским, не составит труда понять, что, к примеру, функция MONTH() возвращает месяц даты, YEAR() - её год, а DAY() (или синоним DAYOFMONTH() ) - день. Ключевое слово INTERVAL - служит для арифметических действий над датами, их изменением.
SELECT "2014-07-07 23:59:59" + INTERVAL 1 SECOND; // результат: 2014-07-08 00:00:00 SELECT "2014-07-07 23:59:59" + INTERVAL 1 DAY; // результат: 2014-07-08 23:59:59 // то же самое. но с помощью функции DATE_ADD() SELECT DATE_ADD("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-08 23:59:59 // Если надо не добавить, а отнять SELECT DATE_SUB("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-06 23:59:59 // или так просто SELECT "2014-07-07 23:59:59" - INTERVAL 1 DAY; // 2014-07-06 23:59:59
Это далеко не все функции для работы с датами и я бы посоветовал вам пробежаться по ним в ознакомительных целях на официальном сайте для того, чтобы знать об их существовании, если возникнет нестандартная ситуация. Но хочу надеяться, что даже такой небольшой обзор функций MySQL для работы с датами в этой статье, поможет вам сориентировать в ситуации и принять правильное решение. Если всё-таки возникнут сложности, то задавайте вопросы в этой теме или разделе "Ваш вопрос ". Будем разбираться вместе;)
the function
YEARMONTH() is blatantly missing!!! Say you have
two dates and you want to do a period_diff...you
can"t just concat() YEAR() and MONTH() because
month is not "0" padded...just seems with
YEARWEEK() it would make sense to also have
YEARMONTH()...thoughts? I know you can do it with
DATE_FORMAT, but then why all the other
functions?