Операции над датами и временем в sql — для студента

Привет, сегодня я покажу, как в T-SQL из даты можно получить определенную часть этой даты, например, год, месяц, день и даже час, иными словами, в данном материале мы ответим на несколько вопросов, которые связаны с извлечением данных из значения, содержащего дату.

Как в T-SQL получить текущую дату?

Для начала давайте я расскажу о том, как в Microsoft SQL Server можно получить значение текущей даты.

Для получения текущей даты в Microsoft SQL Server существует несколько специальных системных функций. Давайте некоторые из этих функций рассмотрим.

• GETDATE – функция возвращает значение, которое содержит дату и время компьютера, на котором запущен экземпляр Microsoft SQL Server, при этом смещение часового пояса не включается. Лично мне достаточно часто приходится пользоваться именно этой функцией;
• CURRENT_TIMESTAMP – эта функция эквивалентна функции GETDATE, она возвращает точно такое же значение. Вы можете использовать любую функцию, но как я уже сказал, лично я отдаю предпочтение функции GETDATE;
• SYSDATETIME – данная функция также возвращает дату и время компьютера, на котором запущен экземпляр Microsoft SQL Server, смещение часового пояса тоже не включается. Но в данном случае функция возвращает значение с более высокой точностью в долях секунды.

Примечание! Для того чтобы получить значение даты и времени с учетом смещения часового пояса, необходимо использовать функцию SYSDATETIMEOFFSET, а для того чтобы получить значение даты и времени в формате UTC функции GETUTCDATE или SYSUTCDATETIME.

Пример – получение текущей даты в Microsoft SQL Server

В данном примере мы вызовем три функции получения текущей даты.

SELECT GETDATE() AS [GETDATE],
CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],
SYSDATETIME() AS [SYSDATETIME]

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

Как получить год из даты в T-SQL?

Если у Вас возникла необходимость из даты получить год, то есть, например, из 01.01.2019 получить 2019 в виде отдельного значения или просто из текущей даты получить год, то в Microsoft SQL Server Вы это можете сделать несколькими способами.

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

DATEPART принимает два параметра: первый, datepart, т.е. какую часть даты нам нужно вернуть, второй, дата, которую необходимо обработать.

Пример – получаем год из даты в Microsoft SQL Server

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

Чтобы DATEPART нам вернула год из даты, первым параметром нам необходимо передать значение, характеризующее часть «год», допустимо передавать следующие значения: year, yyyy или yy.

—Объявляем переменную для хранения даты
DECLARE @TestDate DATETIME
—Присваиваем значение переменной (текущая дата)
SET @TestDate = GETDATE()

—Запрос SELECT
SELECT @TestDate AS [Дата],

—Передаем переменную в качестве параметра
YEAR(@TestDate) AS [Год YEAR],
DATEPART(YY, @TestDate) AS [Год DATEPART],

—Передаем выражение, приводящее к типу DATE
YEAR('01.01.2019') AS [Год YEAR],

—В качестве параметра указываем функцию
DATEPART(YY, GETDATE()) AS [Год DATEPART]

Как получить месяц из даты в T-SQL?

В T-SQL из даты можно получить и номер месяца, для этого можно использовать функцию MONTH, она возвращает целое число, представляющее месяц указанной даты или все ту же функцию DATEPART, в которую, в данном случае необходимо будет передать значение, характеризующее часть даты «месяц», можно использовать: month, mm или m.

Пример – получаем месяц из даты в Microsoft SQL Server

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

—Объявляем переменную для хранения даты
DECLARE @TestDate DATETIME
—Присваиваем значение переменной (текущая дата)
SET @TestDate = GETDATE()

—Запрос SELECT
SELECT @TestDate AS [Дата],

—Передаем переменную в качестве параметра
MONTH(@TestDate) AS [Месяц MONTH],
DATEPART(MM, @TestDate) AS [Месяц DATEPART],

—Передаем выражение, приводящее к типу DATE
MONTH('01.01.2019') AS [Месяц MONTH],

—В качестве параметра указываем функцию
DATEPART(MM, GETDATE()) AS [Месяц DATEPART]

Как из даты получить день в T-SQL?

Для того чтобы получить из даты день, в T-SQL можно использовать функцию DAY – это функция возвращает целое число, представляющее день указанной даты. Также можно использовать и уже знакомую функцию DATEPART со значением первого параметра: day, dd или d.

Пример – получаем день из даты в Microsoft SQL Server

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

—Объявляем переменную для хранения даты
DECLARE @TestDate DATETIME
—Присваиваем значение переменной (текущая дата)
SET @TestDate = GETDATE()

—Запрос SELECT
SELECT @TestDate AS [Дата],

—Передаем переменную в качестве параметра
DAY(@TestDate) AS [День DAY],
DATEPART(DD, @TestDate) AS [День DATEPART],

—Передаем выражение, приводящее к типу DATE
DAY('01.01.2019') AS [День DAY],

—В качестве параметра указываем функцию
DATEPART(DD, GETDATE()) AS [День DATEPART]

Как из даты получить час в T-SQL?

Чтобы из даты получить час, мы можем использовать функцию DATEPART со значением hour или hh. Только в данном случае второй параметр (date), в котором мы передаем значение даты, должен обязательно содержать время, т.е. иметь тип данных DATETIME, тип DATE не допускается.

Пример – получаем час из даты в Microsoft SQL Server

В этом примере мы из даты получаем час.

—Объявляем переменную для хранения даты
DECLARE @TestDate DATETIME
—Присваиваем значение переменной (текущая дата)
SET @TestDate = GETDATE()

—Запрос SELECT
SELECT @TestDate AS [Дата],

—Передаем переменную в качестве параметра
DATEPART(HH, @TestDate) AS [Час],

—В качестве параметра указываем функцию
DATEPART(HH, GETDATE()) AS [Час]

