Time 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 года

Красотища! 😉 И еще несколько примеров запросов, которые так же бывают часто нужны, но вызывают ступор у новичков.

Подробно описывать используемые в примерах функции MySQL — я смысла не вижу, т.к. они интуитивно-понятны и для человека, хоть немного знакомого с английским, не составит труда понять, что, к примеру, функция MONTH() возвращает месяц даты, YEAR() — её год, а DAY() (или синоним DAYOFMONTH()) — день. Ключевое слово INTERVAL — служит для арифметических действий над датами, их изменением.


Это далеко не все функции для работы с датами и я бы посоветовал вам пробежаться по ним в ознакомительных целях на официальном сайте для того, чтобы знать об их существовании, если возникнет нестандартная ситуация. Но хочу надеяться, что даже такой небольшой обзор функций MySQL для работы с датами в этой статье, поможет вам сориентировать в ситуации и принять правильное решение. Если всё-таки возникнут сложности, то задавайте вопросы в этой теме или разделе "Ваш вопрос". Будем разбираться вместе 😉

incode.pro

Работа с датами и временем в PHP и MySQL

При работе с любым языком программирования, задачи, касающиеся даты и времени, часто является тривиальными и простыми.


PHP, к счастью, имеет один из самых мощных инструментов по работе с датой/временем, который поможет вам легко справиться со всеми видами задач связанных с временем: работа с Unix timestamp, форматирование дат для лучшего восприятия их человеком, работа с часовыми поясами, подсчет количества дней между сегодняшним днем ​​и, например, вторым вторником в следующем месяце, и т.д. В этой статье вы ознакомитесь с основными временными функциями PHP (time(), mktime() и date()) и их объектно-ориентированными «коллегами», а затем познакомитесь с MySQL-датами, и мы покажем вам, как подружить их с PHP.

PHP-функции даты и времени

Большая часть этой статьи будет посвящена работе с Unix-временем или POSIX. Время здесь представлено в виде количества секунд, прошедших с полуночи 1 января 1970 года, UTC. Если вы заинтересованы в полной истории времени Unix, то ознакомьтесь со статьей Unix time в Википедии.

UTC, известное также под именем Coordinated Universal Time, также известное как GMT, а иногда и Zulu time, это время нулевого градуса долготы.   Все остальные часовые пояса в мире являются положительным или отрицательным смещением относительно этого времени. UTC и Unix время сделает вашу жизнь проще, когда вам придется иметь дело с часовыми поясами. Я расскажу об этом чуть позже, а пока рассмотрим некоторые функции для работы со временем.

Получение текущего времени Unix


Функция time() без аргументов возвращает количество секунд, прошедших с начала эпохи Unix. Чтобы проиллюстрировать это я буду использовать интерактивную консоль PHP.

Если вам нужен массив для работы с Unix-временем, используйте функцию getdate(). Она принимает необязательный аргумент Unix timestamp, если его нет, то по умолчанию это значение функции time().

Форматирование времени Unix

Unix-время может быть легко отформатировано в почти любую строку, чтобы человек мог его прочитать. Функция date() используется для форматирования Unix timestamp в понятную для человека строку и принимает аргументы форматирования и необязательный аргумент «время». Если необязательный аргумент «время» не указан, используется значение функции time(), т.е. текущее время.

Аргумент «r» при форматировании строки возвращает время в формате RFC 2822. Конечно, Вы можете использовать другие спецификаторы, чтобы определить свои собственные форматы.

Полный список допустимых значений для форматирования, смотрите на странице функции date() в документации по PHP. Функция становится еще более полезной, когда используется в сочетании с функциями mktime() и strtotime(), это вы увидите в дальнейших примерах.

Создание Unix-времени от заданного времени

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


Вы можете установить isDST равный 1, если используется летнее время, в противном случае 0, или -1, если это неизвестно (значение по умолчанию).

Вы можете видеть, что mktime() может быть очень полезна при работе с запросами к базе данных, которые используют диапазоны дат определенные пользователем. Например, если вы храните timestamp в виде целых чисел (Unix время) в MySQL, очень легко настроить общий временной диапазон для запроса.

Преобразование английских дат в Unix время

