SQL-Ex blog

Различные типы данных даты и времени

Сначала давайте взглянем на различные типы данных, перечисленные в следующей таблице:

SQL Server



Oracle



PostgreSQL

Простые операции с датами

Как видим, уже имеются некоторые отличия в рассматриваемых типах данных. Давайте теперь рассмотрим простые операции с датами. Как всегда, мы будем использовать бесплатно загружаемый образец базы данных github Chinook, поскольку он доступен в нескольких форматах СУБД по этой ссылке: https://github.com/cwoodruff/ChinookDatabase. Эта база представляет собой симуляцию магазина цифровых медиа с некоторым набором данных. Все что вам нужно сделать, это загрузить подходящую версию, и вы получите необходимые скрипты для создания структуры данных и вставки всех данных.

SQL Server

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

select getdate() as actual_date

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

select cast(getdate() as date) as actual_date

Кроме того, имеется стандартная функция (ANSI SQL), которая делает то же самое:

select CURRENT_TIMESTAMP

Oracle

Если мы попытаемся сделать то же самое в Oracle, то придем к совершенно другим функциям. Во-первых, для получения актуальной даты/времени мы должны использовать функцию sysdate:

select sysdate as actual_date from dual;

Помним также, что в Oracle нельзя выполнять запрос без предложения FROM, поэтому мы всегда добавляем предложение from dual.



Помимо этого, в Oracle возвращаемый результат зависит от национального форматирования сессии, именно поэтому сейчас мы видим только дату. Но если мы применим форматирование даты:

select to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') as actual_date from dual;

то увидим как дату, так и время. А если захотим получить только дату, используется функция trunc:

select to_char(trunc(sysdate), 'dd-mm-yyyy hh24:mi:ss') as actual_date from dual;

PostgreSQL

В PostgreSQL у нас имеется другая функция или, более точно, более одной функции, и все они соответствуют стандарту ANSI SQL. Например, вы можем вернуть текущую дату и время с помощью CURRENT_TIMESTAMP:

SELECT CURRENT_TIMESTAMP;

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

SELECT CURRENT_TIMESTAMP(0);

Обратите внимание, что при использовании CURRENT_TIMESTAMP также выводится часовой пояс.

Если мы хотим вернуть только дату или время, в PostgreSQL имеются следующие функции:

SELECT CURRENT_DATE;

SELECT CURRENT_TIME;

Добавить и вычесть из даты

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

SQL Server

Для этой цели в SQL Server у нас имеется функция DATEADD. Давайте рассмотрим пример, добавляющий 5 дней к фактической дате:

select dateadd(d,5,getdate()) as actual_5_days

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

Предположим теперь, что нам нужно добавить 5 дней к фактической дате, но выводить время нам не нужно, только дату. Мы должны преобразовать результат функции GETDATE(), как мы это делали раньше.

select dateadd(d,5,cast(getdate() as date)) as actual_5_days

Пусть теперь нам нужно добавить 7 дней к дате счета в таблице Invoice базы данных Chinook, чтобы получить дату оплаты счетов на сумму более 20 евро:

select Invoicedate, dateadd(d,7,Invoicedate) as PaymentDate, Total

from Invoice

where total>20

Довольно легко, нет? Теперь предположим, что нам нужно вычесть неделю из фактической даты:

select dateadd(WEEK,-1,cast(getdate() as date)) as week_before

Обратите внимание, что мы используем ту же функцию DATEADD() также и для вычитания, но с использованием отрицательного числа!

Oracle

Теперь попробуем выполнить те же самые операции в Oracle. Сначала добавим 5 дней к фактической дате:

select sysdate+5 as actual_5_days from dual;

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

select to_char(sysdate+1/24,'dd/mm/yyyy hh24:mm:ss') as actual_1_hour from dual;

Как и ранее, если нам нужна только дата, мы используем функцию TRUNC:

select to_char(TRUNC(sysdate)+5,'dd/mm/yyyy hh24:mm:ss') as actual_5_DAYS from dual;