У меня все, надеюсь, перечисленные выше примеры помогут Вам в решении Ваших задач. Начинающим программистам рекомендую почитать книгу «Путь программиста T-SQL», в ней я рассказываю про все основные конструкции языка T-SQL, с помощью нее Вы научитесь работать с языком T-SQL с нуля.

Пока!

Источник: https://info-comp.ru/obucheniest/698-get-year-from-date-in-t-sql.html

SQL Работа с датами

Только для читателей Lifeexample возможно открыть интернет-магазин на Moguta.CMS со скидкой в 15%

>

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

“Время — ткань, из которой состоит жизнь” сказал Бенджамин Франклин. Интерпретируя данное высказывание в сферу программирования, получим “Время – то, что делает наши приложения живыми“. Работа со временем и датой, открывает новые возможности для простых скриптов.

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

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

Как получить текущую дату в SQL

1 вариант:

2 вариант:

1	WHERE date = STR_TO_DATE(now(), '%Y-%m-%d')

Прибавить к дате один час в SQL

1	DATE_ADD('2013-03-30', INTERVAL 1 HOUR)

Прибавить к дате один день в SQL

1	DATE_ADD('2013-03-30', INTERVAL 1 DAY)

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

Прибавить к дате один месяц в SQL

1	DATE_ADD('2013-03-30', INTERVAL 1 MONTH)

Аналогично можно прибавлять любое количество месяцев к текущей дате.

Получить вчерашний день в SQL

Первый вариант:

1	DATE_ADD(CURDATE(), INTERVAL -1 DAY)

Второй вариант:

1	DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Получить дату начала текущей недели в SQL

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

1	CURDATE()-WEEKDAY(CURDATE());

Получить выборку с этого понедельника по текущий день недели в SQL

WHERE ( date BETWEEN (CURDATE()-WEEKDAY(CURDATE())) AND CURDATE() )

Получить выборку с первого числа текущего месяца по текущий день недели в SQL

WHERE ( date BETWEEN (CURDATE()-WEEKDAY(CURDATE()))  AND CURDATE())

Как получить дату рождения пользователя в SQL