Почти магическая функция strtotime() принимает строку даты/времени в качестве первого аргумента, и Unix timestamp, чтобы использовать в качестве основы для преобразования. Смотрите документацию для получения информации о допустимых форматах даты.

Объекты DateTime и DateTimeZone

Объект PHP DateTime является объектно-ориентированным подходом к работе с датами и часовыми поясами. Конструктор принимает строковое представление времени, очень похоже на функцию strtotime(), о которой говорилось выше. Значение по умолчанию, если аргумент не указан, «now» (сейчас).

Метод format() объекта DateTime работает так же, как функция date(), и принимает все те же символы форматирования. Объект DateTime также имеет несколько полезных констант, которые могут быть переданы в метод format().


Полный список констант можно найти на странице документации DateTime.

Поскольку мы будем работать с часовыми поясами, давайте установим часовой пояс по умолчанию. В файле конфигурации php.ini (у меня есть один для CLI и один для Apache) найдите раздел, который выглядит следующим образом:

Если значение не указано для date.timezone, PHP будет пытаться сделать все возможное, чтобы определить часовой пояс системы которая установлена на вашем сервере. Вы можете проверить, какое значения использует PHP при помощи функции date_default_timezone_get().

Давайте установим временную зону сервера на время UTC (date.timezone = UTC) и сохраним файл конфигурации. Вы должны перезагрузить Apache или CLI, чтобы увидеть изменения.

Объект DateTime включает в себя внутренний экземпляр класса DateTimeZone для отслеживания часовых поясов. Когда вы создаете новый экземпляр DateTime, DateTimeZone должен быть установлен по умолчанию как указано в php.ini.

Полный список допустимых имен часовых поясов можете найти на соответствующей странице документации.

Теперь вы можете увидеть разницу во времени, между двумя объектами DateTime, созданных для разных часовых поясов. Например, вот пример, который преобразует время из UTC в America/New_York(EST).


Обратите внимание на смещение -0500 в декабре месяце. Если вы измените значение времени на летний день, например, 1 июля, то увидите, что он «знает» летнее время (EDT).

Использование даты в MySQL и PHP

Если вы использовали MySQL на любом уровне, вы, вероятно, обратили внимание на тип данных DATETIME. Он выглядит как дата, и если бы вы сказали, что это дата, то вы были бы правы. Но поскольку вы выбираете его из MySQL, в PHP эта дата уже представляет собой строку, которая выглядит как дата.

* Да, я знаю, что MySQL имеет множество функций форматирования даты. Почему же мы хотим отформатировать дату прямо из базы данных в таком случае? У нас может быть несколько различных преобразований и форматов, которые мы хотим применить. И лучше всего форматировать дату только тогда, когда человек собирается её увидеть.

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

Значение DATETIME — это время локального сервера MySQL в любом часовом поясе. Если все, что вы делаете, касается только одного сервера в одном часовом поясе, DATETIME, вероятно, подходит для большинства ваших потребностей, и я очень завидую вам.

Итак, как вам справляться с датами и часовыми поясами в PHP и MySQL? Сохранять даты как Unix timestamps.

Вы уже знаете, что время Unix — это количество секунд, прошедших с 1 января 1970 года в UTC, так что это дает вам постоянный часовой пояс для работы, и, надеюсь, вы заметили, что многие PHP функции даты/времени основываются на Unix timestamps.

При работе с MySQL, я обычно создаю столбец таблицы, который будет хранить даты как INTEGER UNSIGNED. При вставке даты можно использовать функции time() из PHP или UNIX_TIMESTAMP() из MySQL.

Если вы хотите использовать MySQL для форматирования даты, то вы можете. Но время будет соответствовать часовому поясу сервера и я советую вам воздержаться от любого типа форматирования, то тех пока вы не находитесь в шаблоне/на уровне представления в вашем веб-приложении и готовы, чтобы человеческий глаз увидел это.

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

Вы можете легко конвертировать сохраненный Unix timestamp (UTC) в любую дату в часовом поясе конкретного пользователя.

Это вернет дату «Mon, 16 Jan 2012 12:03:49 -0600». Где -0600 говорит нам, что это 6 часов от временной зоны UTC, которая имеет смещение 0.

