Similar presentations:
Операторы и функции обработки даты/времени
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 ServerTransact-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 ServerDATEPART(datepart , date)
Функция возвращает целое число,
представляющее собой указанную аргументом datepart
часть заданной вторым аргументом даты (date).
Значения аргумента
Допустимые сокращения
год
yy
месяц
mm
день
dd
час
hh
минуты
mi
секунды
ss
Weekday — день недели
dw
6.
СУБД MS SQL Serverselect 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 Serverselect 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 ServerSELECT 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 ServerDatepart
Допустимые сокращения
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 ServerSELECT 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 Serverselect 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.
СУБД SQLiteSTRFTIME
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.
Особенности обработки дат и времени в СУБД SQLiteSelect
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.
Особенности обработки дат и времени в СУБД PostgreSQL9.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.
Особенности обработки дат и времени в СУБД PostgreSQLselect 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')