SELECT name, birth, CURRENT_DATE,      (YEAR(CURRENT_DATE)-YEAR(birth))      — (RIGHT(CURRENT_DATE,5)

Источник: http://LifeExample.ru/razrabotka-i-optimizacia-saita/sql-rabota-s-datami.html

Что делают в SQL текущая дата и другие функции даты и времени

Функция текущей даты SQL CURDATE() и её аналоги CURRENT_DATE() и CURRENT_DATE среди других функций даты и времени применяются наиболее часто из-за широких возможностей, обеспечиваемых ими для анализа данных.

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

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

Функция текущей даты CURDATE() возвращает значение текущей даты в формате 'YYYY-MM-DD' и 'YYYYDDMM'. Вычисляя несколькими способами (их как раз и разберём в этом параграфе) разницу значений дат, можно определить такие важные значения, как возраст человека, его трудовой стаж, продолжительность различных процессов и явлений и многое другое.

В примерах работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Это уже база с большим объёмом данных по сравнению с примерами ко многим другим темам нашего курса. Поэтому не будем приводить строки данных таблиц и таблицы результатов запросов. Однако это будет компенсировано подробным разбором логики построения запросов, которые, надо признать, имеют достаточно высокую сложность.

Пример 1. Сформировать список актеров старше 70 лет. Пишем следующий запрос:

SELECT FName, LName, BirthDate FROM ACTOR WHERE TIMESTAMPDIFF(YEAR, BirthDate, CURDATE()) > 70

В этом запросе вычисляется разница между текущей датой CURDATE() и датой рождения актёра BirthDate, содержащейся в таблице ACTOR. Для вычисления разницы применена функция TIMESTAMPDIFF(). Ключевое слово YEAR — задаёт единицу измерения — в годах интервала между датами.

Вычисленное значение и результат его сравнения с числом 70 вполне пригодны в качестве условия выборки в секции WHERE. Следует учесть, что функция TIMESTAMPDIFF() существует лишь в MySQL.

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

Для вычисления разницы дат можно использовать и оператор «минус». Это сделано в следующем примере.

Пример 2. Вывести список актеров, которые не задействованы в новых постановках (в постановках последних 3 лет). Использовать CURDATE(), NOT IN. Запрос будет следующим:

В этом запросе задействована функция YEAR(). Аргументами её являются дата премьеры постановки premieredate из таблицы play и текущая дата. Для вычисления разницы использован оператор «минус».

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

Этот подзапрос — самый глубокий в «матрёшке» целого запроса. Подзапрос более высокого уровня из таблицы team, содержащей данные о ролях, выбирает идентификаторы актёров, с помощью предиката IN. Выбираются те актёры, которые участвовали в постановках трёх послежних лет.

И, наконец, самый внешний запрос к таблице actor выбирает значения с отрицанием (NOT IN) значения предыдущего подзапроса.

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

Пример 3. Сформировать список актеров с их стажем (в днях). Использовать CURDATE(), GROUP BY. Запрос будет следующим:

SELECT DISTINCT a.Actor_ID, a.FName, a.LName, CURDATE() — p1.PremiereDate AS ExpDays FROM Play p1 JOIN Team t1 ON p1.play_id = t1.play_id JOIN Actor a ON t1.actor_id = a.Actor_id WHERE t1.ACTOR_ID = a.Actor_ID ORDER BY ExpDays, a.Actor_ID DESC

В этом запросе разница между текущей датой CURDATE() и датой премьеры постановки PremiereDate из таблицы Play вычисляется как имя столбца в результирующей таблице. Поскольку эти даты имеют один и тот же формат, для вычисления разницы достаточно использовать оператор «минус». Разница вычислена.

Соединение таблиц Team и Actor — второе в этой цепочке из трёх таблиц.

Составить SQL запросы с текущей датой самостоятельно, а затем посмотреть решения

Пример 4. Определить самого востребованного актера за последние 5 лет. Оператор JOIN использовать 2 раза. Использовать CURDATE(), LIMIT 1.

Правильное решение и комментарий.

Функции, возвращающие текущие дату, время, дату и время

• CURDATE(), CURRENT_DATE(), CURRENT_DATE — возвращают текущую дату в формате 'YYYY-MM-DD' или YYYYDDMM в зависимости от того, вызывается функция в текстовом или числовом контексте.
• CURTIME(), CURRENT_TIME(), CURRENT_TIME — возвращают текущее время суток в формате 'hh-mm-ss' или hhmmss в зависимости от того, вызывается функция в текстовом или числовом контексте.
• NOW() — возвращает текущие дату и время формате 'YYYY-MM-DD hh:mm:ss' или YYYYDDMMhhmmss в зависимости от того, вызывается функция в текстовом или числовом контексте.

Функции для вычисления разницы между моментами

TIMEDIFF(param1, param2) — возвращает разницу между значениями времени, заданными параметрами param1 и param2.

DATEDIFF(param1, param2) — возвращает разницу между датами param1 и param2. Значения param1 и param2 могут иметь типы DATE или DATETIME, а при вычислении разницы используется лишь часть DATE.

PERIOD_DIFF(param1, param2) — возвращает разницу в месяцах между датами param1 и param2. Значения param1 и param2 могут быть представлены в числовом формате YYYYMM или YYMM.

TIMESTAMPDIFF(interval, param1, param2) — возвращает разницу между значениями датами param1 и param2.

Значения param1 и param2 могут быть представлены в форматах 'YYYY-MM-DD' или 'YYYY-MM-DD hh:mm:ss'. Единица измерения разницы задаётся параметром interval.

Он может принимать значения FRAC_SECOND (микросекунды), SECOND (секунды), MINUTE (минуты), HOUR (часы), DAY (дни), WEEK (недели), MONTH (месяцы), QUARTER (кварталы), YEAR (годы).

Функции для добавления (или вычитания) некоторого значения к моменту

ADDDATE(date, INTERVAL value) — возвращает дату, к которой прибавлено значение value. Ключевое слово INTERVAL обязательно следует в запросе, после него указывается значение value, а затем единицы измерения прибавляемого значения.

Ими могут быть SECOND (секунды), MINUTE (минуты), HOUR (часы), MINUTE_SECOND (минуты и секунды), HOUR_MINUTE (часы и минуты), DAY_SECOND (дни, часы минуты и секунды), DAY_MINUTE (дни, часы и минуты), DAY_HOUR (дни и часы), YEAR_MONTH (годы и месяцы).

SUBDATE(date, INTERVAL value) — вычитает из величины даты date произвольный временной интервал и возвращает результат. Ключевое слово INTERVAL обязательно следует в запросе, после него указывается значение value, а затем единицы измерения вычитаемого значения. Возможные единицы измерения — те же, что и для функции ADDDATE().

SUBTIME(datetime, time) — вычитает из величины времени datetime вида 'YYYY-MM-DD hh:mm:ss' произвольно заданное значение времени time и возвращает результат.

PERIOD_ADD(period, N) — добавляет N месяцев к значению даты period. Значение period должно быть представлено в числовом формате 'YYYYMM' или 'YYMM'.

TIMESTAMPADD(interval, param1, param2) — прибавляет к дате и времени суток param2 в полном или кратком формате временной интервал param1, единицы измерения которого заданы параметром interval. Возможные единицы измерения — те же, что и для функции TIMESTAMPDIFF().

Функции, характеризующие момент (значение аргумента)

1. DATE(datetime) — извлекает из значения даты и времени суток в формате DATETIME ('YYYY-MM-DD hh:mm:ss') только дату, отсекая часы, минуты и секунды.
2. TIME(datetime) — извлекает из значения даты и времени суток в формате DATETIME ('YYYY-MM-DD hh:mm:ss') только время суток, отсекая дату.

3. TIMESTAMP(param) — принимает в качестве аргумента дату и время суток в полном или кратком формате и возвращает полный вариант в формате DATETIME ('YYYY-MM-DD hh:mm:ss').
4. DAY(date), DAYOFMONTH(date) — принимают в качестве аргумента дату, и возвращают порядковый номер дня в месяце (от 1 до 31).

5. DAYNAME(date) — принимает в качестве аргумента дату, и возвращает день недели в виде полного слова на английском языке.
6. DAYOFWEEK(date) — принимает в качестве аргумента дату, и возвращает порядкоый номер дня недели от 1 (воскресенье) до 7 (суббота).

7. WEEKDAY(date) — принимает в качестве аргумента дату, и возвращает порядкоый номер дня недели от 0 (понедельник) до 6 (воскресенье).
8. WEEK(date) — принимает в качестве аргумента дату, и возвращает номер недели в году для этой даты от 0 до 53.

9. WEEKOFYEAR(datetime) — возвращает порядковый номер недели в году для даты datetime от 1 до 53.
10. MONTH(datetime) — возвращает числовое значение месяца года от 1 до 12 для даты datetime.
11. MONTHNAME(datetime) — возвращает строку с названием месяца для даты datetime.

12. QUARTER(datetime) — возвращает значение квартала от 1 до 4 для даты datetime, которая может быть передана в формате 'YYYY-MM-DD' или 'YYYY-MM-DD hh:mm:ss'.
13. YEAR(datetime) — возвращает год от 1000 до 9999 для даты datetime.

14. DAYOFYEAR(date) — возвращает порядковый номер дня в году от 1 до 366 для даты date.
15. HOUR(datetime) — возвращает значение часа от 0 до 23 для времени datetime.
16. MINUTE(datetime) — возвращает значение минут от 0 до 59 для времени datetime.

17. SECOND(time) — возвращает количество секунд для времени суток time, которое задаётся либо в виде строки 'hh:mm:ss', либо числа hhmmss.

EXTRACT(type FROM datetime) — принимает дату и время суток datetime и возвращает часть, определяемую параметром type. Значениями параметра могут быть YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

Функции для преобразования разницы в дни и секунды

TO_DAYS(date) — принимает дату date в кратком 'YYYY-MM-DD' или полном формате 'YYYY-MM-DD hh:mm:ss' и возвращает количество дней, прошедших с нулевого года.

FROM_DAYS(N) — принимает количество дней N, прошедших с нулевого года, и возвращает дату в формате 'YYYY-MM-DD'.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(datetime) — если параметр не указан, то возвращает количество секунд, прошедших с 00:00 1 января 1970 года. Если параметр datetime указан (в кратком 'YYYY-MM-DD' или полном формате 'YYYY-MM-DD hh:mm:ss'), то возвращает разницу в секундах между 00:00 1 января 1970 года и датой datetime.

• FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp, format) — принимает количество секунд, прошедших с 00:00 1 января 1970 года и возвращает дату и время суток в виде строки 'YYYY-MM-DD hh:mm:ss' или в виде числа YYYYDDMMhhmmss в зависимости от того, вызвана функция в строковом или числовом контексте.
• TIME_TO_SEC(time) — принимает время суток time в формате 'hh:mm:ss' и возвращает количество секунд, прошедших с начала суток.
• SEC_TO_TIME(seconds) — принимает количество секунд seconds, прошедших с начала суток и возвращает время в формате 'hh:mm:ss' или hhmmss в зависимости от того, вызвана функция в строковом или числовом контексте.
• MAKEDATE(year, dayofyear) — принимает год year, номер дня в году dayofyear и возвращает дату в формате 'YYYY-MM-DD'.
• MAKETIME(hour, minute, second) — принимает часы hour, минуты minute и секунды second и возвращает время суток в формате 'hh:mm:ss'.
• Поделиться с друзьями
• Реляционные базы данных и язык SQL

