Similar presentations:
Функции 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.
Функция DATEPARTDATEPART(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 CASEWHEN 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.
Функция DATEADDDATEADD (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.
Функция DATENAMEDATENAME( 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) DAYfrom 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