Mysql date format


Естественное представление о времени — это даты: год, месяц, день и время: часы, минуты секунды. В некоторых приложениях имеют значение дни недели и доли секунд, но во всех приложениях и всегда необходимы инструменты для обработки переменных времени и их представления в естественном виде.

mysql date format

Время характеризуется местом в пространстве, где событие происходит и точкой, где оно может быть использовано. Это общий порядок, который определяет необходимость локализации и учёт часовых поясов.

MySQL: типы данных для записи времени

Формально, типы данных в системе управления базами данных MySQL составляет пять описателей (date, time, timestamp и year). Разработчик часто предпочитает универсальный вариант: char (20) — когда есть объективные основания использовать свою интерпретацию функции MySQL date_format() или обеспечить нужные условия для правильной сортировки.


mysql date format

В этом примере формируется таблица вариантов представления дат, и в неё вносятся две строки. Первая строка содержит данные в естественном формате, и в составе заполняемых полей отсутствует поле «x_timestamp». Вторая строка по всем полям использует функцию MySQL now(). Следует отметить, что указанное поле заполняется в обоих случаях.

Метка времени и её формат

Этот тип поля времени (timestamp) и использование функции MySQL date_format() чрезвычайно эффективно для создания меток времени. Это «не имеет никакого отношения» к любому алгоритму. Это не требует особых изысков в представлении даты или времени. Это просто временная метка — точка на линии развития событий.

mysql select date format

Поле данного типа заполняется в момент создания строки. Нет необходимости уделять ему внимание, но всегда и в любом запросе можно положиться: MySQL date_format () datetime = время, когда строка была создана. Важно, естественно, никогда его не менять. Строка создана и время её создания определено лишь однажды. Читать можно, изменять нельзя.

У данного решения очень много сфер применения:

  • протокол диалога с посетителем;
  • лог журнала безопасности;
  • отслеживание действий сотрудников;
  • календарь событий;
  • почтовый ящик и т. д.

Время — это, прежде всего, череда событий. Всё, что связано с веб-ресурсом, стремящемуся к совершенству, росту рейтинга и реальному отражению действительности так или иначе связано с правильным отображением времени:

  • в том виде, как есть, — это не видно пользователю, но доступно разработчику;
  • в том виде, как это должно быть отражено на веб-ресурсе, использовано в алгоритме, учтено в документе и т. д.

Время многогранно и зависит от той точки пространства, где произошло событие в контексте той точки пространства, где оно должно быть использовано или отображено.

Общий смысл MySQL select date_format()

Запрос на выборку информации — самая главная фраза на языке SQL. Она настолько востребована, что очень важно при формировании нужной выборки максимально быстро превращать её содержимое в надлежащий вид, не отягощая алгоритм дополнительными конструкциями.

mysql select date format

С этой точки зрения MySQl date_format() позволяет оформлять любое значение даты и времени в тот формат, в который необходимо сразу в момент выполнения запроса.

В таблице содержатся две строки и имеются два одинаковых поля: «x_timestamp» и «x_datetime». Запрос обращается к этим двум полям, но указывает их имена, минуя PHP, в date MySQL format непосредственно.

Строка форматирования может быть любой. Содержание строки форматирования транслируется в результат запроса в формате «как есть» с заменой ключевых элементов формата «%*», где символ звёздочки означает символ формата: год, день, месяц, час.

Национальный формат времени и сортировка


Использование даты в стандарте типа MySQL — лучшее решение для использования функционала языка SQL. Нет проблем ни с сортировкой, ни с вычислениями, ни с форматированием в стиле MySQL date_format().

Но не во всех странах рабочая неделя начинается с воскресенья, не всегда считается нормальным представление времени в 12-часовом формате и не во всех языках есть понятия «AM» и «PM». Есть много такого рода национальных особенностей, но подавляющее большинство из них лежит за пределами функционала языка SQL.

php date mysql format

Сомнительно, что вообще существует такой диалект SQL, который способен расшириться на учёт национального колорита стран дальше, чем за пределы термина «локализация». Собственно, не удивительно, что «локализация» на практике означает больше автозамену одних слов (сочетаний) на другие в зависимости от языка.

Но не всегда национальный язык определяет всё, в первую очередь, он никак не связан с правильным представлением времени.

Ключ событий и ключ данных


Время никогда не течёт вспять, следовательно, это идеальный ключ к любой строке, к любой таблице, к любой базе данных. Два события могут произойти одновременно, если интервал их отслеживания больше, чем разница во времени между их появлением. Например, два пользователя зашли на сайт в одну и ту же минуту, но в разные секунды этой минуты.

mysql date format datetime