Источник: https://function-x.ru/sql_current_date.html

MS SQL Server и T-SQL | Функции по работе с датами и временем

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

• GETDATE: возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime SELECT GETDATE() — 2017-07-28 21:34:55.830
• GETUTCDATE: возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта datetime SELECT GETUTCDATE() — 2017-07-28 18:34:55.830
• SYSDATETIME: возвращает текущую локальную дату и время на основе системных часов, но отличие от GETDATE состоит в том, что дата и время возвращаются в виде объекта datetime2 SELECT SYSDATETIME() — 2017-07-28 21:02:22.7446744
• SYSUTCDATETIME: возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта datetime2 SELECT SYSUTCDATETIME() — 2017-07-28 18:20:27.5202777
• SYSDATETIMEOFFSET: возвращает объект datetimeoffset(7), который содержит дату и время относительно GMT SELECT SYSDATETIMEOFFSET() — 2017-07-28 21:02:22.7446744 +03:00
• DAY: возвращает день даты, который передается в качестве параметра SELECT DAY(GETDATE()) — 28
• MONTH: возвращает месяц даты SELECT MONTH(GETDATE()) — 7
• YEAR: возвращает год из даты SELECT YEAR(GETDATE()) — 2017
• DATENAME: возвращает часть даты в виде строки. Параметр выбора части даты передается в качестве первого параметра, а сама дата передается в качестве второго параметра: SELECT DATENAME(month, GETDATE()) — July Для определения части даты можно использовать следующие параметры (в скобках указаны их сокращенные версии):
  • year (yy, yyyy): год
  • quarter (qq, q): квартал
  • month (mm, m): месяц
  • dayofyear (dy, y): день года
  • day (dd, d): день месяца
  • week (wk, ww): неделя
  • weekday (dw): день недели
  • hour (hh): час
  • minute (mi, n): минута
  • second (ss, s): секунда
  • millisecond (ms): миллисекунда
  • microsecond (mcs): микросекунда
  • nanosecond (ns): наносекунда
  • tzoffset (tz): смешение в минутах относительно гринвича (для объекта datetimeoffset)

• DATEPART: возвращает часть даты в виде числа. Параметр выбора части даты передается в качестве первого параметра (используются те же параметры, что и для DATENAME), а сама дата передается в качестве второго параметра:

  SELECT DATEPART(month, GETDATE()) — 7

• DATEADD: возвращает дату, которая является результатом сложения числа к определенному компоненту даты. Первый параметр представляет компонент даты, описанный выше для функции DATENAME. Второй параметр — добавляемое количество. Третий параметр — сама дата, к которой надо сделать прибавление:

  SELECT DATEADD(month, 2, '2017-7-28') — 2017-09-28 00:00:00.000
  SELECT DATEADD(day, 5, '2017-7-28') — 2017-08-02 00:00:00.000
  SELECT DATEADD(day, -5, '2017-7-28') — 2017-07-23 00:00:00.000

  Если добавляемое количество представляет отрицательное число, то фактически происходит уменьшение даты.

• DATEDIFF: возвращает разницу между двумя датами. Первый параметр — компонент даты, который указывает, в каких единицах стоит измерять разницу. Второй и третий параметры — сравниваемые даты:

  SELECT DATEDIFF(year, '2017-7-28', '2018-9-28') — разница 1 год
  SELECT DATEDIFF(month, '2017-7-28', '2018-9-28') — разница 14 месяцев
  SELECT DATEDIFF(day, '2017-7-28', '2018-9-28') — разница 427 дней

• TODATETIMEOFFSET: возвращает значение datetimeoffset, которое является результатом сложения временного смещения с другим объектом datetimeoffset

  SELECT TODATETIMEOFFSET('2017-7-28 01:10:22', '+03:00')

• SWITCHOFFSET: возвращает значение datetimeoffset, которое является результатом сложения временного смещения с объектом datetime2

  SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:30')

