sql сколько дней между датами

Функция DATEDIFF стр. 2

Задача. Посчитать число минут в интервале между двумя датами – ‘2011-10-07 23:43:00’ и ‘2011-10-08 01:23:00’

SQL Server

Встроенная функция DATEDIFF решает проблему:

Результат – 100 минут.

В запросе используется стандартное представление даты (ISO) в виде текстовой строки ‘yyyy-mm-ddThh:mm:ss’, которое не зависит ни от локальных настроек сервера, ни и от самого сервера.

MySQL

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

PostgreSQL

В PostgreSQL нет функции, подобной DATEDIFF (SQL Server) или TIMESTAMPDIFF (MySQL). Поэтому для решения задачи можно применить следующую последовательность действий:

Для получения интервала можно взять разность двух значений темпорального типа, При этом требуется явное преобразование типа:

Для получения числа секунд в интервале воспользуемся функцией

Значение интервала представим последним способом как наиболее коротким. Для получения окончательного решения задачи поделим полученный результат на 60:

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

Oracle

Oracle тоже не имеет в своем арсенале функции типа DATEDIFF. Кроме того, Oracle не поддерживает стандартное текстовое представление даты/времени.

Мы можем посчитать интервал в минутах, приняв во внимание, что разность дат (значений типа date ) в Oracle дает в результате число дней (суток). Тогда для вычисления интервала в минутах нужно просто разность дат умножить на 24 (число часов в сутках), а затем на 60 (число минут в часе):

Подобным образом можно получить и другие временные интервалы.

Источник

DATEDIFF (Transact-SQL)

Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе datepart, за период времени, указанный в аргументах startdate и enddate.

Сведения о функции, которая обрабатывает значительные отличия значений аргументов startdate и enddate, см. в статье DATEDIFF_BIG (Transact-SQL). Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

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

Каждое конкретное имя аргумента datepart и сокращение этого имени datepart будут возвращать одно и то же значение.

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

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

enddate
См. описание аргумента startdate.

Тип возвращаемых данных

Возвращаемое значение

Значение типа int, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.

В качестве возвращаемого значения вне диапазона для int (от –2 147 483 648 до 2 147 483 647) DATEDIFF возвращает сообщение об ошибке. Для миллисекунды максимальная разница между startdate и enddate составляет 24 дня 20 часов 31 минута и 23 647 секунд. Для секунды максимальная разница составляет 68 лет, 19 дней, 3 часа, 14 минут и 7 секунд.

Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то DATEDIFF возвращает значение 0.

При вычислении возвращаемого значения DATEDIFF использует компонент смещения часовых поясов для аргументов startdate или enddate.

Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate и enddate значений типа smalldatetime секунды и миллисекунды у возвращаемых значений всегда равны 0.

Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, DATEDIFF присваивает значения 0 недостающим частям другого аргумента.

Границы, задаваемые аргументом datepart

Приведенные ниже инструкции имеют одинаковые значения аргументов startdate и enddate. Указанные даты являются соседними, а временная разница между ними составляет 100 наносекунд (0,0000001 секунды). Разница между аргументами startdate и enddate в каждой инструкции пересекает одну календарную или временную границу аргумента datepart. Каждое выражение возвращает значение 1.

Если аргументы startdate и enddate имеют разные значения года, но одинаковые значения календарной недели, DATEDIFF вернет 0 для части week аргумента datepart.

Remarks

Функция DATEDIFF неявно приводит строковые литералы к типу datetime2. Это означает, что DATEDIFF не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.

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

Примеры

В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.

A. Указание столбцов в качестве начальной и конечной даты

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

Б. Указание определенных пользователем переменных в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают определенные пользователем переменные.

В. Указание скалярных системных функций в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают скалярные системные функции.

Г. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.

Д. Указание констант в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate используются символьные константы.

Е. Указание числовых выражений и скалярных системных функций в качестве конечной даты

Ж. Указание ранжирующих функций в качестве начальной даты

В этом примере в качестве аргумента startdate. используется ранжирующая функция.

З. Указание агрегатной оконной функции в качестве начальной даты

В этом примере в качестве аргумента startdate используется агрегатная оконная функция.

И. Определение разницы между startdate и enddate в виде строковых компонентов даты

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.

К. Указание столбцов в качестве начальной и конечной даты

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

Л. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.

М. Указание констант в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate используются символьные константы.

Н. Указание ранжирующих функций в качестве начальной даты

В этом примере в качестве аргумента startdate. используется ранжирующая функция.

О. Указание агрегатной оконной функции в качестве начальной даты