На PHP есть интересное решение: есть объект и есть экземпляр объекта. Есть идея статической «переменной»:

  • private static $iUniqueNo = -1; // «УУ» всегда уникальное число
  • // при каждом вызове этой функции $iUniqueNo будет другим и
  • // в течение одной секунды никогда не повторится.
  • public function IncUniqueNo() {
  • if (self::$iUniqueNo > 99)
  • self::$iUniqueNo = 0;
  • else
  • self::$iUniqueNo++;
  • }

Не слишком фантазируя, сливая в единое целое: год, месяц, день, часы, минуты, секунды и $iUniqueNo, получаем: ГГГГММДДччммссУУ — уникальный ключ («УУ» = «$iUniqueNo») для любой записи и одновременно штамп момента времени для любого использования.

Это идеальное решение в контексте типа времени базы данных и функции MySQL date_format(), которая элементарно делает и время, и ключ из одного поля «timestamp» одновременно.

fb.ru

Some Date/Time Basics


MySQL supports a variety of date/time data type combinations. All you have to do, when creating a date/time column, is choose one of the following types that most closely matches the date/time portion and accuracy that you require:

  • DATE: Stores a date value in the form YYYY-MM-DD. For example 2008-10-23.
  • TIME: values in ‘HH:MM:SS’ format (or ‘HHH:MM:SS’ format for large hours values). TIME values may range from ‘-838:59:59’ to ‘838:59:59’.
  • DATETIME: Stores a date and time value of the form YYYY-MM-DD HH:MM:SS. For example 2008-10-23 10:37:22. The supported range of dates and times is 1000-01-01 00:00:00 all the way through to 9999-12-31 23:59:59
  • TIMESTAMP: Similar to DATETIME but more precise, as the TIMESTAMP includes milliseconds. There are a few other differences as well, depending on the version of MySQL and the mode in which the server is running.
  • YEAR: The YEAR type is a one-byte type used for representing years. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. The default is four characters if no width is given. For four-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000.

A table containing DATE and DATETIME columns is created much the same way as any other column in a table. For example, we can create a new table called orders that contains order number, order item, order date and order delivery columns as follows:

 CREATE TABLE `MyDB`.`orders` (  `order_no` INT NOT NULL AUTO_INCREMENT,  `order_item` TEXT NOT NULL,  `order_date` DATETIME NOT NULL,  `order_delivery` DATE NOT NULL,  PRIMARY KEY (`order_no`) ) 

While it is common to store dates using a dash (-) as the delimiter and a colon (:) as the time delimiter, it is in fact possible to use any character, or even no character at all, between the date and time segments. Hence, the following formats all represent the same date and time:

 2009-10-20 10:39:11 20091020103911 2009/10/20 10.39.11 2009*10*20*10*39*11 

Input Values

MySQL will happily accept any of the following statements as date inputs:

 INSERT INTO tbl_name (idate) VALUES (19970505); INSERT INTO tbl_name (idate) VALUES ('19970505'); INSERT INTO tbl_name (idate) VALUES ('97-05-05'); INSERT INTO tbl_name (idate) VALUES ('1997.05.05'); INSERT INTO tbl_name (idate) VALUES ('1997 05 05'); INSERT INTO tbl_name (idate) VALUES ('0000-00-00'); INSERT INTO tbl_name (idate) VALUES (CURDATE()); 

The yyyy-mm-dd format used by MySQL is called the International (or ISO) date format. The beauty of using that format is that it prevents the days and months from being mixed up. Depending on the database locale, ambiguous months and days such as 3/4/1999 could be interchanged. I have run into this issue many times in Access, which relied on the ‘dd/mm/yyyy’ short date format!


Applications which insert dates must convert them from a pure date data type into a string comprised of some variation of year, month, and day format. The following VBScript code would convert a date and time into a YYYY-M-D h:m:s string such as ‘2006-3-3 15:2:7’:

 dt = CDate( dt ) ISODateTime = "'" & Year(dt) & "-" & Month(dt) & "-" & Day(dt) _  	 & " " & Hour(dt) & ":" & Minute(dt) & ":" & Second(dt) & "'" 

Note that, without the time formatting on the final line, the time portion would be left out and would be lost forever!

Output Values

By default, MySQL also displays dates in the International format:

 SELECT idate FROM tbl_name WHERE idate = CURDATE();  --returns 2011-01-21 

To display dates and times in a different format, use MySQL’s versatile DATE_FORMAT() function:

 DATE_FORMAT(date,format) 

It accepts a number of specifiers in the format argument. These are the «%» character followed by format specifier characters. For example, %e specifies the numeric month; %h specifies the hour:

 SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); --returns something like 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); --returns something like '22:23:00'   

Working with Month Names