• EOMONTH: возвращает дату последнего дня для месяца, который используется в переданной в качестве параметра дате.

  SELECT EOMONTH('2017-02-05') — 2017-02-28
  SELECT EOMONTH('2017-02-05', 3) — 2017-05-31

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

• DATEFROMPARTS: по году, месяцу и дню создает дату

  SELECT DATEFROMPARTS(2017, 7, 28) — 2017-07-28

• ISDATE: проверяет, является ли выражение датой. Если является, то возвращает 1, иначе возвращает 0.

  SELECT ISDATE('2017-07-28') — 1
  SELECT ISDATE('2017-28-07') — 0
  SELECT ISDATE('28-07-2017') — 0
  SELECT ISDATE('SQL') — 0

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

CREATE TABLE Orders
(
Id INT IDENTITY PRIMARY KEY,
ProductId INT NOT NULL,
CustomerId INT NOT NULL,
CreatedAt DATE NOT NULL DEFAULT GETDATE(),
ProductCount INT DEFAULT 1,
Price MONEY NOT NULL
);

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

Другой пример — найдем заказы, которые были сделаны 16 дней назад:

Источник: https://metanit.com/sql/sqlserver/8.3.php

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

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

SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) — TO_DAYS(date_col) 3

WEEKDAY(date) — Возвращает индекс дня недели для аргумента date (0 =понедельник, 1 = вторник, … 6 = воскресенье):

SELECT WEEKDAY('1998-02-03 22:23:00'); // -> 1
SELECT WEEKDAY('1997-11-05'); // -> 2

DAYOFMONTH(date) — Возвращает порядковый номер дня месяца для аргумента date в диапазоне от 1 до 31:

SELECT DAYOFMONTH('1998-02-03'); // -> 3

DAYOFYEAR(date) — Возвращает порядковый номер дня года для аргумента date в диапазоне от 1 до 366:

SELECT DAYOFYEAR('1998-02-03'); // -> 34

MONTH(date) — Возвращает порядковый номер месяца в году для аргумента date в диапазоне от 1 до 12:

SELECT MONTH('1998-02-03'); // -> 2

DAYNAME(date) — Возвращает название дня недели для аргумента date:

SELECT DAYNAME(«1998-02-05»); // -> 'Thursday'

MONTHNAME(date) — Возвращает название месяца для аргумента date:

SELECT MONTHNAME(«1998-02-05»); // -> 'February'

QUARTER(date) — Возвращает номер квартала года для аргумента date в диапазоне от 1 до 4:

SELECT QUARTER('98-04-01'); // -> 2

WEEK(date), WEEK(date,first) — При наличии одного аргумента возвращает порядковый номер недели в году для date в диапазоне от 0 до 53 (да, возможно начало 53-й недели) для регионов, где воскресенье считается первым днем недели. Форма WEEK() с двумя аргументами позволяет уточнить, с какого дня начинается неделя — с воскресенья или с понедельника. Результат будет в пределах 0-53 или 1-52.

• Вот как работает второй аргумент:
  0 — Неделя начинается с воскресенья; возвращаемое значение — в промежутке 0-53
  1 — Неделя начинается с понедельника; возвращаемое значение — в промежутке 0-53
  2 — Неделя начинается с воскресенья; возвращаемое значение — в промежутке 1-53
• 3 — Неделя начинается с понедельника; возвращаемое значение — в промежутке 1-53 (ISO 8601)

SELECT WEEK('1998-02-20'); // -> 7
SELECT WEEK('1998-02-20',0); // -> 7
SELECT WEEK('1998-02-20',1); // -> 8
SELECT WEEK('1998-12-31',1); // -> 53

Примечание: в версии 4.0 функция WEEK(#,0) была изменена с целью соответствия календарю США. Заметьте, если неделя является последней неделей прошлого года, MySQL вернет 0 если вы не указали 2 или 3 как опциональный аргумент:

SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); // -> 2000, 0
SELECT WEEK('2000-01-01',2); // -> 52

Можно считать, что MySQL должен вернуть 52, так как данная дата и является 52-ой неделей года 1999. Мы решили возвращать 0, так как мы хотим, чтобы функция давала «номер недели в указанном году». Это делает функцию WEEK() более надежной при использовании совместно с другими функциями, которые вычисляют части дат.

Если вам все же важно уточнить корректную неделю в году, тогда вы можете использовать 2 или 3 как опциональный аргумент или использовать YEARWEEK()

SELECT YEARWEEK('2000-01-01'); // -> 199952
SELECT MID(YEARWEEK('2000-01-01'),5,2); // -> 52

YEAR(date) — Возвращает год для аргумента date в диапазоне от 1000 до 9999:

SELECT YEAR('98-02-03'); // -> 1998

YEARWEEK(date), YEARWEEK(date,first) — Возвращает год и неделю для аргумента date. Второй аргумент в данной функции работает подобно второму аргументу в функции WEEK(). Следует учитывать, что год может отличаться от указанного в аргументе date для первой и последней недель года:

SELECT YEARWEEK('1987-01-01'); // -> 198653
Обратите внимание, что номер недели отличается от того, который возвращает функция WEEK() (0), будучи вызванной с опциональным аргументом 0 или 1. Это потому, что WEEK() возвращает номер недели именно в указанном году.

HOUR(time)- Возвращает час для аргумента time в диапазоне от 0 до 23:

SELECT HOUR('10:05:03'); //-> 10

MINUTE(time) — Возвращает количество минут для аргумента time в диапазоне от 0 до 59:

SELECT MINUTE('98-02-03 10:05:03'); // -> 5

SECOND(time) — Возвращает количество секунд для аргумента time в диапазоне от 0 до 59:

SELECT SECOND('10:05:03'); // -> 3

PERIOD_ADD(P,N) — Добавляет N месяцев к периоду P (в формате YYMM или YYYYMM). Возвращает величину в формате YYYYMM. Следует учитывать, что аргумент периода P не является значением даты:

SELECT PERIOD_ADD(9801,2); // -> 199803

