Функции T-SQL для работы со значениями даты и времени. Лекция 11

1.

Функции Transact-SQL
для работы с данными типа
даты/времени
Transact-SQL (T-SQL) — процедурное расширение языка SQL, созданное
компанией Microsoft (для Microsoft SQL Server) и Sybase (для Sybase ASE).
SQL был расширен такими дополнительными возможностями как:
• управляющие операторы,
• локальные и глобальные переменные,
• различные дополнительные функции для обработки строк, дат, математики
и т. п.,
• поддержка аутентификации Microsoft Windows.

2.

Функция DATEPART
Функция DATEADD
Функция DATEDIFF
Функция DATENAME
Первый день недели
Функция DATEFROMPARTS

3.

Функция DATEPART
DATEPART(datepart , date)
Функция возвращает целое число,
представляющее собой указанную аргументом datepart
часть заданной вторым аргументом даты (date).
Значения аргумента
Допустимые сокращения
год
yy
месяц
mm
день
dd
час
hh
минуты
mi
секунды
ss
Weekday — день недели
dw

4.

select b_datetime, DATEPART(yy, b_datetime) year,
DATEPART(mm, b_datetime) month, DATEPART(dd,
b_datetime) day, DATEPART(hh, b_datetime) hour,
DATEPART(mi, b_datetime) minute, DATEPART(ss,
b_datetime) second
from utb
b_datetime
year
month
day
hour
minute
second
2000-01-01 01:13:36.000
2000
1
1
1
13
36
2001-01-01 01:13:37.000
2001
1
1
1
13
37
2002-01-01 01:13:38.000
2002
1
1
1
13
38
….

5.

Функцию DATEPART можно заменить
более простыми функциями:
• DAY(date) — целочисленное представление
дня указанной даты. Эта функция
эквивалентна функции DATEPART(dd, date).
• MONTH(date) — целочисленное
представление месяца указанной даты. Эта
функция эквивалентна функции DATEPART(mm,
date).
• YEAR(date) — целочисленное представление
года указанной даты. Эта функция
эквивалентна функции DATEPART(yy, date).

6.

select date, DATEPART(yy, date) year, DATEPART(mm,
date) month, DATEPART(dd, date) day, DATEPART(hh,
date) hour, DATEPART(dw, date) Weekday
from battles
date
year
month
day
hour
Weekday
1942-11-15 00:00:00.000
1942
11
15
0
1
1941-05-25 00:00:00.000
1941
5
25
0
1
1943-12-26 00:00:00.000
1943
12
26
0
1
1944-10-25 00:00:00.000
1944
10
25
0
4
1962-10-20 00:00:00.000
1962
10
20
0
7
….

7.

Задача
Определить продолжительность рейса
1123 в минутах
• полет не может продолжаться более суток;
• для рейсов, которые вылетают в один день,
а прилетают на следующий,
• для рейсов, которые вылетают и прилетают
в один день.

8.

Определим время вылета и прилета
в минутах
SELECT DATEPART(hh, time_out)*60 +
DATEPART(mi, time_out) time_dep,
DATEPART(hh, time_in)*60 + DATEPART(mi,
time_in) time_arr
FROM Trip
WHERE trip_no = 1123
time_dep
time_arr
980
220

9.

Мы должны сравнить, превышает ли время
прилета time_arr время вылета time_dep.
• Если это так, следует вычесть из первого
второе, чтобы получить продолжительность
рейса: time_arr - time_dep.
• В противном случае к разности нужно
добавить одни сутки (24*60 = 1440 минут):
time_arr - time_depr + 1440

10.

SELECT CASE
WHEN time_dep >= time_arr --время прилета
time_arr не превышает времени вылета
time_dep
THEN time_arr - time_dep + 1440
ELSE time_arr - time_dep
END dur
FROM (SELECT DATEPART(hh, time_out)*60 +
DATEPART(mi, time_out) time_dep,
DATEPART(hh, time_in)*60 + DATEPART(mi,
time_in) time_arr
FROM Trip
dur
WHERE trip_no = 1123
680
) tm;

11.

Функция DATEDIFF
Синтаксис:
DATEDIFF(datepart, startdate, enddate)
Функция возвращает интервал времени,
прошедшего между двумя временными
отметками — startdate (начальная отметка) и
enddate (конечная отметка).
Этот интервал может быть измерен в разных
единицах. Возможные варианты
определяются аргументом datepart

12.

Задача
Определить количество дней, прошедших между
первым и последним совершенными рейсами.
Pass_in_trip (trip_no, date, ID_psg, place)
SELECT DATEDIFF(dd,
(SELECT MIN(date) FROM pass_in_trip),
(SELECT MAX(date) FROM pass_in_trip));

13.

Задача
Определить продолжительность рейса 1123 в
минутах
Trip (trip_no, ID_comp, plane, town_from, town_to,
time_out, time_in)
SELECT CASE
WHEN DATEDIFF(mi, time_out, time_in)> 0
then DATEDIFF(mi, time_out, time_in)
else DATEDIFF(mi, time_out, time_in)+1440
END dur
FROM Trip
WHERE trip_no=1123