Теперь давайте, как и для SQL Server, добавим 7 дней к дате счета:

select Invoicedate, Invoicedate+7 as PaymentDate, Total

from chinook.Invoice

where total>20

Очень легко, а теперь вычтем неделю:

select sysdate as actual, sysdate-7 as week_before from dual;

Мы можем такте применить все то форматирование, которое мы видели ранее:

select to_char(sysdate,'mm/dd/yyyy') as actual, to_char(sysdate-7,'mm/dd/yyyy') as week_before from dual;

PostgreSQL

Теперь в PostgreSQL:

select current_date as actual_date, current_date+5 as actual_5_days;

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

select current_timestamp as actual_date, current_timestamp+interval '1 hour' as actual_1_hour;

Давайте теперь добавим 7 дней к InvoiceDate. Обратите внимание, что, поскольку столбец InvoiceDate имеет тип данных timestamp, нам нужно задать интервал; если бы мы просто написали 7, то получим ошибку.

select "InvoiceDate","InvoiceDate"+ interval '7 days' as PaymentDate, "Total"

from "Invoice"

where "Total">20

Аналогично для вычитания:

select current_date as actual_date, current_date -7 as date_week_before

Имея функцию current_date, которая возвращает тип данных date, я могу просто использовать целое число, и оно будет неявно полагаться днями. Если вместо этого использовать current_timestamp, нам необходимо будет указывать интервал:

select current_timestamp as actual_date, current_timestamp - interval '7 days' as date_week_before

Диапазон времени между двумя датами

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

SQL Server

Для этой цели в SQL Server используется функция DATEDIFF. Предположим, что нам необходимо получить среднее время, прошедшее между одной покупкой и следующей (следующая строка) для каждого клиента. Я использую готовый код, применяющий оконные функции, но объясняю только синтаксис SELECT DATEDIFF:

;WITH InvCust AS

(SELECT DISTINCT CustomerID, 

        InvoiceDate AS cur_d, 

        next_d = LEAD(InvoiceDate, 1) OVER (PARTITION BY CustomerID ORDER BY InvoiceDate)

  FROM Invoice)

,customer_avg as 

(SELECT CustomerID,cur_d

       ,next_d

       ,dif_day = DATEDIFF(DAY, cur_d, next_d)

       ,avg_cust = AVG(DATEDIFF(DAY, cur_d, next_d)) OVER (PARTITION BY CustomerID)

       ,avg_all = AVG(DATEDIFF(DAY, cur_d, next_d)) OVER ()

FROM InvCust)

select distinct customer.customerid, FirstName + ' ' + lastname as Customer, country,avg_cust, avg_all

from customer_avg

inner join customer

on Customer.CustomerId=customer_avg.CustomerId

order by avg_cust

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

Oracle

WITH InvCust AS

(SELECT DISTINCT CustomerID, 

        InvoiceDate AS cur_d, LEAD(InvoiceDate, 1) OVER (PARTITION BY CustomerID ORDER BY InvoiceDate) as next_d

  FROM chinook.Invoice)

,customer_avg as 

(SELECT CustomerID,cur_d, next_d

  ,(next_d-cur_d)as dif_day, AVG((next_d-cur_d)) OVER (PARTITION BY CustomerID) as  avg_cust

  ,AVG(next_d-cur_d) OVER () as avg_all

FROM InvCust)

select distinct customer.customerid,FirstName||' '||lastname as Customer, country,cast(avg_cust as decimal(5,2)) as avg_customer, cast(avg_all as decimal(5,2)) as avg_allcust

from customer_avg

inner join chinook.customer

on Customer.CustomerId=customer_avg.CustomerId

order by avg_customer, customerid;

В Oracle все еще проще, просто вычитание одной даты из другой, и вы автоматически получаете разницу в днях!

PostgreSQL

WITH InvCust AS

(SELECT DISTINCT "CustomerId", 

        "InvoiceDate" AS cur_d, LEAD("InvoiceDate", 1) OVER (PARTITION BY "CustomerId" ORDER BY "InvoiceDate") as next_d

  FROM "Invoice")