PERIOD_DIFF(P1,P2) — Возвращает количество месяцев между периодами P1 и P2. P1 и P2 должны быть в формате YYMM или YYYYMM. Следует учитывать, что аргументы периода P1 и P2 не являются значениями даты:

SELECT PERIOD_DIFF(9802,199703); // -> 11

DATE_ADD(date,INTERVAL expr type), DATE_SUB(date,INTERVAL expr type), ADDDATE(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type) — Данные функции производят арифметические действия над датами. Обе являются нововведением версии MySQL 3.22.

Функции ADDDATE() и SUBDATE() — синонимы для DATE_ADD() и DATE_SUB(). В версии MySQL 3.23 вместо функций DATE_ADD() и DATE_SUB() можно использовать операторы + и -, если выражение с правой стороны представляет собой столбец типа DATE или DATETIME (см. пример ниже).

Аргумент date является величиной типа DATETIME или DATE, задающей начальную дату.

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

Ключевое слово type показывает, каким образом необходимо интерпретировать данное выражение. Вспомогательная функция EXTRACT(type FROM date) возвращает интервал указанного типа (type) из значения даты.

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

SECOND — SECONDS
MINUTE — MINUTES
HOUR — HOURS
DAY — DAYS
MONTH — MONTHS
YEAR — YEARS
MINUTE_SECOND — «MINUTES:SECONDS»
HOUR_MINUTE — «HOURS:MINUTES»
DAY_HOUR — «DAYS HOURS»
YEAR_MONTH — «YEARS-MONTHS»
HOUR_SECOND — «HOURS:MINUTES:SECONDS»
DAY_MINUTE — «DAYS HOURS:MINUTES»
DAY_SECOND — «DAYS HOURS:MINUTES:SECONDS»

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

В других случаях результат представляет собой величину DATETIME:

SELECT «1997-12-31 23:59:59» + INTERVAL 1 SECOND; // -> 1998-01-01 00:00:00
SELECT INTERVAL 1 DAY + «1997-12-31»; // -> 1998-01-01
SELECT «1998-01-01» — INTERVAL 1 SECOND; // -> 1997-12-31 23:59:59
SELECT DATE_ADD(«1997-12-31 23:59:59», INTERVAL 1 SECOND); // -> 1998-01-01 00:00:00
SELECT DATE_ADD(«1997-12-31 23:59:59», INTERVAL 1 DAY); // -> 1998-01-01 23:59:59
SELECT DATE_ADD(«1997-12-31 23:59:59», INTERVAL «1:1» MINUTE_SECOND); // -> 1998-01-01 00:01:00
SELECT DATE_SUB(«1998-01-01 00:00:00», INTERVAL «1 1:1:1» DAY_SECOND); // -> 1997-12-30 22:58:59
SELECT DATE_ADD(«1998-01-01 00:00:00», INTERVAL «-1 10» DAY_HOUR); // -> 1997-12-30 14:00:00
SELECT DATE_SUB(«1998-01-02», INTERVAL 31 DAY); // -> 1997-12-02

Если указанный интервал слишком короткий (т.е. не включает все части интервала, ожидаемые при заданном ключевом слове type), то MySQL предполагает, что опущены крайние слева части интервала.

Например, если указан аргумент type в виде DAY_SECOND, то ожидаемое выражение expr должно иметь следующие части: дни, часы, минуты и секунды. Если в этом случае указать значение интервала в виде «1:10», то MySQL предполагает, что опущены дни и часы, а данная величина включает только минуты и секунды.

Другими словами, сочетание «1:10» DAY_SECOND интерпретируется как эквивалент «1:10» MINUTE_SECOND. Аналогичным образом в MySQL интерпретируются и значения TIME — скорее как представляющие прошедшее время, чем как время дня.

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

SELECT DATE_ADD(«1999-01-01», INTERVAL 1 DAY); // -> 1999-01-02
SELECT DATE_ADD(«1999-01-01», INTERVAL 1 HOUR); // -> 1999-01-01 01:00:00

При использовании некорректных значений дат результат будет равен NULL. Если при суммировании MONTH, YEAR_MONTH или YEAR номер дня в результирующей дате превышает максимальное количество дней в новом месяце, то номер дня результирующей даты принимается равным последнему дню нового месяца:

SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH); // -> 1998-02-28

Из предыдущего примера видно, что слово INTERVAL и ключевое слово type не являются регистро-зависимыми.

EXTRACT(type FROM date) — Типы интервалов для функции EXTRACT() используются те же, что и для функций DATE_ADD() или DATE_SUB(), но EXTRACT() производит скорее извлечение части из значения даты, чем выполнение арифметических действий.

SELECT EXTRACT(YEAR FROM «1999-07-02»); // -> 1999
SELECT EXTRACT(YEAR_MONTH FROM «1999-07-02 01:02:03»); // -> 199907
SELECT EXTRACT(DAY_MINUTE FROM «1999-07-02 01:02:03»); // -> 20102

TO_DAYS(date) — функция возвращает номер дня для даты, указанной в аргументе date, (количество дней, прошедших с года 0):

SELECT TO_DAYS(950501); // -> 728779
SELECT TO_DAYS('1997-10-07'); // -> 729669

Функция TO_DAYS() не предназначена для использования с величинами, предшествующими введению григорианского календаря (1582), поскольку не учитывает дни, утерянные при изменении календаря.

FROM_DAYS(N) — Возвращает величину DATE для заданного номера дня N:

SELECT FROM_DAYS(729669); // -> '1997-10-07'