14.

Функция DATEADD
DATEADD (datepart, number, date)
Функция возвращает значение типа datetime,
которое получается добавлением к дате date
количества интервалов типа datepart, равного
number (целое число).

15.

Datepart
Допустимые сокращения
Day — день
dd, d
Dayofyear — день года
dy, y
Hour — час
hh
Millisecond - миллисекунда
ms
Minute — минута
mi, n
Month — месяц
mm, m
Quarter — квартал
qq, q
Second — секунда
ss, s
Week — неделя
wk, ww
Year — год
yy, yyyy

16.

SELECT current_timestamp, DATEADD(day, 2,
current_timestamp)
2017-11-24 22:37:50.290
2017-11-26 22:37:50.290
SELECT current_timestamp, DATEADD(yy, 2,
current_timestamp)
2017-11-24 22:39:39.273
2019-11-24 22:39:39.273
SELECT current_timestamp, DATEADD(mm, 2,
current_timestamp)
2017-11-24 22:41:38.057
2018-01-24 22:41:38.057

17.

Задача
Определить, какой будет день через неделю после
последнего полета.
SELECT (SELECT MAX(date) max_date
FROM pass_in_trip),
DATEADD(day, 7, (SELECT MAX(date) max_date
FROM pass_in_trip))
2005-11-29 00:00:00.000
2005-12-06 00:00:00.000
Применение подзапроса в качестве аргумента допустимо, так как этот подзапрос
возвращает единственное значение типа datetime.

18.

Функция DATENAME
DATENAME( datepart, date )
Функция возвращает символьное
представление составляющей (datepart)
указанной даты (date).
SELECT DATENAME(weekday, '20031231' )+', ' +
DATENAME(day, '20031231') +
' ' + DATENAME(month, '20031231') + ' ' +
DATENAME(year,'20031231');

19.

SELECT DATENAME(weekday,'20181031')+', ' +
DATENAME(day,'20181031') +' ' +
DATENAME(month,'20181031') + ' ' +
DATENAME(year,'20181031')
Wednesday, 31 October 2018

20.

Задача 110
Определить имена разных пассажиров, когда-либо
летевших рейсом, который вылетел в субботу, а
приземлился в воскресенье

21.

select name from passenger where id_psg in
(select id_psg
from pass_in_trip pit join trip t on pit.trip_no =
t.trip_no
where time_in < time_out and datepart(dw, date)
=7)
или
select name from passenger where id_psg in
(select id_psg
from pass_in_trip pit join trip t on pit.trip_no =
t.trip_no
where time_in <= time_out and
datename(weekday, date) = ‘Saturday’)

22.

Особенности обработки дат и времени в СУБД SQLite
Источник: https://oracleplsql.ru/data-types-sqlite.html
Обобщенные типы данных:
TEXT
INTEGER
NUMERIC
REAL
NONE
Тип атрибутов даты и времени можно описать как ТEXT.
Select date, time_out
from trip join pass_in_trip on trip.trip_no=pass_in_trip.trip_no
date
time_out
2003-04-01 00:00:00.000
1900-01-01 06:12:00.000
2003-04-01 00:00:00.000
1900-01-01 06:12:00.000
2003-04-01 00:00:00.000
1900-01-01 06:12:00.000
2003-04-01 00:00:00.000
1900-01-01 22:50:00.000
2003-04-02 00:00:00.000
1900-01-01 09:00:00.000
2003-04-05 00:00:00.000
1900-01-01 09:35:00.000

23.

Особенности обработки дат и времени в СУБД SQLite
Конкатенация даты (DateTime) и
времени (DateTime) в MS SQL
Select date, time_out
from trip join pass_in_trip on
trip.trip_no=pass_in_trip.trip_no
Select date+time_out
from trip join pass_in_trip on
trip.trip_no=pass_in_trip.trip_no
date
time_out
dt
2003-04-01 00:00:00.000
1900-01-01 06:12:00.000
2003-04-01 06:12:00.000
2003-04-01 00:00:00.000
1900-01-01 06:12:00.000
2003-04-01 06:12:00.000
2003-04-01 00:00:00.000
1900-01-01 06:12:00.000
2003-04-01 06:12:00.000
2003-04-01 00:00:00.000
1900-01-01 22:50:00.000
2003-04-01 22:50:00.000
2003-04-02 00:00:00.000
1900-01-01 09:00:00.000
2003-04-02 09:00:00.000
2003-04-05 00:00:00.000
1900-01-01 09:35:00.000
2003-04-05 09:35:00.000

24.

