Как сравнить даты в гугл таблицах
Перейти к содержимому

Как сравнить даты в гугл таблицах

  • автор:

Разница между двумя датами в таблицах Google, выраженными в годах, месяцах, днях (в одном столбце)

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

вы может также установить его из nuget.

3 ответа

0

0

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

Если вы примете это, то следующие формулы должны работать:

enter image description here

РЕДАКТИРОВАТЬ

Возможный обходной путь — взять дни, оставшиеся в начальном месяце

, что, кажется, согласуется с этим веб-сайтом

Вы также можете использовать аргумент MD Datedif:

Но как в Google Sheets, так и в Excel это может привести к отрицательному числу, как указано в документации Excel :

Как посчитать дни между двумя датами в Google Таблицах

Если вы хотите подсчитать количество дней между двумя датами, вы можете использовать для этого функции ДНИ, РАЗНДАТ и ЧИСТРАБДНИ в Google Таблицах. DAYS и DATEDIF учитывают все дни, а NETWORKDAYS не включают субботу и воскресенье.

Подсчет всех дней между двумя датами

Для подсчета дней между двумя датами, независимо от того, является ли день будним или праздничным, вы можете использовать функции ДНИ или РАЗНДАТ.

Использование функции ДНИ

Функцию ДНЕЙ проще всего использовать, если вы не беспокоитесь об исключении праздников или выходных. Однако в DAYS будут учтены дополнительные дни, проведенные в високосном году.

Чтобы использовать ДНЕЙ для подсчета двух дней, откройте Google Таблицы таблицу и щелкните пустую ячейку. Введите = ДНИ («01.01.2019 ″,« 01.01.2018 »), заменив показанные даты своими собственными.

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

Как показано в приведенном выше примере, функция ДНЕЙ подсчитывает общее количество дней между двумя конкретными датами. Формат даты, используемый в приведенном выше примере, — это британский формат ДД / ММ / ГГГГ. Если вы находитесь в США, убедитесь, что вы используете ММ / ДД / ГГГГ.

Вам нужно будет использовать формат даты по умолчанию для вашего региона. Если вы хотите использовать другой формат, нажмите «Файл»> «Настройки электронной таблицы» и измените значение «Локаль» на другое место.

Вы также можете использовать функцию ДНИ со ссылками на ячейки. Если вы указали две даты в отдельных ячейках, вы можете ввести = ДНИ (A1, A11), заменив ссылки на ячейки A1 и A11 своими собственными.

Функция ДНЕЙ в Google Таблицах, вычисляющая количество дней, проведенных в двух других ячейках.

В приведенном выше примере разница в 29 дней регистрируется с дат, содержащихся в ячейках E6 и F10.

Использование функции РАЗНДАТ

Альтернативой ДНЯМ является функция РАЗНДАТ, которая позволяет вычислить количество дней, месяцев или лет между двумя установленными датами.

Как и DAYS, DATEDIF учитывает високосные дни и рассчитывает все дни, а не ограничивает вас рабочими днями. В отличие от DAYS, DATEDIF не работает в обратном порядке, поэтому сначала используйте дату начала, а затем дату окончания.

Если вы хотите указать даты в формуле РАЗНДАТ, щелкните пустую ячейку и введите = РАЗНДАТ («01.01.2018 ″,« 01.01.2019 ″, «D»), заменив даты своими собственными.

Функция РАЗНДАТ в Google Таблицах, вычисляющая количество дней между двумя установленными датами, используемыми в формуле.

Если вы хотите использовать даты из ссылок на ячейки в своей формуле DATEDIF, введите = DATEDIF (A7, G7, ”D”), заменив ссылки на ячейки A7 и G7 своими собственными.

Функция РАЗНДАТ в Google Таблицах, вычисляющая количество дней между двумя датами, используя две отдельные ссылки на ячейки.

Подсчет рабочих дней между двумя датами

Функции ДНИ и РАЗНДАТ позволяют находить дни между двумя датами, но они считают все дни. Если вы хотите подсчитывать только рабочие дни и не учитывать дополнительные праздничные дни, вы можете использовать функцию ЧИСТРАБДНИ.

NETWORKDAYS рассматривает субботу и воскресенье как выходные дни, не считая их при расчете. Как и DATEDIF, NETWORKDAYS сначала использует дату начала, а затем дату окончания.