The date format standard where I work is ‘DD-MMM-YYYY’ where MMM is the abbreviated three-letter month name. Having worked with this format for years, I can’t think of a more diabolical way to drive developers batty! There have been many trials and tribulations, but we’ve gotten the hang of it now! Here’s what I’ve learned:

Ideally, you should exert control over the inputs by using some type of GUI widget that limits the month names to valid dates. A dropdown or calendar control works nicely for this purpose. Conversely, accepting date inputs via freeform text fields is asking for trouble.

Other inputs, such as those submitted from a partner via XML data, must be accepted in whatever format is given to you. Working with dates which contain full month names can be especially problematic. In these instances, MySQL’s STR_TO_DATE() function can help. It is the inverse of the DATE_FORMAT() function. It takes a value string (str) and a format string (format). The format string can contain literal characters and format specifiers beginning with «%». Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. STR_TO_DATE() returns a date/time value if the format string contains both date and time parts, or a date or time value if the string contains only date or time parts. If the date/time value extracted from str is invalid, STR_TO_DATE() returns NULL and produces a warning:

 

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y'); '2013-05-01' SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y'); '2013-05-01'

Displaying month names is fairly easy because the DATE_FORMAT() function has specifiers for both full and abbreviated month names ( %M and %b respectively ). The MONTHNAME() function can also be of service there. Just be careful with abbreviated month names because some locales, like French (‘fr’), use a four letter abbreviation. The problem with French month abbreviations is that three letters is not enough to distinguish between June and July, which are juin and juillet in French.

Beginning with MySQL 5.0.25, the locale indicated by the lc_time_names system variable controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions. Locale names have a language and region portion listed by IANA (http://www.iana.org/assignments/language-subtag-registry) such as ‘ja_JP’ or ‘pt_BR’. The default value is ‘en_US’.

The following function reads the language portion of the lc_time_names system variable to return a three letter month name in the current locale. It accepts the month number as the input parameter, ranging from 1 to 12:

 

CREATE FUNCTION `GetThreeLetterMonthName`(`month` SMALLINT) RETURNS char(3) CHARSET latin1 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN CASE WHEN LEFT(@@lc_time_names, 2) = 'fr' AND month = 7 THEN 'jul' ELSE LEFT(MONTHNAME(STR_TO_DATE(month,'%c')), 3) END; END;

The following tests highlight the workaround for the June/July issue in French. Juillet is thus displayed as jul:

 SELECT @@lc_time_names; -->returns 'en_US' SELECT GetThreeLetterMonthName(6); -->returns 'Jun' SELECT GetThreeLetterMonthName(7); -->returns 'Jul' SET lc_time_names = 'fr_CA'; SELECT GetThreeLetterMonthName(6); -->returns 'jui' SELECT GetThreeLetterMonthName(7); -->returns 'jul' 

www.databasejournal.com

Just aggregating some of what I found on this page in a single query.

SELECT
— DAY OF LAST WEEK — LAST YEAR
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) +7 DAY) LAST_YEAR_LAST_WK_MONDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) +6 DAY) LAST_YEAR_LAST_WK_TUESDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) +5 DAY) LAST_YEAR_LAST_WK_WEDNESDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) +4 DAY) LAST_YEAR_LAST_WK_THURSDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) +3 DAY) LAST_YEAR_LAST_WK_FRIDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) +2 DAY) LAST_YEAR_LAST_WK_SATURDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) +1 DAY) LAST_YEAR_LAST_WK_SUNDAY,

— DAY OF THIS WEEK — LAST YEAR
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) -0 DAY) LAST_YEAR_THIS_WK_MONDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) -1 DAY) LAST_YEAR_THIS_WK_TUESDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) -2 DAY) LAST_YEAR_THIS_WK_WEDNESDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) -3 DAY) LAST_YEAR_THIS_WK_THURSDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) -4 DAY) LAST_YEAR_THIS_WK_FRIDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) -5 DAY) LAST_YEAR_THIS_WK_SATURDAY,
DATE_SUB((CURRENT_DATE — INTERVAL 1 YEAR),INTERVAL WEEKDAY((CURRENT_DATE — INTERVAL 1 YEAR)) -6 DAY) LAST_YEAR_THIS_WK_SUNDAY,

— DAY OF LAST WEEK — THIS YEAR
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +7 DAY) LAST_WK_MONDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +6 DAY) LAST_WK_TUESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +5 DAY) LAST_WK_WEDNESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +4 DAY) LAST_WK_THURSDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +3 DAY) LAST_WK_FRIDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +2 DAY) LAST_WK_SATURDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) +1 DAY) LAST_WK_SUNDAY,

— DAY OF CURRENT WEEK — THIS YEAR
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -0 DAY) CUR_WK_MONDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -1 DAY) CUR_WK_TUESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -2 DAY) CUR_WK_WEDNESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -3 DAY) CUR_WK_THURSDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -4 DAY) CUR_WK_FRIDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -5 DAY) CUR_WK_SATURDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -6 DAY) CUR_WK_SUNDAY,

