date 'yyyy-mm-dd'

time 'hh:mm:ss.zzz'

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)

• Работник: новая
• Банк: старая
• Бухгалтерия: новая (но надо доплатить)

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 (
    StudentId 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 not null,
    nd date not null
)

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 * from StudentGroups where nd = inf

select * from StudentGroups
where bg <= :time and :time < nd

select * from StudentGroups

select sum(cast(nd - bg as float) * price) 
         / sum(cast(nd - bg as float))
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 (
    StudentId 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)