Чтобы использовать ЧИСТРАБДНИ, щелкните пустую ячейку и введите = ЧИСТРАБДНИ (ДАТА (2018,01,01), ДАТА (2019,01,01)). Использование вложенной функции ДАТА позволяет преобразовывать числа в годах, месяцах и датах в порядковые номера даты в указанном порядке.

Замените показанные цифры вашими цифрами года, месяца и даты.

Функция ЧИСТРАБДНИ в Google Таблицах, вычисление рабочих дней между двумя датами и игнорирование субботы и воскресенья с использованием вложенной формулы ДАТА.

Вы также можете использовать ссылки на ячейки в формуле ЧИСТРАБДНИ вместо вложенной функции ДАТА.

Введите = ЧИСТРАБДНИ (A6, B6) в пустой ячейке, заменив ссылки на ячейки A6 и B6 своими собственными.

Функция ЧИСТРАБДНИ в Google Таблицах, вычисляя рабочие дни между двумя датами и игнорируя субботу и воскресенье, используя даты из двух ссылок на ячейки.

В приведенном выше примере функция ЧИСТРАБДНИ используется для расчета рабочих дней между разными датами.

Если вы хотите исключить определенные дни из своих расчетов, например дни определенных праздников, вы можете добавить их в конце формулы ЧИСТРАБДНИ.

