27 lines
751 B
PL/PgSQL
27 lines
751 B
PL/PgSQL
CREATE OR REPLACE FUNCTION public.day_epoch(dtime timestamp with time zone)
|
|
RETURNS integer
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
begin
|
|
SET TIME ZONE 'Europe/Berlin';
|
|
|
|
RETURN FLOOR(EXTRACT(EPOCH FROM dtime)/(24*3600));
|
|
end;
|
|
$function$
|
|
;
|
|
|
|
|
|
CREATE TABLE entries (
|
|
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
|
|
started TIMESTAMP WITH TIME ZONE DEFAULT date_trunc('minute', now()) NOT NULL,
|
|
till TIMESTAMP WITH TIME ZONE DEFAULT null,
|
|
"day" INT DEFAULT day_epoch(now()) NOT NULL,
|
|
"comment" TEXT DEFAULT null,
|
|
isfree BOOLEAN DEFAULT false NOT NULL
|
|
);
|
|
CREATE TABLE weekly_worktimes (
|
|
"year" int2 NOT NULL,
|
|
"month" int2 NOT NULL,
|
|
worktime int2 NOT NULL,
|
|
CONSTRAINT weekly_worktime_pk PRIMARY KEY (month, year)
|
|
); |