Если бы мы изменили часовой пояс на America/Los_Angeles, то в результате дата выглядела бы так:

А America/New_York вернет:

Заключение

Работа с датами и временными зонами является ​​частью задач многих программистов, но вам не о чем беспокоиться, если у вас есть PHP, надежный и простой в использовании инструмент для работы с датами.

Вы видели, как легко получить метку времени, как отформатировать дату в любой формат, как преобразовать английское представление даты в метку времени (timestamp) и как преобразовывать время между часовыми поясами. Если выделить две основных темы из статьи, то это: 1) Unix время и 2) UTC, как основная временная зона для всех дат при работе с PHP и MySQL.

Идея отсчитывать всё время по UTC относится не только к PHP и MySQL, это считается хорошей практикой в любом языке.

Перевод статьи с phpmaster.com

www.webmasters.by

Описание таблиц с полями времени

В примере описаны три варианта представления времени. Два — типа MySQL timestamp (start_timestamp, next_timestamp), одна — типа MySQL datetime (start_datetime) и пользовательский вариант в виде строки символов (work_income).

Авторский пример - это не картинка

Тип timestamp — это родной для MySQL формат времени фиксации событий, занимает 8 байт, является целым числом и имеет одну особенность: первая колонка заполняется автоматом в момент создания: запрос (1). Вторая и все следующие — остаются нулевыми, но это не NULL. Запрос (2) содержит функцию MySQL timestamp now(), применённую для заполнения поля start_datetime.

Авторский пример - это не картинка

В этом примере видно, что таблица, сформированная запросом (1, создание таблицы) будет заполнять автоматом первую колонку start_timestamp при создании таблицы. Запрос (2, заполнение таблицы) показывает, что это именно так. При этом вторая колонка next_timestamp остается нулевой: все составляющие даты и времени представлены «0» во всех позициях, но не NULL, как всё поле целиком.

Если из запроса убрать ссылку на поле start_datetime и функцию, его заполняющую now(), то поле start_datetime будет содержать значение NULL. Верхняя часть картинки показывает таблицу с использованием функции заполнения, нижняя часть — таблицу без использования now(). Различия существенные.

Время юникс и пользовательское время