Для этого щелкните пустую ячейку и введите = ЧИСТРАБДНИ (A6, B6, . В этом примере A6 — это дата начала, B6 — дата окончания, а диапазон B6: D6 — это диапазон ячейки, содержащие праздничные дни, которые нужно исключить.

Вы можете заменить ссылки на ячейки своими датами, используя вложенную функцию DATE, если хотите. Для этого введите = ЧИСТРАБДНИ (E11, F13, <ДАТА (2019,11,18), ДАТА (2019,11,19)>), заменив ссылки на ячейки и критерии ДАТА своими собственными цифрами.

Функция ЧИСТРАБДНИ в Google Таблицах вычисляет рабочие дни между двумя датами и игнорирует субботу и воскресенье, за исключением дополнительных праздничных дней.

В приведенном выше примере один и тот же диапазон дат используется для трех формул ЧИСТРАБДНИ. Если в ячейке B2 указано 11 стандартных рабочих дней, из ячеек B3 и B4 удаляются от двух до трех дополнительных выходных дней.

Запрос и сравнение даты в таблицах Google

Я работаю над созданием реплики листа 1 на другой лист 2 (тот же документ), и query () работал нормально до тех пор, пока столбец, который я хочу отфильтровать, не будет ячейками формулы (ДЛИННЫМИ с запросом, совпадением и т. Д.).

Что я хочу сделать, так это отфильтровать строки в листе 1, где приближается дата события в столбце M (есть больше условий фильтрации, но основная проблема — это просто для упрощения).

Мне не нужны строки, в которых дата либо пуста, либо в прошлом (различные форматы даты), либо в которых формула дает результат в виде пустой строки «».

Формулы, которые я пробовал (что дает ошибку) — обратите внимание, я просто выбираю 2 столбца для тестирования:

= query (sheet1! A3: N, «выберите I, M, где I = ‘Сингапур’ И ДАТА ЗНАЧЕНИЯ (M)> СЕГОДНЯ ()», 0)

= query (sheet1! A3: N, «выберите I, M, где I = ‘Сингапур’ И M> СЕГОДНЯ ()», 0)

Эта формула не дает ошибок, но не отображает правильные данные — показывает все данные, кроме января 2017 г. — 7 августа 2017 г.

= ФИЛЬТР (лист1! A3: N, лист1! I3: I = «Сингапур», лист1! M3: M> СЕГОДНЯ ())

Эта формула дает пустой результат:

= query (sheet1! A3: N, «выберите I, M, где I = ‘Сингапур’ AND M = ’22 августа ‘», 0)

Я новичок в Google Таблицах и скриптах приложений, так что ценим любые советы от кого-либо Спасибо !!

Функции для работы с датой и временем в google таблицах

В Google Таблицах есть несколько функций для работы с датой и временем – есть очень полезные, есть менее очевидные. Рассмотрим их.

Функции для работы с датой и временем в google Таблицах

На скриншоте во втором столбце результат действия формулы, а в третьем – текст самой формулы.

Далее я расскажу про каждую функцию отдельно. Отмечу, что в качестве даты можно использовать ссылку на ячейку с таковой или указывать дату напрямую в формуле в формате «01.02.2015».

Функция TODAY (СЕГОДНЯ)

возвращает сегодняшнюю дату. Аргументов у нее нет – сегодняшний день и есть сегодняшний. Результат вычисления функции обновляется вместе с остальными ячейками при любом изменении в документе.

Функция NOW (ТДАТА)

возвращает текущие время и дату. Аргументов тоже нет. Если ее не отформатировать – в ячейке будет отображаться и то, и другое:

Функции для работы с датой и временем в google Таблицах

А если в качестве формата использовать временной, в ячейке будет только текущее время:

Функции для работы с датой и временем в google Таблицах

  • Сверху неотформатированная ячейка, снизу – ячейка с временным форматом.
  • Функции для работы с датой и временем в google Таблицах
  • Функция NETWORKDAYS (ЧИСТРАБДНИ)
  • возвращает количество рабочих дней между двумя датами. Даты могут храниться в ячейках, как в этом примере:
  • Функции для работы с датой и временем в google Таблицах
  • Между первым октября 2015 года и 18 февраля 2016 года – 101 рабочий день.
  • Кроме того, даты можно задать и в самой формуле, хотя это и менее гибкий способ – ведь в ячейках их менять удобнее:
  • Функции для работы с датой и временем в google Таблицах
  • Можно задать в качестве второго аргумента функцию TODAY – и каждый день у вас будет отображаться актуальное количество рабочих дней, прошедших с определенной даты (аналогично можно задать TODAY и как первый аргумент – и отслеживать количество рабочих дней ДО определенной даты):
  • Функции для работы с датой и временем в google Таблицах
  • Последний аргумент этой функции – праздники, то есть даты, которые рабочими не считаются.
  • Удобнее всего их задать в диапазоне, а в функции сослаться на этот диапазон. Диапазон может выглядеть так:
  • А формула так:
  • Функции для работы с датой и временем в google Таблицах
  • Рабочих дней стало 95.

Функция NETWORKDAYS.INTL (ЧИСТРАБДНИ.INTL)

предыдущей возможностью задать нестандартную рабочую неделю. Это третий аргумент, и задается он в виде «0000011», где нули – это рабочие дни, а единицы – выходные. Допустим, для шведской четырехдневной недели формула будет выглядеть так:

  1. Функции для работы с датой и временем в google Таблицах
  2. И рабочих дней стало 77.
  3. Функция WORKDAY (РАБДЕНЬ)
  4. возвращает дату, которая наступит по прошествии определенного количества дней (второй аргумент) с определенной даты (первый аргумент):
  5. Функции для работы с датой и временем в google Таблицах

В данном примере мы ссылаемся на ячейку B7, в которой стоит дата 01.10.2015. По прошествии 155 рабочих дней с этой даты наступит пятое мая.

  • Функции DAY (ДЕНЬ), MONTH (МЕСЯЦ), YEAR (ГОД)
  • возвращают соответствующий параметр из даты, которая является единственным аргументом этой функции:
  • В примере аргумент – сегодняшняя дата, заданная функцией TODAY.
  • Функция WEEKNUM (НОМНЕДЕЛИ)

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

  1. Функция DATEDIF (РАЗНДАТ)
  2. вычисляет количество дней, месяцев и лет между двумя датами.
  3. Первые два аргумента – стартовая и конечная даты. А третий – параметр, у него есть следующие варианты:
  4. D – полные дни;
  5. M – полные месяцы;
  6. Y – полные годы. В примере находим разницу между 1 октября 2015 и 18 февраля 2016:
  7. Видимо, что полного года еще не прошло – но можно вычислить десятичную долю года, воспользовавшись функцией YEARFRAC (ДОЛЯГОДА)
  8. .
  9. У DATEDIF есть еще три варианта последнего аргумента:

Основные принципы работы с датами и временем в Excel

6426 27.10.2012 Скачать пример

Видео

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

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

«Классическая» форма 3.10.2006
Сокращенная форма 3.10.06
С использованием дефисов 3-10-6
С использованием дроби 3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

Функции для работы с датой и временем в google Таблицах

  • Время вводится в ячейки с использованием двоеточия. Например
  • 16:45
  • По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:
  • 16:45:30
  • И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

Функции для работы с датой и временем в google Таблицах

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

Функции для работы с датой и временем в google Таблицах

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY):

Функции для работы с датой и временем в google Таблицах

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка ЧислоОбщий), то можно увидеть интересную картинку:

Функции для работы с датой и временем в google Таблицах

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

  • Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! ��
  • Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием — из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Функции для работы с датой и временем в google Таблицах

Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники.

Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время.

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

Функции для работы с датой и временем в google Таблицах

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа.

Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak).

