Как протянуть дату в гугл таблице

Заполнение таблиц Google (формулы автозаполнения, числа и даты)

В Google Таблицах есть несколько отличных ярлыков, которые помогут вам выполнять работу намного быстрее (и точнее). Одна из таких функций — Заполнение в Google Таблицах .

Заполнить вниз позволяет быстро автоматически заполнять столбец (или строку) на основе ранее существовавшего шаблона.

Например, предположим, что вы хотите быстро получить серию из 100 чисел (например, 1, 2, 3… и так далее). Хотя вы можете сделать это вручную, это пустая трата времени. Вместо этого вы можете использовать заполнение в Google Таблицах, чтобы получить эту серию за несколько секунд.

Ниже приведены несколько примеров, в которых вы можете использовать заполнение в Google Таблицах, чтобы повысить эффективность и ускорить выполнение работы (каждый из этих примеров также подробно рассматривается далее в этом руководстве):

• Получите серию чисел (с указанным пробелом между числами)
• Получить все 12-месячные названия (или названия месяцев и года)
• Скопируйте формулу

Давайте начнем и сначала узнаем, что такое дескриптор заполнения (который делает возможным заполнение), а затем рассмотрим несколько полезных примеров использования заполнения в Google Таблицах.

Что такое маркер заполнения?

Когда вы выбираете ячейку или диапазон ячеек в Google Таблицах, вы заметите, что нижний правый край выделения имеет небольшой квадрат (как показано ниже).

Когда вы наводите курсор на этот маленький синий квадратик, курсор меняется на значок плюса. Это означает, что если вы нажмете левую кнопку мыши (или трекпад), у вас будет выбран маркер заполнения, и вы сможете использовать его для заполнения данных в Google Таблицах.

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

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

Повторить одно и то же значение ячейки в столбце с помощью маркера заполнения

Если вы хотите повторить одно и то же число (или даже текст) в столбце, вы можете легко сделать это в Google Таблицах. Предположим, у вас есть набор данных, как показано ниже, и вы хотите быстро заполнить ячейки A3: A11 тем же значением, что и в A2.

Ниже приведены шаги для этого:

• Выберите ячейку A2
• Поместите курсор на значок маркера заливки (синий квадрат в правом нижнем углу выделения).
• Удерживая левую клавишу мыши (трекпада), перетащите ее в ячейку A11.

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

Совет от профессионалов : если у вас есть данные в соседнем столбце (слева или справа), вы также можете просто дважды щелкнуть значок маркера заполнения (вместо щелчка и перетаскивания). Двойной щелчок сделает то же самое и заполнит столбец до последней заполненной ячейки в соседнем столбце.

Заполните серию чисел

Если вы хотите быстро добавить ряд чисел в столбец, вы можете сделать это за секунды, используя «Заполнить вниз». Хороший вариант использования этого может быть, когда вы хотите заполнить ряд чисел (1, 2, 3… и так далее). Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите получить серийные номера в столбце A.

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

• Введите 1 в ячейку A2 и 2 в ячейку A3.
• Выделите обе ячейки A2 и A3.
• Поместите курсор на значок маркера заливки (синий квадрат в правом нижнем углу выделения).
• Удерживая левую клавишу мыши (или трекпада), перетащите ее в ячейку A11.

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

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

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

Если вы используете 1 и 3 в ячейках A2 и A3 соответственно, использование дескриптора заполнения даст вам серию нечетных чисел (поскольку шаблон должен сохранять разницу в 2 в числах).

Использование маркера заполнения для заполнения чисел также работает, когда у вас есть текст вместе с числом. Например, вместо того, чтобы просто использовать номер, если вы используете # 1 и # 2 и в первых двух ячейках, а затем заполните их, он сможет распознать шаблон и дать вам # 3, # 4 и так далее.

Заполните месяцы (или месяцы и годы)