,customer_avg as 

(SELECT "CustomerId",cur_d, next_d

  ,next_d-cur_d as dif_day, AVG(next_d-cur_d) OVER (PARTITION BY "CustomerId") as avg_cust

  ,AVG(next_d-cur_d) OVER () as avg_all

FROM InvCust)

select distinct "Customer"."CustomerId","FirstName"||' '||"LastName" as Customer, "Country",avg_cust, avg_all

from customer_avg

inner join "Customer"

on "Customer"."CustomerId"=customer_avg."CustomerId"

order by avg_cust;

И то же самое в PostgreSQL, хотя в этой СУБД мы также имеем функцию AGE. Поэтому давайте напишем тот же запрос с использованием этой функции:

WITH InvCust AS

(SELECT DISTINCT "CustomerId", 

        "InvoiceDate" AS cur_d, LEAD("InvoiceDate", 1) OVER (PARTITION BY "CustomerId" ORDER BY "InvoiceDate") as next_d

  FROM "Invoice")

,customer_avg as 

(SELECT "CustomerId",cur_d,next_d

  ,age(next_d,cur_d) as dif_day,AVG(age(next_d,cur_d)) OVER (PARTITION BY "CustomerId") as avg_cust

  ,AVG(age(next_d,cur_d)) OVER () as avg_all

FROM InvCust)

select distinct "Customer"."CustomerId","FirstName"||' '||"LastName" as Customer, "Country",avg_cust, avg_all

from customer_avg

inner join "Customer"

on "Customer"."CustomerId"=customer_avg."CustomerId"

order by avg_cust;

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

select age(timestamp'2014-01-01')

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

Извлечение из даты компоненты даты

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

SQL Server

В SQL Server для этого мы можем использовать по сути 4 различных функции, в зависимости от того, что мы хотим получить, но очевидно, что практически все можно сделать с функцией DATEPART:

SELECT getdate() as actual_date, 

       DATEPART(d,getdate()) as date_day,

       DATEPART(month,getdate()) as date_month,

       DATEPART(year,getdate()) as date_year

Как видно, это довольно легко, просто укажите требуемую компоненту даты, дату, и все сделано! Но этого можно достичь также и другим способом:

SELECT getdate() as actual_date, 

       day(getdate()) as date_day,

       month(getdate()) as date_month,

       year(getdate()) as date_year

Те же результаты, используя непосредственно функции DAY, MONTH и YEAR.

Oracle

В Oracle мы получаем те же результаты с помощью функции EXTRACT:

SELECT SYSDATE AS ACTUAL_DATE, 

       EXTRACT(DAY FROM SYSDATE) AS ACTUAL_DAY,

       EXTRACT(MONTH FROM SYSDATE) AS ACTUAL_MONTH,

       EXTRACT(YEAR FROM SYSDATE) AS ACTUAL_YEAR FROM DUAL;

Довольно просто, и почти тот же синтаксис, что и в SQL Server!

PostgreSQL

В PostgreSQL имеется по сути 2 функции, чтобы сделать одно и то же, так как у нас имеется и date_part, и extract:

SELECT current_date AS ACTUAL_DATE, 

       EXTRACT(DAY FROM current_date) AS ACTUAL_DAY,

       EXTRACT(MONTH FROM current_date) AS ACTUAL_MONTH,

       EXTRACT(YEAR FROM current_date) AS ACTUAL_YEAR

Это в точности тот же синтаксис, как и в Oracle, а теперь date_part:

SELECT current_date AS ACTUAL_DATE, 

       date_part('day',current_date) AS ACTUAL_DAY,

       date_part('month', current_date) AS ACTUAL_MONTH,

       date_part('year', current_date) AS ACTUAL_YEAR

Незначительное отличие от синтаксиса SQL Server, но почти то же самое. Заметим, что функцией стандарта ANSI SQL в данном случае является EXTRACT.