После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY).

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

Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Функции для работы с датой и временем в google Таблицах

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).

Вычисление временных интервалов

Поскольку время в Excel, как было сказано выше, такое же число, как дата, но только дробная его часть, то с временем также возможны любые математические операции, как и с датой — сложение, вычитание и т.д.

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Функции для работы с датой и временем в google Таблицах

Ссылки по теме

6 функций Google Sheet, которые помогают работать с данными

Google Sheet – универсальный инструмент для организации и вычисления данных. Этот облачный сервис – большое преимущество перед Excel. С ним легко наладить совместную работу, автоматизировать данные и не переживать об их возможной утере – изменения сохраняются каждую секунду.

Google Sheets похожи на Excel, многие функции программируются точно так же, как и в привычной электронной таблице. Но есть несколько лайфхаков, которые помогают упростить работу с данными.

Присоединение текста в «Гугл Таблицах»

Иногда в таблицах нужно использовать текст. Может, вам необходимо суммировать некоторые ключевые значения, а может, надо генерировать html-коды.

Знак & объединяет значения ячеек и выводит их в текстовом формате в другой ячейке. Кавычки “” заменяют текст, который вы хотите вставить.

Например, у вас есть такая таблица с ячейками:

Пример объединения текста в «Гугл Таблицах»

Вам нужно, чтобы в одной ячейке появилось общее: «4 ящика и 5 коробок». Вручную писать, если ячеек много – долго, с помощью простой формулы это можно автоматизировать.

Для нашего примера формула: = A1 & «» & B1 & «и» & A2 & «» & B2 объединяет значения. Выделите ячейку, где должна отображаться сводная информация, и введите эту формулу. Готово. В новой ячейке появилось «4 ящика и 5 коробок».

Объединяем разные значения в ячейках таблиц и выводим их в одну

Если вам нужно объединить множество значений, то в помощь – функция JOIN. Укажите, какой символ надо добавить между значениями ячеек, к которым вы хотите присоединиться.

Например, нужно объединить в строку данные со столбцов:

Пример для объединения ячеек в столбце в одну

Пишем в новой ячейке формулу:

Объединение цифровых ячеек в одну

Если вместо знака «−» поставить, к примеру, «+», то результат будет такой:

Ставьте нужный вам знак между кавычками

INDEX: возврат первого или последнего значения в листе «Гугл Таблицы»

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

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

= INDEX (A: A; 1;1) – всегда возвращает первую ячейку в столбце А.

Функция INDEX возвращает значение выбранной ячейки

А если объединить INDEX () и COUNTA (), то будет возвращаться последнее значение в таблице

= INDEX (A:A; COUNTA (A:A); 1)

Импорт данных в «Гугл Таблицы»

«Гугл Таблицы» умеют импортировать данные. Вы можете извлекать нужные вам цифры из электронных таблиц с различных источников, включая XML, HTML, RSS и CSV – удобно для импорта списков сообщений в блогах, каких-либо настроек, списка товарных запасов и так далее. Это можно сделать при помощи функции IMPORTXML:

= IMPORTXML (» https://en.wikipedia.org/wiki/Moon_landing «; «//a/@href»)

Импорт содержимого списка или таблицы

Содержимое списка или таблицы можно импортировать из указанного URL-адреса с помощью такой формулы:

Импорт содержимого ссылок в «Гугл Таблицы»

Импорт CSV-файла

Импорт CSV-файла из указанного URL-адреса:

= IMPORTDATA («ссылка на файл CSV» )

Импорт данных из файла CSV

Импорт данных из RSS или atom-канала

Импортировать данные из RSS-канала или atom тоже не сложно:

Импорт данных из файла RSS или atom

Эти 6 функций помогут вам сделать работу с «Гугл Таблицами» проще и эффективней. Используйте их.

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

В этой статье мы рассмотрим основы работы с датой и временем в MySQL.

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, которая содержит столбцы номера заказа, заказанного товара, даты заказа и даты доставки заказа:

CREATE TABLE `MySampleDB`.`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`)
)
ENGINE = InnoDB;

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

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

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

2008-10-23 10:37:22
20081023103722
2008/10/23 10.37.22
2008*10*23*10*37*22

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