DATE_FORMAT(date,format) — Форматирует величину date в соответствии со строкой format. В строке format могут использоваться следующие определители: %M Название месяца (январь…декабрь)
%W Название дня недели (воскресенье…суббота)
%D День месяца с английским суффиксом (0st, 1st, 2nd, 3rd и т.д.

)
%Y Год, число, 4 разряда
%y Год, число, 2 разряда
%X Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с '%V'
%x Год для недели, где воскресенье считается первым днем недели, число, 4 разряда, используется с '%v'
%a Сокращенное наименование дня недели (Вс…Сб)
%d День месяца, число (00..

31)
%e День месяца, число (0..31)
%m Месяц, число (00..12)
%c Месяц, число (0..12)
%b Сокращенное наименование месяца (Янв…Дек)
%j День года (001..366)
%H Час (00..23)
%k Час (0..23)
%h Час (01..12)
%I Час (01..12)
%l Час (1..12)
%i Минуты, число (00..

59)
%r Время, 12-часовой формат (hh:mm:ss [AP]M)
%T Время, 24-часовой формат (hh:mm:ss)
%S Секунды (00..59)
%s Секунды (00..59)
%p AM или PM
%w День недели (0=воскресенье..6=суббота)
%U Неделя (00..53), где воскресенье считается первым днем недели
%u Неделя (00..53), где понедельник считается первым днем недели
%V Неделя (01..

53), где воскресенье считается первым днем недели. Используется с '%X'
%v Неделя (01..53), где понедельник считается первым днем недели. Используется с '%x'

1. %% Литерал '%'.
2. Все другие символы просто копируются в результирующее выражение без интерпретации:

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); // -> 'Saturday October 1997'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); // -> '22:23:00'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j'); // -> '4th 97 Sat 04 10 Oct 277'
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); // -> '22 22 10 10:23:00 PM 22:23:00 00 6'
SELECT DATE_FORMAT('1999-01-01', '%X %V'); // -> '1998 52'
SELECT DATE_FORMAT(sale.time, '%Y-%m-%d') as dat // 2014-03-11

В MySQL 3.23 символ '%' должен предшествовать символам определителя формата. В более ранних версиях MySQL символ '%' необязателен.

Причина того, что промежутки для месяца и дня начинаются с нуля заключается в том, что MySQL позволяет использовать неполные даты, такие как '2004-00-00', начиная с MySQL 3.23.

TIME_FORMAT(time,format) — Данная функция используется аналогично описанной выше функции DATE_FORMAT(), но строка format может содержать только те определители формата, которые относятся к часам, минутам и секундам. При указании других определителей будет выдана величина NULL или 0.

CURDATE(), CURRENT_DATE — Возвращает сегодняшнюю дату как величину в формате YYYY-MM-DD или YYYYMMDD, в зависимости от того, в каком контексте используется функция — в строковом или числовом:

SELECT CURDATE(); // -> '1997-12-15'
SELECT CURDATE() + 0; //-> 19971215

CURTIME(), CURRENT_TIME — Возвращает текущее время как величину в формате HH:MM:SS или HHMMS, в зависимости от того, в каком контексте используется функция — в строковом или числовом:

SELECT CURTIME(); // -> '23:50:26'
SELECT CURTIME() + 0; // -> 235026

NOW(), SYSDATE(), CURRENT_TIMESTAMP — Возвращает текущую дату и время как величину в формате YYYY-MM-DD HH:MM:SS или YYYYMMDDHHMMSS, в зависимости от того, в каком контексте используется функция — в строковом или числовом:

SELECT NOW(); // -> '1997-12-15 23:50:26'
SELECT NOW() + 0; // -> 19971215235026

Заметьте, что NOW() вычисляется только единожды для каждого запроса, а именно — в начале его выполнения. Это позволяет быть уверенным в том, что множественные ссылки на NOW() в рамках одного запроса дадут одно и то же значение.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date) — При вызове данной функции без аргумента она возвращает временную метку UNIX_TIMESTAMP (секунды с 1970-01-01 00:00:00 GMT) как беззнаковое целое число.

Если функция UNIX_TIMESTAMP() вызывается с аргументом date, она возвращает величину аргумента как количество секунд с 1970-01-01 00:00:00 GMT.

Аргумент date может представлять собой строку типа DATE, строку DATETIME, величину типа TIMESTAMP или число в формате YYMMDD или YYYYMMDD местного времени:

SELECT UNIX_TIMESTAMP(); // -> 882226357
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); // -> 875996580

При использовании функции UNIX_TIMESTAMP в столбце TIMESTAMP эта функция будет возвращать величину внутренней временной метки непосредственно, без подразумеваемого преобразования строки во временную метку («string-to-unix-timestamp'' ).

Если заданная дата выходит за пределы допустимого диапазона, то функция UNIX_TIMESTAMP() возвратит 0, но следует учитывать, что выполняется только базовая проверка (год 1970-2037, месяц 01-12, день 01-31).

FROM_UNIXTIME(unix_timestamp) — Возвращает представление аргумента unix_timestamp как величину в формате YYYY-MM-DD HH:MM:SS или YYYYMMDDHHMMSS, в зависимости от того, в каком контексте используется функция — в строковом или числовом:

SELECT FROM_UNIXTIME(875996580); // -> '1997-10-04 22:23:00'
SELECT FROM_UNIXTIME(875996580) + 0; // -> 19971004222300

FROM_UNIXTIME(unix_timestamp,format) — Возвращает строковое представление аргумента unix_timestamp, отформатированное в соответствии со строкой format. Строка format может содержать те же определители, которые перечислены в описании для функции DATE_FORMAT():

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); // -> '1997 23rd December 03:43:30 1997'

SEC_TO_TIME(seconds) — Возвращает аргумент seconds, преобразованный в часы, минуты и секунды, как величину в формате HH:MM:SS или HHMMSS, в зависимости от того, в каком контексте используется функция — в строковом или числовом:

SELECT SEC_TO_TIME(2378); // -> '00:39:38'
SELECT SEC_TO_TIME(2378) + 0; // -> 3938

TIME_TO_SEC(time) — Возвращает аргумент time, преобразованный в секунды:

SELECT TIME_TO_SEC('22:23:00'); // -> 80580
SELECT TIME_TO_SEC('00:39:38'); // -> 2378