— DAY OF NEXT WEEK — THIS YEAR
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -7 DAY) NEXT_WK_MONDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -8 DAY) NEXT_WK_TUESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -9 DAY) NEXT_WK_WEDNESDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -10 DAY) NEXT_WK_THURSDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -11 DAY) NEXT_WK_FRIDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -12 DAY) NEXT_WK_SATURDAY,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) -13 DAY) NEXT_WK_SUNDAY,

— FIRST AND LAST OF MONTH —
DATE_FORMAT( CURRENT_DATE — INTERVAL 1 MONTH, '%Y-%m-01') LAST_MON_START,
LAST_DAY(CURRENT_DATE — INTERVAL 1 MONTH) LAST_MONTH_END,
DATE_FORMAT( CURRENT_DATE, '%Y-%m-01' ) THIS_MON_START,
LAST_DAY(SYSDATE()) THIS_MONTH_END,
DATE_FORMAT( CURRENT_DATE + INTERVAL 1 MONTH, '%Y-%m-01') NEXT_MON_START,
LAST_DAY(CURRENT_DATE + INTERVAL 1 MONTH) NEXT_MONTH_END,

— LAST YEAR, THIS YEAR, NEXT YEAR
YEAR(CURRENT_DATE — INTERVAL 1 YEAR) LAST_YEAR,
YEAR(CURRENT_DATE) CURRENT_YEAR,
YEAR(CURRENT_DATE + INTERVAL 1 YEAR) NEXT_YEAR,

DATE_FORMAT( CURRENT_DATE, '%Y-01-01') FIRST_DAY_OF_CUR_YEAR,
DATE_FORMAT( CURRENT_DATE, '%Y-12-31') LAST_DAY_OF_CUR_YEAR
;

dev.mysql.com

Формат даты и времени

MySQL date format поддерживает несколько форматов даты и времени. Их можно определить следующим образом:

DATE — хранит значение даты в виде ГГГГ-ММ-ДД. Например, 2008-10-23.
DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.

Создание полей даты и времени

Таблица, содержащая типы данных DATE и DATETIME, создается так же, как и другие столбцы. Например, мы можем создать новую таблицу под названием orders, которая содержит столбцы номера заказа, заказанного товара, даты заказа и даты доставки заказа:

Столбец ORDER_DATE — это поле типа MySQL DATE TIME, в которое мы записываем дату и время, когда был сделан заказ. Для даты доставки невозможно предсказать точное время, поэтому мы записываем только дату.

Форматы даты и времени

Наиболее часто используемым разделителем для дат является тире (—), а для времени — двоеточие (:). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.

Например, все следующие форматы являются правильными:

Функции даты и времени

MySQL содержит множество функций, которые используются для обработки даты и времени. В приведенной ниже таблице представлен список наиболее часто используемых функций:

Вы можете поэкспериментировать с этими функциями MySQL date format, даже не занося никаких данных в таблицу. Например:

Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):
mysql> SELECT MONTHNAME(NOW());

+——————+
| MONTHNAME(NOW()) |
+——————+
| October |
+——————+
1 row in set (0.00 sec)

Внесение значений даты и времени в столбцы таблицы

Рассмотрим, как вносятся значения date MySQL в таблицу. Чтобы продемонстрировать это, мы продолжим использовать таблицу orders, которую создали в начале статьи.

Мы начнем с добавления новой строки заказа. Значение поля order_no будет автоматически увеличиваться на 1, так что нам остается вставить значения order_item, дату создания заказа и дату доставки. Дата заказа — это время, в которое вставляется заказ, поэтому мы можем использовать функцию NOW(), чтобы внести в строку текущую дату и время.

Дата доставки — это период времени после даты заказа, которую мы можем вернуть, используя функцию MySQL DATE_ADD(), которая принимает в качестве аргументов дату начала (в нашем случае NOW ()) и INTERVAL (в нашем случае 14 дней). Например:

Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в качестве даты доставки:

Точно так же можно заказать товар с датой доставки через два месяца:

Извлечение данных по дате и времени

В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:

Точно так же мы можем использовать BETWEEN, чтобы выбрать товары, доставка которых произойдет между двумя указанными датами. Например:

Заключение

В этой статье мы рассмотрели форматы, используемые для определения даты и времени, и перечислили функции, используемые в для операций в MySQL с тип DATE. А также несколько примеров внесения и извлечения данных.

Перевод статьи «Working with Dates and Times in MySQL» был подготовлен дружной командой проекта Сайтостроение от А до Я.

www.internet-technologies.ru


You May Also Like

About the Author: admind

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.