Поля таблицы MySQL timestamp & datetime отличаются по заполнению и использованию. Например, вариант:

  • int mktime ( [int hour [, int minute [, int second [, …

доступен для поля типа datetime, в то время как поле типа timestamp больше ориентируется на функции самого языка MySQL.

С тем, чтобы использовать поля времени наиболее эффективно и безопасно, целесообразно ориентироваться на способ работы с MySQL timestamp в предлагаемом функционале, в том его назначении, которое определено самой базой данных. Это её собственный штамп момента времени, формат представления времени.

MySQL timestamp now

Тип datetime более относим ко времени в представлении эпохи юникс и его можно ассоциировать, например, с функцией PHP mktime() или обычным текстовым форматом, например:

Авторский пример - это не картинка

Здесь переменная $cStndDate — обычный текст, который интерпретируется в запросе как что-то, написанное в формате даты/времени. Если переменная указана правильно, то в нужное поле таблицы запишется нужное значение.

Пользовательское время — это требование задачи. Далеко не всегда следует представлять базе данных все права по управлению временем. Она всегда все сделает «по-своему». Это не всегда правильно, например, нужны названия русских месяцев или особый порядок записи дня, месяца и года.

Часовые пояса и правильная логика

Работа с MySQL timestamp & datetime требует внимательности и аккуратности. Другое дело — собственный формат даты, записанный в виде обычного текста. Правда, в этом случае теряются достоинства стандартных типов даты, например, сортировка или вычисления над датами, но появляются желаемые преимущества.

mysql timestamp

В любом случае, разработчику следует быть предельно внимательным. Часовые пояса, настройки локализации и множество других нюансов могут создать определённое непонимание. Если что-то при работе с датой стало с NULL или нулями во всех позициях, нужно искать, прежде всего, ошибку в собственном алгоритме. MySQL, его поля даты и функции времени работают исключительно стабильно и правильно.

fb.ru

Многие интернет-ресурсы написаны таким образом, что для корректной их работы требуется установка верной даты и времени (часового пояса)  MySQL — а также указания часового пояса в настройках РНР. Часовой пояс является одной из настроек сервера баз данных и может задаваться локально, глобально или для текущей пользовательской сессии.

 

Чтобы определить время MySQL нужно редактировать конфигурационный файл (для большинства дистрибутивов он располагается по адресу /etc/mysql/my.cnf) или задавать переменные непосредственно в консоли сервера баз данных. Рассмотрим задание временной зоны при помощи переменных.

Авторизуемся в консоли MySQL (потребуется пароль пользователя root)

mysql -u root -p

 

При помощи запроса SELECT NOW(); можно просмотреть текущие дату и время MySQL

 

mysql> SELECT NOW();
+———————+
| NOW() |
+———————+
| 2017-08-31 21:21:56 |
+———————+
1 row in set (0.00 sec)

 

Можно вывести только время без даты. Для этого нужно выполнить запрос SELECT CURTIME();

mysql> SELECT CURTIME();
+————+
| CURTIME() |
+————+
| 21:24:16 |
+————+
1 row in set (0.00 sec)

 

 

Установить часовой пояс для определенного пользователя можно выполнив в консоли MySQL

SET time_zone = '+05:00';

 

Чтобы изменения сохранились после перезагрузки и имели силу для всего сервера баз данных нужно задать глобальную переменную:

SET GLOBAL time_zone = '+5:00';

 

Можно также указывать имя временной зоны — например, MSK для Москвы, но лучшей практикой является задавать часовой пояс определяя разницу с временем по Гринвичу как сделано выше

 

Также время опционально для конкретной сессии

mysql> set @@session.time_zone = '+00:00';

 

Можно выяснить требуется ли задавать время для сессии выполнив:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+———————+———————+
| @@global.time_zone | @@session.time_zone |
+———————+———————+
| SYSTEM             | SYSTEM              |
+———————+———————+
1 row in set (0.00 sec)

 

В данном случае время сессии совпадает с системным и непосредственное его задание не требуется.

 

 

Время в MySQL может указываться в TIMESTAMP и DATETIME

  • DATETIME (значение занимает 8 байт) — время не зависит от временной зоны и выводится в том виде, в котором было задано. Изменив время в MySQL изменить значение в DATETIME нельзя (может меняться при установке времени для сессии). Выводится в виде YYYY-MM-DD-HH-MM-SS
  • TIMESTAMP  (значение занимает 4 байта) является абсолютным значением времени с начала эпохи Unix — с полуночи 1 января 1970 года, при выводе из базы учитывается часовой пояс (указанный для системы,  MySQL в целом или сессии MySQL), именно значение в TIMESTAMP обычно приходится корректировать. Значение TIMESTAMP выводится запросом SELECT NOW(); — TIMESTAMP же корректируется всеми приведенными в данном материале командами.

 

Общее правило для корректной работы приложения — время РНР должно совпадать с временем MySQL. Если корректируется время в РНР, то скорректировать его нужно и в MySQL.

 


Чаще всего на практике для сервера на котором работают один или несколько сайтов ценной оказывается команда:

SET GLOBAL time_zone = '+5:00';

 

Кроме часового пояса MySQL приходится задавать часовой пояс в настройках РНР и системное время на сервере.

server-gu.ru

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

Перевод времени из PHP в MySQL

Ну так как же нам перевести время из PHP-скрипта в MySQL? Очень просто. Для начала, давайте решим, откуда в вашем скрипте берется какая-либо метка времени? Допустим, время регистрации пользователя является временем работы скрипта. В этом случае нам достаточно использовать встроенную в PHP функцию time(). Она и вернет текущую метку времени UNIX. А если вам нужно сохранить в базе данных дату, которую пользователь ввел в HTML-форму? Тут задача немного сложнее. Но она сама по себе несложная.

И как теперь сохранить это в базу? Да еще и в формате типа данных MySQL DATETIME… Легко!

 Нувотивсёбырь Задача решена! Проверьте 🙂

www.lezhenkin.ru

Использование календарных типов данный в 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 января по локальному времени, ничего переводить тут не понадобится.

habr.com


You May Also Like

About the Author: admind

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

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

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