Операторы и функции обработки даты/времени

1.

Лекция
ОПЕРАТОРЫ И ФУНКЦИИ
ОБРАБОТКИ ДАТЫ/ВРЕМЕНИ

2.

СУБД MS SQL Server
Моисеенко С. SQL. Задачи и решения : интерактивный учебник / С.
Моисеенко. – URL: http://www.sql-tutorial.ru/ (01.09.2020). – Текст :
электронный.
СУБД SQLite
• Типы данных SQLite. – URL: https://oracleplsql.ru/data-types-sqlite.html
• Функции даты и времени в SQLite. – URL:
• https://codernotes.ru/articles/bazy-dannyh-t-sql/funkcii-daty-i-vremeni-vsqlite.html
• Работа с датами и временем. – URL: https://metanit.com/sql/sqlite/6.2.php
СУБД PostgreSQL
9.9. Операторы и функции даты/времени. – URL:
https://postgrespro.ru/docs/postgresql/14/functions-datetime

3.

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

4.

СУБД MS SQL Server
• Функция DATEPART
• Функция DATEADD
• Функция DATEDIFF
• Функция DATENAME
• Первый день недели
• Функция DATEFROMPARTS

5.

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

6.

СУБД MS SQL Server
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
….

7.

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

8.

СУБД MS SQL Server
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
….

9.

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

10.

СУБД MS SQL Server
Определим время вылета и прилета в минутах
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

11.

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

12.

СУБД MS SQL Server
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;

13.

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

14.

СУБД MS SQL Server
Задача
Определить количество дней, прошедших между
первым и последним совершенными рейсами.
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));

15.

СУБД MS SQL Server
Задача
Определить продолжительность рейса 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

16.

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

17.

СУБД MS SQL Server
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

18.

СУБД MS SQL Server
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

19.

СУБД MS SQL Server
Задача
Определить, какой будет день через неделю после
последнего полета.
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.

20.

СУБД MS SQL Server
Функция DATENAME
DATENAME( datepart, date )
Функция возвращает символьное представление составляющей
(datepart) указанной даты (date).
SELECT DATENAME(weekday, '20221026' )
+ ', '
+ DATENAME(day, '20221026')
+''
+ DATENAME(month, '20221026')
+''
+ DATENAME(year,'20221026');
Wednesday, 26 October 2022

21.

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

22.

СУБД MS SQL Server
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’)

23.

Особенности обработки дат и времени в СУБД SQLite
Источник: https://oracleplsql.ru/data-types-sqlite.html
Обобщенные типы данных:
Тип данных синтаксис
TEXT
DATE
INTEGER
NUMERIC
DATETIME
REAL
TIMESTAMP
NONE
TIME
Пояснение
Эквивалент NUMERIC
Эквивалент NUMERIC
Эквивалент NUMERIC
Эквивалент NUMERIC
Тип атрибутов даты и времени можно описать как Т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

24.

Особенности обработки дат и времени в СУБД 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

25.

Особенности обработки дат и времени в СУБД 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

26.

Функция & Описание
СУБД SQLite
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'.

27.

Особенности обработки дат и времени в СУБД SQLite
Со слайда 25
Конкатенация даты (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
…….

28.

СУБД SQLite
STRFTIME
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
%M
%s
%S
Месяц (01-12)
Минуты (00-59)
Секунды с 1970-01-01
Секунды (00-59)
%w
День недели (0-6) (0 = воскресенье, 1 = понедельник, 2 =
вторник, 3 = среда, 4 = четверг, 5 = пятница, 6 = суббота)
%W
%Y
%%
Номер недели в году (00-53) Первый понедельник начало недели 1.
Год с веком (гггг)
% как литерал

29.

Особенности обработки дат и времени в СУБД SQLite
Select
date,
strftime('%Y', date) YEAR,
strftime('%m', date) MONTH,
strftime('%d', date) DAY
from pass_in_trip
Select
from trip
time_out,
strftime('%H', time_out) hour,
strftime('%M', time_out) minute,
strftime('%S', time_out) second
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
…….
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
minute second
---------- ---------- ---------14
30
00
08
12
00
16
20
00
09
00
00
09
35
00

30.

Особенности обработки дат и времени в СУБД PostgreSQL
9.9. Операторы и функции даты/времени. – URL:
https://postgrespro.ru/docs/postgresql/14/functions-datetime
select
date '2001-09-28' as a,
date '2001-09-28' + 7 as b,
date '2001-10-01' - 7 as c,
date '2001-09-28' + interval '1 hour' as d,
date '2001-09-28' - interval '1 hour' as e,
date '2001-09-28' + interval '1 year' as f,
date '2001-09-28' - interval '1 year' as g,
date '2001-09-28' + interval '1 month' as h,
date '2001-09-28' - interval '1 month'as i ,
date '2001-09-28' + time '03:00' as j,
timestamp '2001-09-28 01:00' + interval '23 hours' as k,
time '01:00' + interval '3 hours' as l,
time '05:00' - time '03:00' as m,
date '2001-10-01' - date '2001-09-28' as n

31.

Особенности обработки дат и времени в СУБД PostgreSQL
select age(date '2001-04-10', date '1957-06-13'),
age(timestamp '2001-04-10', timestamp '1957-06-13'),
age(timestamp '1957-06-13'),
date_part('hour', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40'),
date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('day', timestamp '2001-02-16 20:38:40'),
date_part('month', interval '2 years 3 months'),
date_part('year', interval '2 years 3 months')