Особенности обработки дат и времени в СУБД SQLite
Конкатенация даты (Text) и времени
(Text) в SQLite
Select date||time_out dt
from trip join pass_in_trip on
trip.trip_no=pass_in_trip.trip_no
Select date||" "||time_out dt
from trip join pass_in_trip on
trip.trip_no=pass_in_trip.trip_no
dt
-------------------------------------2003-04-29 00:00:001900-01-01 14:30:00
2003-04-05 00:00:001900-01-01 16:20:00
2003-04-08 00:00:001900-01-01 16:20:00
2003-04-08 00:00:001900-01-01 16:20:00
dt
-------------------------------------2003-04-29 00:00:00 1900-01-01 14:30:00
2003-04-05 00:00:00 1900-01-01 16:20:00
2003-04-08 00:00:00 1900-01-01 16:20:00
2003-04-08 00:00:00 1900-01-01 16:20:00
Конкатенация даты (DateTime) и
времени (DateTime) в MS SQL
dt
2003-04-01 06:12:00.000
2003-04-01 06:12:00.000
Select date+time_out
from trip join pass_in_trip on
trip.trip_no=pass_in_trip.trip_no
2003-04-01 06:12:00.000
2003-04-01 22:50:00.000
2003-04-02 09:00:00.000
2003-04-05 09:35:00.000

25.

Функция & Описание
DATE
SQLite функция date - очень мощная функция, которая может вычислять дату и возвращать ее
в формате 'YYYY-MM-DD'.
DATETIME
SQLite функция datetime - очень мощная функция, которая может вычислять значение
даты/времени и возвращать его в формате 'YYYY-MM-DD HH:MM:SS'.
JULIANDAY
SQLite функция julianday берет дату, применяет модификаторы, а затем возвращает дату как
юлианский день. Юлианский день - это количество дней с 12:00 24 ноября 4714 г. до н.э. по
гринвичскому времени по григорианскому календарю. Функция julianday возвращает дату в
виде числа с плавающей запятой.
NOW
В SQLite нет функции, называемой функцией now, а «now» - это параметр временной строки,
который используется в различных функциях SQLite для получения текущей даты и времени.
На первый взгляд это может показаться немного запутанным, поскольку в других базах
данных SQL есть встроенная функция, целью которой является возвращение текущей даты и
времени. SQLite делает это по-другому.
STRFTIME
SQLite функция strftime - это очень мощная функция, которая позволяет вам возвращать
отформатированную дату, а также выполнять вычисления дат в эту дату. Эта функция
возвращает дату в виде текстового представления.
TIME
SQLite функция time - это очень мощная функция, которая может вычислять время и
возвращать его в формате 'HH-MM-SS'.

26.

Особенности обработки дат и времени в СУБД SQLite
Конкатенация даты (Text) и времени (Text) в SQLite
Select date||" "||time_out dt
from trip join pass_in_trip on
trip.trip_no=pass_in_trip.trip_no
Конкатенация даты (Text) и времени (Text) в
SQLite
Select DATE(date)||" "||TIME(time_out) dt
from trip join pass_in_trip on
trip.trip_no=pass_in_trip.trip_no
dt
-------------------------------------2003-04-29 00:00:00 1900-01-01 14:30:00
2003-04-05 00:00:00 1900-01-01 16:20:00
2003-04-08 00:00:00 1900-01-01 16:20:00
2003-04-08 00:00:00 1900-01-01 16:20:00
……..
dt
------------------2003-04-29 14:30:00
2003-04-05 16:20:00
2003-04-08 16:20:00
2003-04-08 16:20:00
2003-04-02 09:00:00
2003-04-05 09:35:00
…….

27.

STRFTIME
Синтаксис функции strftime в SQLite:
strftime(format, timestring [, modifier1, modifier2, ... modifier_n ] )
Format
Пояснение
%d
День месяца (1-31)
%f
Секунды с долями секунды (SS.sss)
%H
Час на 24-часовых часах (00-23)
%j
День года (001-366)
%J
Юлианский номер дня
(DDDDDDD.ddddddd)
%m
Месяц (01-12)
%M
Минуты (00-59)
%s
Секунды с 1970-01-01
%S
Секунды (00-59)
%w
День недели (0-6) (0 = воскресенье,
1 = понедельник, 2 = вторник, 3 =
среда, 4 = четверг, 5 = пятница, 6 =
суббота)
%W
Номер недели в году (00-53) Первый
понедельник - начало недели 1.
%Y
Год с веком (гггг)
%%
% как литерал
Регистр ВАЖЕН!

28.

Select date, strftime('%Y', date) YEAR, strftime('%m', date) MONTH, strftime('%d', date) DAY
from pass_in_trip
Date
YEAR MONTH DAY
---------------------------- ---------- ---------2003-04-29 00:00:00 2003 04
29
2003-04-05 00:00:00 2003 04
05
2003-04-08 00:00:00 2003 04
08
2003-04-08 00:00:00 2003 04
08
…….
Select time_out, strftime('%H', time_out) hour, strftime('%M', time_out) minute,
strftime('%S', time_out) second
from trip
time_out
------------------1900-01-01 14:30:00
1900-01-01 08:12:00
1900-01-01 16:20:00
1900-01-01 09:00:00
1900-01-01 09:35:00
……
hour
---------14
08
16
09
09
minute second
---------- ---------30
00
12
00
20
00
00
00
35
00