Как и числа, вы также можете использовать дескриптор заполнения, чтобы быстро получить названия месяцев (или названия месяца и года). Предположим, у вас есть набор данных, как показано ниже, и вы хотите быстро заполнить название месяца и значение года в столбце A.

Ниже приведены шаги для заполнения значения месяца и года с помощью дескриптора заполнения:

• Введите «Январь 2019» в ячейку A2 и «Февраль 2019» в ячейку A3.
• Выделите обе ячейки A2 и A3.
• Поместите курсор на значок маркера заливки (синий квадрат в правом нижнем углу выделения).
• Удерживая левую клавишу мыши (трекпада), перетащите ее в ячейку A11.

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

Обратите внимание, что для этого Google Таблицы должны распознавать запись в ячейке как дату. Например, если вы введете «Янв 2019» или «Январь 2019», Google Таблицы распознают их как допустимые форматы даты. Но если вы введете что-то еще (например, Я 2019), и если Google Таблицы не распознают это как дату, он не сможет заполнить и следовать шаблону.

Если вы используете только название месяца (например, январь, февраль и т. Д.), А затем заполните его, Google Таблицы предоставят вам месяцы до декабря, а затем повторите название месяца, начиная с января. Если у вас есть оба месяца имя и значение года, хотя название месяца повторяется, год увеличивается.

Заполнить формулу (применить ко всему столбцу)

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

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

Ниже приведены шаги по заполнению формулы в Google Таблицах:

• Выберите ячейку C2
• Поместите курсор на значок маркера заливки (синий квадрат в правом нижнем углу выделения).
• Удерживая левую кнопку мыши (трекпад), перетащите ее вниз до ячейки C13 (вы также можете дважды щелкнуть по правому нижнему синему квадрату, и он заполнит ячейки)

Обратите внимание, что для того, чтобы это работало, ваша формула не должна использовать ссылку на заблокированную ячейку. Таким образом, формула может быть = B2 * 1,1 или = $ B2 * 1,1 (но не может быть = $ B $ 2 * 1,1 или = B $ 2 * 1,1).

Если перед номером строки стоит знак доллара, это не позволит формуле адаптироваться к номеру строки при заполнении.

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

Так же, как вы можете использовать дескриптор заполнения для заполнения (или заполнения) в Google Таблицах, вы также можете использовать его для заполнения строк.

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

Предположим, у вас есть набор данных, показанный ниже, и вы хотите заполнить строку, указав номер недели.

Ниже приведены шаги для заполнения строк с помощью дескриптора заполнения в Google Таблицах:

Как заполнить столбец с последовательными датами в Google Sheets

В Excel вы можете щелкнуть правой кнопкой мыши и перетащить дату вниз, чтобы заполнить столбец следующими датами в последовательности. Например, если вы введете 01.09.2014, выберите эту ячейку, а затем щелкните правой кнопкой мыши на ячейках ниже, Excel заполнит эти ячейки 10/1/2014, 11/1/2014 и т. Д. Это не работает с одним выбором ячеек в Google Sheets — как я могу заставить это работать?

Ключ заключается в том, чтобы ввести как минимум первые два значения, чтобы автоматическое заполнение работало с шаблоном

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

Однако сначала это не сработало, потому что в моем Google Листе был неправильный языковой стандарт, а в ячейках было неправильное форматирование.

Установите локаль для своего Google Sheet:

Меню Файл → Настройки электронной таблицы . → Установить правильный языковой стандарт

Настройте правильный формат даты для ячеек:

1. Выберите все ячейки или столбец, который должен содержать даты
2. Меню «Формат» → «Числа» → «Дата» (если требуемый формат даты уже доступен. Если нет, перейдите в меню, выполнив следующие действия).
3. → Больше форматов → Больше форматов даты и времени .
4. Либо выберите предварительно определенный формат даты из списка, либо настройте свой собственный

Теперь заполните столбец с последовательными датами:

Это уже объяснялось в других ответах, но для полноты я объясню это здесь снова.

1. Вручную напишите две последовательные даты в две ячейки
2. Выделите обе ячейки (нажмите первую, затем нажмите вторую, удерживая клавишу Shift).
3. В правом нижнем углу синего прямоугольника выбора вы увидите маленький квадрат. Перетащите этот квадрат и отметьте все ячейки, которые вы хотите заполнить последовательными датами

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В данном примере мы ссылаемся на ячейку 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):

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

27.10.2012 16:45

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 дней назад:

Как добавить текущую дату и время в Google Таблицы

Вместо того, чтобы вручную вставлять время и дату в электронную таблицу Google Sheets, вы можете использовать функции СЕЙЧАС и СЕГОДНЯ. Эти функции показывают текущее время или дату, обновляясь при изменении вашей таблицы или на регулярной основе.

Хотя функции СЕЙЧАС и СЕГОДНЯ регулярно обновляются, вы можете быстро вставить необновляющуюся отметку времени или даты с помощью сочетаний клавиш.

Добавление текущего времени и даты с помощью NOW

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

Для начала откройте свой Google Таблицы электронную таблицу или создайте новую, щелкните пустую ячейку и введите = СЕЙЧАС ().

После вставки ваша электронная таблица Google Sheets должна по умолчанию использовать стандартное форматирование для формул NOW, которая отображает метку времени с текущим временем и датой.

Google Таблицы также по умолчанию будут использовать форматирование даты и времени, соответствующее вашему языку, которое вы можете изменить в настройках Google Таблиц, нажав Файл> Настройки электронной таблицы. В приведенном выше примере используется формат даты в Великобритании (ДД / ММ / ГГ).

Обычно метка времени, созданная формулой, использующей функцию СЕЙЧАС, обновляется только при изменении вашей электронной таблицы. Вы можете изменить настройки электронной таблицы, чтобы они обновлялись каждую минуту или каждый час дополнительно.

Для этого войдите в настройки Google Таблиц (Файл> Настройки электронной таблицы), щелкните вкладку «Расчет», а затем выберите частоту обновления в раскрывающемся меню «Пересчет».

Определение даты с помощью функции СЕГОДНЯ

Если вы хотите отображать только текущую дату, вы можете использовать функцию СЕГОДНЯ как альтернативу СЕЙЧАС. Формулы, использующие функцию СЕГОДНЯ, обычно отображают даты в формате ДД / ММ / ГГ или ММ / ДД / ГГ, в зависимости от вашего языкового стандарта.

Как и СЕЙЧАС, функция СЕГОДНЯ не имеет аргументов. Щелкните пустую ячейку и введите = СЕГОДНЯ (), чтобы вставить текущую дату.

Ячейки с формулой СЕГОДНЯ будут обновляться каждый день. Вы можете изменить форматирование, чтобы использовать текст или числа, если хотите.

Форматирование формулы СЕЙЧАС или СЕГОДНЯ

Как мы показали, функция СЕЙЧАС обычно по умолчанию показывает временную метку, которая отображает время и дату.

Если вы хотите это изменить, вам нужно будет изменить форматирование любых ячеек с помощью функции СЕЙЧАС. Таким же образом можно изменить формат любой формулы, используя функцию СЕГОДНЯ.

Чтобы отобразить только текущую дату, выберите свою ячейку (или ячейки) и нажмите Формат> Число> Дата. Чтобы отобразить текущее время без даты, выберите «Формат»> «Число»> «Время».

Вставка статического времени или даты в Google Таблицы

Если вы хотите добавить текущее время или дату в электронную таблицу Google Таблиц, но не хотите, чтобы они обновлялись, вы не можете использовать СЕЙЧАС или СЕГОДНЯ. Вместо этого вам придется использовать сочетание клавиш.

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

Чтобы вставить текущее время, нажмите Ctrl + Shift +: (двоеточие) на клавиатуре.