Функция Описание
ADDDATE() Добавляет дату.
ADDTIME() Добавляет время.
CONVERT_TZ() Конвертирует из одного часового пояса в другой.
CURDATE() Возвращает текущую дату.
CURTIME() Возвращает текущее системное время.
DATE_ADD() Добавляет одну дату к другой.
MySQL DATE_FORMAT() Задает указанный формат даты.
DATE() Извлекает часть даты из даты или выражения дата-время.
DATEDIFF() Вычитает одну дату из другой.
DAYNAME() Возвращает день недели.
DAYOFMONTH() Возвращает день месяца (1-31).
DAYOFWEEK() Возвращает индекс дня недели из аргумента.
DAYOFYEAR() Возвращает день года (1-366).
EXTRACT Извлекает часть даты.
FROM_DAYS() Преобразует номер дня в дату.
FROM_UNIXTIME() Задает формат даты в формате UNIX.
MySQL DATE_SUB() Вычитает одну дату из другой.
HOUR() Извлекает час.
LAST_DAY Возвращает последний день месяца для аргумента.
MAKEDATE() Создает дату из года и дня года.
MAKETIME () Возвращает значение времени.
MICROSECOND() Возвращает миллисекунды из аргумента.
MINUTE() Возвращает минуты из аргумента.
MONTH() Возвращает месяц из переданной даты.
MONTHNAME() Возвращает название месяца.
NOW() Возвращает текущую дату и время.
PERIOD_ADD() Добавляет интервал к месяцу-году.
PERIOD_DIFF() Возвращает количество месяцев между двумя периодами.
QUARTER() Возвращает четверть часа из переданной даты в качестве аргумента.
SEC_TO_TIME() Конвертирует секунды в формат ‘ЧЧ:MM:СС’.
SECOND() Возвращает секунду (0-59).
MySQL STR_TO_DATE() Преобразует строку в дату.
SUBTIME() Вычитает время.
SYSDATE() Возвращает время, в которое была выполнена функция.
TIME_FORMAT() Задает формат времени.
TIME_TO_SEC() Возвращает аргумент, преобразованный в секунды.
TIME() Выбирает часть времени из выражения, передаваемого в качестве аргумента.
TIMEDIFF() Вычитает время.
TIMESTAMP() С одним аргументом эта функция возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов.
TIMESTAMPADD() Добавляет интервал к дате-времени.
TIMESTAMPDIFF() Вычитает интервал из даты — времени.
TO_DAYS() Возвращает аргумент даты, преобразованный в дни.
UNIX_TIMESTAMP() Извлекает дату-время в формате UNIX в формат, принимаемый MySQL.
UTC_DATE() Возвращает текущую дату по универсальному времени (UTC).
UTC_TIME() Возвращает текущее время по универсальному времени (UTC).
UTC_TIMESTAMP() Возвращает текущую дату-время по универсальному времени (UTC).
WEEK() Возвращает номер недели.
WEEKDAY() Возвращает индекс дня недели.
WEEKOFYEAR() Возвращает календарную неделю даты (1-53).
YEAR() Возвращает год.
YEARWEEK() Возвращает год и неделю.

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

mysql> SELECT NOW();

  • Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):
    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 дней). Например:

INSERT INTO orders (order_item, order_date, order_delivery)
VALUES ('iPhone 8Gb', NOW(), DATE_ADD(NOW(), INTERVAL 14 DAY));

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

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

mysql> INSERT INTO orders (order_item, order_date, order_delivery) VALUES ('ipod Touch 4Gb', NOW(), DATE_ADD(NOW(), INTERVAL 2 MONTH));

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

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

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

Данная публикация представляет собой перевод статьи «Working with Dates and Times in MySQL» , подготовленной дружной командой проекта Интернет-технологии.ру

MySQL | Функции для работы с датами и временем

Последнее обновление: 26.05.2018

MySQL имеет ряд встроенных функций для работы с датами и временем.

Получение даты и времени

  • Функции NOW(), SYSDATE(), CURRENT_TIMESTAMP() возвращают текущую локальную дату и время на основе системных часов в виде объекта datetime. Все три функции возвращают одинаковый результат SELECT NOW(); — 2018-05-25 21:34:55
    SELECT SYSDATE(); — 2018-05-25 21:34:55
    SELECT CURRENT_TIMESTAMP(); — 2018-05-25 21:32:55
  • Функции CURDATE и CURRENT_DATE возвращают текущую локальную дату в виде объекта date: SELECT CURRENT_DATE(); — 2018-05-25
    SELECT CURDATE(); — 2018-05-25
  • Функции CURTIME и CURRENT_TIME возвращают текущее время в виде объекта time: SELECT CURRENT_TIME(); — 20:47:45
    SELECT CURTIME(); — 20:47:45
  • UTC_DATE возвращает текущую локальную дату относительно GMT SELECT UTC_DATE(); — 2018-05-25
  • UTC_TIME возвращает текущее локальное время относительно GMT SELECT UTC_TIME(); — 17:47:45