В этом примере в качестве аргумента startdate используется агрегатная оконная функция.

Источник

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

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

Все ниже рассмотренные функции работают с календарными типами данных.

Получение текущей даты и времени.

Чтобы получить текущую дату и время используется функция NOW ().

SELECT NOW ()
Результат: 2015-09-25 14:42:53

Для получения только текущей даты есть функция CURDATE ().

SELECT CURDATE ()
Результат: 2015-09-25

И функция CURTIME (), которая возвращает только текущее время:

SELECT CURTIME ()
Результат: 14:42:53

Функции CURDATE () и NOW () удобно использовать для добавления в базу данных записей, для которых требуется хранить дату добавления. Например, при добавлении статьи на сайт хорошо бы хранить ее дату публикации. Тогда запрос на добавление статьи в базу будет примерно таким:

INSERT INTO posts (id_post, text_post, date_publication) VALUES (1, ‘текст статьи’, NOW ());

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

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

а также их комбинации:

SELECT ADDDATE (‘2015-09-28 10:30:20’, INTERVAL 1 DAY)
Результат: 2015-09-29 10:30:20

SELECT ADDDATE (‘2015-09-28 10:30:20’, INTERVAL ‘3 1:20’ DAY_MINUTE)
Результат: 2015-10-01 11:50:20

SELECT SUBDATE (‘2015-09-28 10:30:20’, INTERVAL 20 HOUR)
Результат: 2015-09-27 14:30:20

Функция PERIOD_ADD (period, n) прибавляет к значению period n месяцев. Значение период должно быть представлено в формате YYYYMM (например сентябрь 2015 года будет 201509). Пример:

SELECT PERIOD_ADD (201509, 4)
Результат: 201601

SELECT TIMESTAMPADD (QUARTER, 1, ‘2015-09-28’)
Результат: 2015-12-28

Функция SUBTIME (date, time) вычитает из даты date время time. Пример:

SELECT SUBTIME (‘2015-09-28 10:30:20′, ’50:20:19’)
Результат: 2015-09-26 08:10:01

Вычисление интервала между датами

Функция DATEDIFF (date1, date2) вычисляет разницу в днях между двумя датами, при этом часы, минуты и секунды при указании дат игнорируются. Пример:

SELECT DATEDIFF (‘2015-09-28 00:00:20’, ‘2015-09-27 23:40:20’)
Результат: 1

С помощью этой функции легко определить сколько дней прошло с даты публикации статьи:

SELECT DATEDIFF (CURDATE (), date_publication) FROM posts WHERE id_post = 1

SELECT PERIOD_DIFF (201509, 201501)
Результат: 9

SELECT TIMESTAMPDIFF (HOUR, ‘2015-09-28 10:30:20’, ‘2015-09-28 19:50:20’)
Результат: 9

Получение различных форматов даты и времени и другой информации

Функция DATE (datetime) возвращает дату, отсекая время. Пример:

SELECT DATE (‘2015-09-28 10:30:20’)
Результат: 2015-09-28

Функция TIME (datetime) возвращает время, отсекая дату. Пример:

SELECT TIME (‘2015-09-28 10:30:20’)
Результат: 10:30:20

TIMESTAMP (‘2015-09-28’)
Результат: 2015-09-28 00:00:00

DAY (date) и DAYOFMONTH (date). Функции-синонимы, которые возвращают порядковый номер дня месяца. Пример:

SELECT DAY (‘2015-09-28’), DAYOFMONTH (‘2015-09-28’)
Результат: 28 | 28

Функции DAYNAME (date), DAYOFWEEK (date) и WEEKDAY (date). Первая функция возвращает название дня недели, вторая — номер дня недели (отсчет от 1 — воскресенье до 7 — суббота), третья также номер дня недели только другой отсчет(отсчет от 0 — понедельник, до 6 — воскресенье). Пример:

SELECT DAYNAME (‘2015-09-28’), DAYOFWEEK (‘2015-09-28’), WEEKDAY (‘2015-09-28’)
Результат: Monday 2 | 0

Функции WEEK (date) и WEEKOFYEAR (datetime). Обе функции возвращают номер недели в году, только у первой неделя начинается с воскресенья, а у второй с понедельника. Пример:

SELECT WEEK (‘2015-09-28 10:30:20’), WEEKOFYEAR (‘2015-09-28 10:30:20’)
Результат: 39 | 40

Функция MONTH (date) возвращает числовое значение месяца (от 1 до 12), а MONTHNAME (date) название месяца. Пример:

SELECT MONTH (‘2015-09-28 10:30:20’), MONTHNAME (‘2015-09-28 10:30:20’)
Результат: 9 | September

Функция QUARTER (date) возвращает номер квартала года (от 1 до 4). Пример:

SELECT QUARTER (‘2015-09-28 10:30:20’)
Результат: 3

Функция YEAR (date) возвращает значение года (от 1000 до 9999). Пример:

SELECT YEAR (‘2015-09-28 10:30:20’)
Результат: 2015

Функция DAYOFYEAR (date) возвращает порядковый номер дня в году (от 1 до 366). Прмиер:

SELECT DAYOFYEAR (‘2015-09-28 10:30:20’)
Результат: 271

Функция HOUR (datetime) возвращает значение часа (от 0 до 23). Пример:

SELECT HOUR (‘2015-09-28 10:30:20’)
Результат: 10

Функция MINUTE (datetime) возвращает значение минут (от 0 до 59). Пример:

SELECT MINUTE (‘2015-09-28 10:30:20’)
Результат: 30

Функция SECOND (datetime) возвращает значение секунд (от 0 до 59). Пример:

SELECT SECOND (‘2015-09-28 10:30:20’)
Результат: 20

SELECT EXTRACT (YEAR FROM ‘2015-09-28 10:30:20’), EXTRACT (MONTH FROM ‘2015-09-28 10:30:20’), EXTRACT (DAY FROM ‘2015-09-28 10:30:20’), EXTRACT (HOUR FROM ‘2015-09-28 10:30:20’), EXTRACT (MINUTE FROM ‘2015-09-28 10:30:20’), EXTRACT (SECOND FROM ‘2015-09-28 10:30:20’)
Результат: 2015 | 9 | 28 | 10 | 30 | 20

Взаимообратные функции TO_DAYS (date) и FROM_DAYS (n). Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату. Пример:

SELECT TO_DAYS (‘2015-09-28 10:30:20’), FROM_DAYS (736234)
Результат: 736234 | 2015-09-28

Взаимообратные функции UNIX_TIMESTAMP (date) и FROM_UNIXTIME (n). Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату. Пример:

SELECT UNIX_TIMESTAMP (‘2015-09-28 10:30:20’), FROM_UNIXTIME (1443425420)
Результат: 1443425420 | 2015-09-28 10:30:20

Взаимообратные функции TIME_TO_SEC (time) и SEC_TO_TIME (n). Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время. Пример:

SELECT TIME_TO_SEC (’10:30:20′), SEC_TO_TIME (37820)
Результат: 37820 | 10:30:20

Функция MAKEDATE (year, n) принимает год year и номер дня в году n и преобразует их в дату. Пример:

SELECT MAKEDATE (2015, 271)
Результат: 2015-09-28

Источник

Типы данных и функции даты и времени (Transact-SQL)

В разделах этой статьи представлен обзор всех типов данных и функций даты и времени Transact-SQL.

Типы данных даты и времени

Типы данных даты и времени Transact-SQL перечислены в следующей таблице:

Тип данных Transact-SQL rowversion не относится к типам данных даты и времени. Тип данных timestamp является устаревшим синонимом rowversion.

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

В следующих таблицах приводятся функции даты и времени Transact-SQL. Дополнительные сведения о детерминизме см. в статье Детерминированные и недетерминированные функции.

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

Transact-SQL наследует все значения системной даты и времени от операционной системы компьютера, на котором работает экземпляр SQL Server.

Высокоточные функции системной даты и времени

SQL Server 2019 (15.x) получает значения даты и времени с помощью функции GetSystemTimeAsFileTime() Windows API. Точность зависит от физического оборудования и версии Windows, в которой запущен экземпляр SQL Server. Точность возвращаемых значений этого API-интерфейса задана равной 100 нс. Точность может быть определена с помощью метода GetSystemTimeAdjustment() API-интерфейса Windows.

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SQL Server функция DATEDIFF

В этом учебном пособии вы узнаете, как использовать функцию DATEDIFF в SQL Server (Transact-SQL) с синтаксисом и примерами.

Описание

В SQL Server (Transact-SQL) функция DATEDIFF возвращает разность между двумя значениями даты в зависимости от указанного интервала.

Синтаксис

Синтаксис функции DATEDIFF в SQL Server (Transact-SQL):

Параметры или аргументы

Применение

Функция DATEDIFF может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Пример

Рассмотрим некоторые примеры SQL Server функции DATEDIFF, чтобы понять, как использовать функцию DATEDIFF в SQL Server (Transact-SQL). Например:

Источник