Источник: https://htmlweb.ru/php/mysql_date_time.php

Работа с датами в 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 года

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

// Выбрать записи за текущий день
SELECT * FROM `table_name` WHERE `date_field` >= CURDATE();
// Все записи за вчерашний день
SELECT * FROM `table_name` WHERE `date_field` >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND `date_field` < CURDATE(); // Записи за текущую неделю SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND WEEK(`date_field`) = WEEK(NOW()); // И за текущий месяц SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = MONTH(NOW()); // Выбрать из базы записи за последние 30 дней SELECT * FROM `table_name` WHERE `date_field` > NOW() — INTERVAL 30 DAY;
// Выбрать всё за определенный месяц текущего года (например, за май месяц)
SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = 5;
// или за май месяц, но в 2009 году
SELECT * FROM `table_name` WHERE YEAR(`date_field`) = 2009 AND MONTH(`date_field`) = 5;

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

SELECT '2014-07-07 23:59:59' + INTERVAL 1 SECOND; // результат: 2014-07-08 00:00:00
SELECT '2014-07-07 23:59:59' + INTERVAL 1 DAY; // результат: 2014-07-08 23:59:59
// то же самое. но с помощью функции DATE_ADD()
SELECT DATE_ADD('2014-07-07 23:59:59', INTERVAL 1 DAY); // 2014-07-08 23:59:59
// Если надо не добавить, а отнять
SELECT DATE_SUB('2014-07-07 23:59:59', INTERVAL 1 DAY); // 2014-07-06 23:59:59
// или так просто
SELECT '2014-07-07 23:59:59' — INTERVAL 1 DAY; // 2014-07-06 23:59:59

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

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

Если всё-таки возникнут сложности, то задавайте вопросы в этой теме или разделе «Ваш вопрос». Будем разбираться вместе 😉

Источник: https://incode.pro/mysql/rabota-s-datami-v-mysql.html

Математические операции с датой и временем [Словарик по FireBird]

• Иногда возникает необходимость проводить с датой какие-либо операции что мы и попытаемся изложить.
• Для начала определимся, что у нас есть и что мы с этим можем сделать.
• Операции можно проводить со следующими типами данных: DATE, TIME, TIMESTAMP
• Операции которые могут нам понадобиться: это добавить(убавить) и найти разницу.
• С добавлением к TIMESTAMP проще всего заниматься используя родные UDF fbudf ADDMILLISECOND(), ADDSECOND(), ADDMINUTE(), ADDDAY(), ADDWEEK(), ADDMONTH(), ADDYEAR()

С версии 2.1 доступна функция DATEADD().

1. Если не хочется использовать UDF и не важна погрешность, то можно прибавлять все ручками
2. для TIMESTAMP 1 (единица) это один день, соответственно 0.00001157407 это одна секунда
3. для DATE 1 (единица) это один день
4. для TIME 1 (единица) это одна секунда, 3600 это час
5. к примеру хотим добавить 30 секунд к TIMESTAMP

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + (0.00001157407*30) FROM RDB$DATABASE

30 секунд к TIME

SELECT CURRENT_TIME, CURRENT_TIME + 30 FROM rdb$database

добавляем к дате 5 дней

SELECT CURRENT_DATE , CURRENT_DATE + 5 FROM rdb$database

получить кол-во секунд с полночи можно так

SELECT CURRENT_TIME — CAST('0:00' AS TIME) FROM rdb$database

сколько прошло времени между 18:00 и 15:30 в часах и минутах (мы тут никогда не получим результат больше суток «25:15» потому что невозможно)

SELECT CAST('0:00' AS TIME)+(CAST('18:00' AS TIME)-CAST('15:30' AS TIME)) FROM rdb$database

тоже самое но с TIMESTAMP сравнивается только время, дата не учитывается никак

SELECT CAST('0:00' AS TIME)+(CAST('01.01.2001 18:00' AS TIMESTAMP)-CAST('02.02.2007 15:30' AS TIMESTAMP))*24*60*60 FROM rdb$database

зато дату можно можно прибывать к другому TIMESTAMP

SELECT CAST('03.03.2003' AS TIMESTAMP) + (CAST('05.02.2001 18:00' AS TIMESTAMP)-CAST('02.02.2001 15:30' AS TIMESTAMP)) FROM rdb$database

также мы можем TIME прибавит к TIMESTAMP

SELECT CURRENT_TIMESTAMP+(CAST('2:00' AS TIME)-CAST('0:00' AS TIME))/(60*60*24) FROM rdb$database

TIME и DATE складываются без шаманства

SELECT CURRENT_DATE + CURRENT_TIME FROM rdb$database

получить разницу больше суток в часах можно при помощи следующей процедуры

CREATE OR ALTER PROCEDURE get_my_time (beg TIMESTAMP, ends TIMESTAMP)
RETURNS (_time VARCHAR(20))
AS DECLARE variable INTERVAL DOUBLE PRECISION; DECLARE variable hours INT; DECLARE variable mins INT; DECLARE variable sec INT;
BEGIN INTERVAL = beg — ends; IF (INTERVAL INTERVAL) THEN hours = INTERVAL — 1; ELSE hours = INTERVAL; INTERVAL = (INTERVAL — hours)*60; IF (CAST(INTERVAL AS INT) > INTERVAL) THEN mins = INTERVAL — 1; ELSE mins = INTERVAL; sec = (INTERVAL — mins)*60; IF (sec = 60) THEN sec = 59; /* милисекунды могут сделать бяку, потому так */
  _time = hours || ':' || CASE WHEN mins>10 THEN mins ELSE '0' || mins END || ':' || CASE WHEN sec>10 THEN sec ELSE '0' || sec END; suspend;
END

Только авторизованные участники могут оставлять комментарии.

Источник: http://firebirdsql.su/doku.php?id=matematicheskie_operacii_s_datoj