Парсинг даты и времени

  • DAYOFMONTH(date) возвращает день месяца в виде числового значения
  • DAYOFWEEK(date) возвращает день недели в виде числового значения
  • DAYOFYEAR(date) возвращает номер дня в году
  • MONTH(date) возвращает месяц даты
  • YEAR(date) возвращает год из даты
  • QUARTER(date) возвращает номер квартала года
  • WEEK(date [, first]) возвращает номер недели года. Необязательный параметр позволяет задать стартовый день недели. Если этот параметр равен 1, то первым днем считается понедельник, иначе воскресенье
  • LAST_DAY(date) возвращает последний день месяца в виде даты
  • DAYNAME(date) возвращает название дня недели
  • MONTHNAME(date) возвращает название текущего месяца
  • HOUR(time) возвращает час времени
  • MINUTE(time) возвращает минуту времени
  • SECOND(time) возвращает секунду времени

Функция EXTRACT

Функция EXTRACT извлекает из даты и времени какой-то определенный компонент. Ее формальный синтаксис:

EXTRACT(unit FROM datetime)

Значение datetime представляет исходную дату и (или) время, а значение unit указывает, какой компонент даты или времени будет извлекаться. Параметр unit может представлять одно из следующих значений:

  • SECOND (секунды)
  • MINUTE (минуты)
  • HOUR (час)
  • DAY (день)
  • MONTH (месяц)
  • YEAR (год)
  • MINUTE_SECOND (минуты и секунды)
  • HOUR_MINUTE (часы и минуты)
  • DAY_HOUR (день и часы)
  • YEAR_MONTH (год и месяц)
  • HOUR_SECOND (часы, минуты и секунды)
  • DAY_MINUTE (день, часы и минуты)
  • DAY_SECOND (день, чаы, минуты и секунды)

Примеры вызова функции:

Функции для манипуляции с датами

Ряд функций позволяют производить операции сложения и вычитания с датами и временем:

  • DATE_ADD(date, INTERVAL expression unit) возвращает объект DATE или DATETIME, который является результатом сложения даты date с определенным временным интервалом. Интервал задается с помощью выражения INTERVAL expression unit, где INTERVAL предоставляет ключевое слово, expression — количество добавляемых к дате единиц, а unit — тип единиц (часы, дни и т.д.) Параметр unit может иметь те же значения, что и в функции EXTRACT, то есть DAY, HOUR и т.д.
  • DATE_SUB(date, INTERVAL expression unit) возвращает объект DATE или DATETIME, который является результатом вычитания из даты date определенного временного интервала
  • DATEDIFF(date1, date2) возвращает разницу в днях между датами date1 и date2
  • TO_DAYS(date) возвращает количество дней с 0-го года
  • TIME_TO_SEC(time) возвращает количество секунд, прошедших с момента полуночи

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

  • DATE_FORMAT(date, format) возвращает объект DATE или DATETIME, отформатированный с помощью шаблона format
  • TIME_FORMAT(date, format) возвращает объект TIME или DATETIME, отформатированный с помощью шаблона format

Обе функции в качестве второго параметра принимают строку форматирования или шаблон, который показывает, как оформатировать значение. Этот шаблон может принимать следующие значения:

  • %m: месяц в числовом формате 01..12
  • %с: месяц в числовом формате 1..12
  • %M: название месяца (January…December)
  • %b: аббревиатура месяца (Jan…Dec)
  • %d: день месяца в числовом формате 00..31
  • %e: день месяца в числовом формате 0..31
  • %D: номер дня месяца с суффиксом (1st, 2nd, 3rd…)
  • %y: год в виде двух чисел
  • %Y: год в виде четырех чисел
  • %W: название дня недели (Sunday…Saturday)
  • %a: аббревиатура дня недели (Sun…Sat)
  • %H: час в формате 00..23
  • %k: час в формате 0..23
  • %h: час в формате 01..12
  • %l: час в формате 1..12
  • %i: минуты в формате 00..59
  • %r: время в 12-ти часовом формате (hh:mm:ss AM или PM)
  • %T: время в 24-ти часовом формате (hh:mm:ss)
  • %S: секунды в формате 00..59
  • %p: AM или PM

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

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

Ваш адрес email не будет опубликован.