Ниже представлен пример, в котором используются функции даты. Приведенный запрос выбирает все записи с величиной 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
Итак, все календарные типы данных подробно описаны в разделе «10.3. Date and Time Types » руководства по MySQL. А важная информация, касающаяся поддержки СУБД временных зон, расписана в разделе «9.7. MySQL Server Time Zone Support ». Все следующее далее базируется на изучении руководства. В то же время, в здесь указаны лишь нюансы выбора в пользу того или иного типа, поэтому этот материал никак не заменяет мануал, но дополняет его.
Вначале краткая характеристика каждого из типов:
- TIMESTAMP - тип данных для хранения даты и времени. Данные хранятся в виде количества секунд, прошедших с начала «эпохи Юникса». Диапазон значений: 1970-01-01 00:00:00 - 2038-12-31 00:00:00. Занимает 4 байта.
- YEAR - тип данных для хранения года. Диапазон значений: 1901 - 2155. Занимает 1 байт.
- DATE - тип данных для хранения даты. Диапазон значений: 1000-01-01 - 9999-12-31. Занимает 3 байта.
- TIME - тип данных для хранения времени. Диапазон значений: −828:59:59 - 828:59:59. Занимает 3 байта.
- DATETIME - тип данных для хранения даты и времени. Диапазон значений: 1000-01-01 00:00:00 - 9999-12-31 00:00:00. Занимает 8 байт.
Проанализировав описание типов, представленное выше, можно сделать практически все выводы о достоинствах и недостатках тех или иных типов. Все довольно просто и очевидно.
Но прежде, чем рассказать об использовании этих типов, хочу заметить, что на практике часто используется другой тип для хранения даты и времени: целочисленное значение (для хранения даты - INT (4 байта), даты и времени - BIGINT (8 байт)). Отличие использования целочисленных типов от DATE и DATETIME лишь в том, что при выводе данные не форматируются, а в вычислениях с датами и временем целые числа требуется преобразовывать в соответствующий календарный тип. Кроме того, не производится проверка на валидность представленного значения перед сохранением. Возможности сортировки сохраняются. Поэтому INT и BIGINT имеет смысл использовать в тех же случаях, как DATE и DATETIME, с целью максимизации переносимости и независимости от СУБД. Других преимуществ я не вижу, если они есть, предлагаю указать в комментах.
Использование календарных типов данный в MySQL
Начнем с самого простого - тип YEAR . Единственное его достоинство - малый размер - всего-то 1 байт. Но из-за этого действует строгое ограничение по диапазону допустимых значений (тип может хранить только 255 разных значений). Мне сложно представить практическую ситуацию, когда может потребоваться хранить года строго в диапазоне от 1901 до 2155. Кроме того, тип SMALLINT (2 байта) дает диапазон, достаточный в большинстве ситуаций для хранения года. А экономить 1 байт на строке в таблице БД в наше время смысла нет.Типы DATE и DATETIME можно объединить в одну группу. Они хранят дату или дату и время с довольно широким диапазоном допустимых значений, независимую от установленной на сервере временной зоны. Их использование определенно имеет практический смысл. Но если требуется хранить даты исторических событий, уходящие в прошлое за Нашу эру, придется выбрать другие типы данных. Для хранения дат неких событий, потенциально выходящих за рамки диапазона типа TIMESTAMP (дни рождений, даты выпуска продуктов, избрания президентов, запуски космических ракет и т.д.), отлично подойдут эти типы. При использовании этих типов нужно учитывать один важный нюанс, но об этом ниже.
Тип TIME можно использовать для хранения промежутка времени, когда не нужна точность меньше 1 секунды, и промежутки времени меньше 829 часов. Добавить тут больше нечего.
Остался самый интересный тип - TIMESTAMP . Рассматривать его надо в сравнении с DATE и DATETIME: TIMESTAMP тоже предназначен для хранения даты и/или времени происхождения неких событий. Важное отличие между ними в диапазонах значений: очевидно, что TIMESTAMP не годится для хранения исторических событий (даже таких, как дни рождений), но отлично подходит для хранения текущих (логирование, даты размещения статей, добавления товаров, оформления заказов) и предстоящих в обозримом будущем событий (выходы новых версий, календари и планировщики и т.д).
Основное удобство использования типа TIMESTAMP состоит в том, что для столбцов этого типа в таблицах можно задавать значение по умолчанию в виде подстановки текущего времени, а так же установки текущего времени при обновлении записи. Если вам требуется эти возможности, то с вероятностью 99% TIMESTAMP - именно то, что вам нужно. (Как этоделать, смотрите в мануале.)
Не стоит бояться того, что с приближением к 2038 году ваш софт перестанет работать. Во-первых, до этого времени вашим софтом, скорее всего, просто перестанут пользоваться (особенно версиями, которые пишутся сейчас). Во-вторых, с приближением к этой дате разработчики MySQL обязательно что-нибудь придумают для сохранения работоспособности вашего софта. Все решится так же хорошо, как проблема Y2K.
Итак, тип TIMESTAMP используем для хранения дат и времени свершения событий нашего времени, а DATETIME и DATE - для хранения дат и времени свершения исторических событий, или событий глубокого будущего.
Диапазоны значений - это важное отличие между типами TIMESTAMP, DATETIME и DATE, но не главное. Главное то, что TIMESTAMP хранит значение в UTC . При сохранении значения оно переводится из текущего временной зоны в UTC, а при его чтении - во время текущей временной зоны из UTC. DATETIME и DATE хранят и выводят всегда одно и то же время, независимо от временных зон.
Временные зоны устанавливаются в СУБД MySQL глобально или для текущего подключения .Последнее можно использовать для обеспечения работы разных пользователей в разных временных зонах на уровне СУБД . Все значения времени физически будут храниться в UTC, а приниматься от клиента и отдаваться клинту - в значениях его временной зоны. Но только при использовании типа данных TIMESTAMP. DATE и DATETIME всегда принимают, хранят и отдают одно и то же значение.
Функция NOW() и ее синонимы возвращают значение времени в текущей временной зоне пользователя.
Учитывая все эти обстоятельства, необходимо быть крайне внимательными при изменении временной зоны в пределах подключения к серверу и использовании типов DATE и DATETIME. Если надо хранить дату (например, дату рождения), то никаких проблем не будет. Дата рождения в любой зоне одинаковая. Т.е. если вы родились 1 января в 0:00 UTC/GMT+0, то это не значит, что в Америке будут праздновать ваш день рождения 31 декабря. Но если вы решите хранить время события в столбце DATETIME, то тут уже построить работу с пользовательскими временными зонами на уровне СУБД просто не выйдет. Поясню на примере:
Пользователь X работает в зоне UTC/GMT+2, Y - в зоне UTC/GMT+3. Для соединений пользователей с MySQL установлена соответствующая (у каждого своя) временная зона. Пользователь размещает сообщение на форуме, нас интересует дата написания сообщения.
Вариант 1: DATETIME. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. Значение в поле «дата» сообщения подставляется как результат выполнения функции NOW() - 14:00. Пользователь Y считывает время написания сообщения и видит те же 14:00. Но у него в настройках стоитзона UTC/GMT+3, и он думает, что сообщение было написано не только что, а час назад.
Вариант 2: TIMESTAMP. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. В поле «дата» попадает результат выполнения функции NOW() - в данном случае - 12:00 UTC/GMT+0. ПользовательY считывает время написания сообщения и получает (UTC/GMT+3)(12:00 UTC/GMT+0) = 15:00 UTC/GMT+3. Все получается ровно так, как мы хотим. И главное - пользоваться этим крайне удобно: для поддержки пользовательских временных зон не нужно писать никакой код приведения времени.
Возможности подстановки текущего времени и работы с временными зонами в типе TIMESTAMP настолько весомы, что если вам в неком логе надо хранить дату без времени, все равно стоит использовать TIMESTAMP, вместо DATE, не экономя 1 байт разницы между ними. При этом на «00:00:00» просто не обращать внимания.
Если же вы не можете использовать TIMESTAMP из-за относительно малого диапазона его значений (а обычно это 1-2 случая против 10-15 в базе сайта), придется использовать DATETIME и аккуратно его корректировать значения в нужных местах (т.е. при записи в это поле переводить дату в UTC, а при чтении - во время в зоне считывающего пользователя). Если вы храните только дату, то скорее всего не важно, какая у вас временная зона: новый год все празднуют 1 января по локальному времени, ничего переводить тут не понадобится.
Эти функции также предназначены для работы с календарными типами данных. Рассмотрим их подробнее.
- 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() выдает дату в нужном
формате. Сделайте сами запросы со всеми пятью стандартами и посмотрите на разницу.
Значения в этих форматах:
Как строка в формате "YYYY-MM-DD" или "YY-MM-DD" . Разрешен "расслабленный" синтаксис: любой символ пунктуации может использоваться как разделитель между частями даты. Например, "2012-12-31" , "2012/12/31" , "2012^12^31" и "2012@12@31" эквивалентны.
Как строка без разделителей в формате "YYYYMMDD" или "YYMMDD" при условии, что строка имеет смысл в качестве даты. Например, "20070523" и "070523" интерпретируются как "2007-05-23" , но "071332" является незаконным (он имеет бессмысленные месячные и дневные части) и становится "0000-00-00" .
Как число в формате YYYYMMDD или YYMMDD , при условии, что число имеет смысл в качестве даты. Например, 19830905 и 830905 интерпретируются как "1983-09-05" .
Следовательно, строка "08/25/2012" не является допустимым литералом даты MySQL. У вас есть четыре варианта (в каком-то неопределенном порядке предпочтения, без дополнительной информации о ваших требованиях):
-
$dt = \DateTime::createFromFormat("m/d/Y", $_POST["date"]);
а затем либо:
получить подходящую форматированную строку:
$date = $dt->format("Y-m-d");
получить временную метку UNIX:
$timestamp = $dt->getTimestamp();
который затем передается непосредственно в MySQL FROM_UNIXTIME() :
INSERT INTO user_date VALUES ("", "$name", FROM_UNIXTIME($timestamp))
Настройте Datepicker для предоставления дат в поддерживаемом формате с помощью altField вместе с altFormat :
$("selector").datepicker({ altField: "#actualDate" altFormat: "yyyy-mm-dd" });
Или, если вы довольны тем, что пользователи видят дату в формате YYYY-MM-DD , просто установите вместо параметра dateFormat :
$("selector").datepicker({ dateFormat: "yyyy-mm-dd" });
Вручную введите строку в действительный литерал:
$parts = explode("/", $_POST["date"]); $date = "$parts-$parts-$parts";
Предупреждение
-
Тип DATE используется для значений с частью даты, но без временной части. MySQL извлекает и отображает значения DATE в формате "YYYY-MM-DD" . Поддерживаемый диапазон составляет от "1000-01-01" до "9999-12-31" .
Тип DateTime используется для значений, содержащих как дату, так и время. MySQL извлекает и отображает значения DateTime в формате "YYYY-MM-DD HH:MM:SS" . Поддерживаемый диапазон составляет от "1000-01-01 00:00:00" до "9999-12-31 23:59:59" .
Ваш код уязвим для SQL-инъекции. Вы действительно должны использовать подготовленные заявления , в которые вы передаете свои переменные как параметры, которые не оцениваются для SQL. Если вы не знаете, о чем я говорю, или как это исправить, прочитайте историю Bobby Tables .
