date 'yyyy-mm-dd'
time 'hh:mm:ss.zzz'
date 'yyyy-mm-dd'
datetime 'yyyy-mm-dd hh:mm:ss.zzz'
cast (days.fraction as datetime)
current_date, current_time, current_timestamp(n)
interval 'PyYmMdDThHmMsS'
cast(days.fraction as interval)
Событие | Дата | Статус |
---|---|---|
Записался в вуз | 15.07 | Абитуриент |
Принёс документы | 28.07 | Абитуриент с документами |
Приказ о зачислении | 06.08 | Поступивший |
Выдача документов | 01.09 | Первокурсник |
Событие | Дата | Высота |
---|---|---|
Геодезические измерения | 1856 | 8840 |
Топографические измерения | 1950 | 8848 |
Китайские измерения | 1975 | 8848.13 |
Измерение по GPS | 1998 | 8850 |
Повторные китайские измерения | 2005 | 8844.43 |
Событие | Факт | Документы |
---|---|---|
Родился ребёнок | Рождение | — |
Регистрация рождения | — | Свидетельство о рождении |
Изготовление паспорта | — | Паспорт |
Наступило XX лет | Начало действия | — |
create table entrants ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, status varchar(100), statusTimestamp timestamp )
create table students ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, ) create table studentGroups ( studentId int not null references students(id), groupId int not null references groups(id), bg date, nd date )
check (not exists ( select * from studentGroups s1 inner join studentGroups s2 on s1.studentId = s2.studentId where greatest(s1.bg, s2.bg) < least(s1.nd, s2.nd) ))
studentGroups g1 inner join studentGroups g2 on g1.groupId = g2.groupId using g1 (bg, nd) and g2 (bg, nd)
select g1.*, g2.*, greatest(g1.bg, g2.bg) as bg, least(g1.nd, g2.nd) as nd from studentGroups g1 inner join studentGroups g2 on g1.groupId = g2.groupId and greatest(g1.bg, g2.bg) < least(g1.nd, g2.nd)
t1 inner join t2 on condition using t1 (t1.bg, t1.nd) and t2 (t2.bg, t2.nd)
select t1.*, t2.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on condition and greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd)
select t1.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd) union all select t2.*, greatest(t1.bg, t2.bg) as bg, least(t1.nd, t2.nd) as nd from t1 inner join t2 on t1.value <> t2.value and greatest(t1.bg, t2.bg) < least(t1.nd, t2.nd)
select * from studentGroups where nd = inf
select * from studentGroups where bg <= :time and :time < nd
select * from studentGroups
select avg(cast(nd - bg as float) * price) from prices
insert into studentGroups(studentId, groupId, bg, nd) values (:studentId, :groupId, current_date, inf)
update studentGroups set nd = current_date where studentId = :studentId and nd = inf insert into studentGroups(studentId, groupId, bg, nd) values (:studentId, :groupId, current_date, inf)
update studentGroups set nd = current_date where studentId = :studentId and nd = inf
update studentGroups set nd = :bg where studentId = :sid and bg < :bg and :bg < nd
update studentGroups set :bg = :nd where studentId = :sid and bg < :nd and :nd < bg
delete from studentGroups where studentId = :sid and :bg <= bg and nd <= :nd
insert into studentGroups(studentId, groupId, bg, nd) values (:sid, :groupId, :bg, :nd)
insert into studentGroups select sid, groupId, :nd, nd from studentGroups where studentId = :sid and bg < :bg and :nd < nd
create table students ( id int not null primary key, firstName varchar(100) not null, lastName varchar(100) not null, ) create table studentGroups ( studentId int not null references students(id), groupId int not null references groups(id), when date, primary key (studentId, groupId, when) )
insert into studentGroups(studentId, groupId, when) values (:studentId, :groupId, current_date)