Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE ticket
(
  ticket_id INTEGER NOT NULL,
  working_time VARCHAR (30) NULL DEFAULT NULL CHECK (working_time != '')
);
INSERT INTO ticket VALUES (18, '20.02.2021,15:00,17:00');
INSERT INTO ticket VALUES (18, '20.02.2021,15:00,17:00');
INSERT INTO ticket VALUES (18, '20.02.2021,15:00,17:00');

INSERT INTO ticket VALUES (20, '20.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, '_rubbish__');
INSERT INTO ticket VALUES (20, '20.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, NULL);

INSERT INTO ticket VALUES (20, '21.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, '_rubbish__');
INSERT INTO ticket VALUES (20, '21.02.2021,12:00,14:15');
INSERT INTO ticket VALUES (20, NULL);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

CREATE INDEX tk_id ON ticket USING BTREE (ticket_id);
CREATE INDEX wt_ix ON ticket USING BTREE (working_time);
SELECT * FROM ticket;


ticket_id
working_time


18
20.02.2021,15:00,17:00

18
20.02.2021,15:00,17:00

18
20.02.2021,15:00,17:00

20
20.02.2021,12:00,14:15

20
_rubbish__

20
20.02.2021,12:00,14:15

20
null

20
21.02.2021,12:00,14:15

20
_rubbish__

20
21.02.2021,12:00,14:15

20
null


CREATE FUNCTION try_cast_date(p_in TEXT, p_default INT DEFAULT NULL)
   RETURNS DATE
AS
$$
BEGIN
  BEGIN
    RETURN TO_DATE(SUBSTRING($1 FROM 1 FOR 10), 'DD.MM.YYYY')DATE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN p_default;
  END;
END;
$$
LANGUAGE PLPGSQL IMMUTABLE;
SELECT 
  try_cast_date(working_time),
  pg_typeof(try_cast_date(working_time))
FROM ticket;


try_cast_date
pg_typeof


2021-02-20
date

2021-02-20
date

2021-02-20
date

2021-02-20
date

null
date

2021-02-20
date

null
date

2021-02-21
date

null
date

2021-02-21
date

null
date


CREATE FUNCTION try_cast_time
  (p_in TEXT, p_s INT, p_e INT, p_default INT DEFAULT NULL)
   RETURNS TIMETZ
AS
$$
BEGIN
  BEGIN
    RETURN SUBSTRING($1 FROM $2 FOR $3)::TIMETZ;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN p_default;
  END;
END;
$$
language plpgsql;
SELECT 
  try_cast_time(working_time, 12, 5), 
  pg_typeof(try_cast_time(working_time, 12, 5))
FROM ticket;


try_cast_time
pg_typeof


15:00:00+01
time with time zone

15:00:00+01
time with time zone

15:00:00+01
time with time zone

12:00:00+01
time with time zone

null
time with time zone

12:00:00+01
time with time zone

null
time with time zone

12:00:00+01
time with time zone

null
time with time zone

12:00:00+01
time with time zone

null
time with time zone


SELECT DISTINCT
  ticket_id,
  try_cast_date(working_time)::DATE,
  SUM((try_cast_date(working_time) + try_cast_time(working_time, 18, 5)) - 
  (try_cast_date(working_time) + try_cast_time(working_time, 12, 5))) 
    OVER (PARTITION BY ticket_id, try_cast_date(working_time)::DATE)
  AS ts_diff
FROM ticket
WHERE try_cast_date(working_time)::DATE IS NOT NULL
ORDER BY ticket_id, try_cast_date(working_time)::DATE


ticket_id
try_cast_date
ts_diff


18
2021-02-20
06:00:00

20
2021-02-20
04:30:00

20
2021-02-21
04:30:00


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT DISTINCT
  ticket_id,
  try_cast_date(working_time)::DATE,
  SUM((try_cast_date(working_time) + try_cast_time(working_time, 18, 5)) - 
  (try_cast_date(working_time) + try_cast_time(working_time, 12, 5))) 
    OVER (PARTITION BY ticket_id, try_cast_date(working_time)::DATE)
  AS ts_diff
FROM ticket
WHERE try_cast_date(working_time)::DATE IS NOT NULL
ORDER BY ticket_id, try_cast_date(working_time)::DATE


QUERY PLAN


Unique  (cost=21.04..21.15 rows=11 width=24) (actual time=0.241..0.261 rows=3 loops=1)

  Output: ticket_id, (try_cast_date((working_time)::text, NULL::integer)), (sum((((try_cast_date((working_time)::text, NULL::integer)) + try_cast_time((working_time)::text, 18, 5, NULL::integer)) - ((try_cast_date((working_time)::text, NULL::integer)) + try_cast_time((working_time)::text, 12, 5, NULL::integer)))) OVER (?))

  Buffers: shared hit=1

  ->  Sort  (cost=21.04..21.07 rows=11 width=24) (actual time=0.241..0.242 rows=7 loops=1)

        Output: ticket_id, (try_cast_date((working_time)::text, NULL::integer)), (sum((((try_cast_date((working_time)::text, NULL::integer)) + try_cast_time((working_time)::text, 18, 5, NULL::integer)) - ((try_cast_date((working_time)::text, NULL::integer)) + try_cast_time((working_time)::text, 12, 5, NULL::integer)))) OVER (?))

        Sort Key: ticket.ticket_id, (try_cast_date((ticket.working_time)::text, NULL::integer)), (sum((((try_cast_date((ticket.working_time)::text, NULL::integer)) + try_cast_time((ticket.working_time)::text, 18, 5, NULL::integer)) - ((try_cast_date((ticket.working_time)::text, NULL::integer)) + try_cast_time((ticket.working_time)::text, 12, 5, NULL::integer)))) OVER (?))

        Sort Method: quicksort  Memory: 25kB

        Buffers: shared hit=1

        ->  WindowAgg  (cost=6.80..20.85 rows=11 width=24) (actual time=0.200..0.236 rows=7 loops=1)

              Output: ticket_id, (try_cast_date((working_time)::text, NULL::integer)), sum((((try_cast_date((working_time)::text, NULL::integer)) + try_cast_time((working_time)::text, 18, 5, NULL::integer)) - ((try_cast_date((working_time)::text, NULL::integer)) + try_cast_time((working_time)::text, 12, 5, NULL::integer)))) OVER (?)

              Buffers: shared hit=1

              ->  Sort  (cost=6.80..6.83 rows=11 width=86) (actual time=0.156..0.157 rows=7 loops=1)

                    Output: ticket_id, (try_cast_date((working_time)::text, NULL::integer)), working_time

                    Sort Key: ticket.ticket_id, (try_cast_date((ticket.working_time)::text, NULL::integer))

                    Sort Method: quicksort  Memory: 25kB

                    Buffers: shared hit=1

                    ->  Seq Scan on public.ticket  (cost=0.00..6.61 rows=11 width=86) (actual time=0.026..0.146 rows=7 loops=1)

                          Output: ticket_id, try_cast_date((working_time)::text, NULL::integer), working_time

                          Filter: (try_cast_date((ticket.working_time)::text, NULL::integer) IS NOT NULL)

                          Rows Removed by Filter: 4

                          Buffers: shared hit=1

Planning Time: 0.081 ms

Execution Time: 0.295 ms


SELECT 
  ticket_id,
  try_cast_date(working_time) + 
    try_cast_time(working_time, 18, 5) AS et_ts,
  try_cast_date(working_time) + 
    try_cast_time(working_time, 12, 5) AS st_ts, 
  
  pg_typeof(try_cast_date(working_time) + try_cast_time(working_time, 12, 5)) AS tp,
  
  (try_cast_date(working_time) + try_cast_time(working_time, 18, 5)) - 
  (try_cast_date(working_time) + try_cast_time(working_time, 12, 5)) AS ts_diff
  
FROM ticket;


ticket_id
et_ts
st_ts
tp
ts_diff


18
2021-02-20 16:00:00+00
2021-02-20 14:00:00+00
timestamp with time zone
02:00:00

18
2021-02-20 16:00:00+00
2021-02-20 14:00:00+00
timestamp with time zone
02:00:00

18
2021-02-20 16:00:00+00
2021-02-20 14:00:00+00
timestamp with time zone
02:00:00

20
2021-02-20 13:15:00+00
2021-02-20 11:00:00+00
timestamp with time zone
02:15:00

20
null
null
timestamp with time zone
null

20
2021-02-20 13:15:00+00
2021-02-20 11:00:00+00
timestamp with time zone
02:15:00

20
null
null
timestamp with time zone
null

20
2021-02-21 13:15:00+00
2021-02-21 11:00:00+00
timestamp with time zone
02:15:00

20
null
null
timestamp with time zone
null

20
2021-02-21 13:15:00+00
2021-02-21 11:00:00+00
timestamp with time zone
02:15:00

20
null
null
timestamp with time zone
null


SELECT ticket_id, SUBSTRING(working_time FROM 12 FOR 5)
FROM ticket;


ticket_id
substring


18
15:00

18
15:00

18
15:00

20
12:00

20


20
12:00

20
null

20
12:00

20


20
12:00

20
null


SELECT 
  TO_DATE(SUBSTRING('21.02.2021' FROM 1 FOR 10), 'DD.MM.YYYY') 
FROM ticket;


to_date


2021-02-21

2021-02-21

2021-02-21

2021-02-21

2021-02-21

2021-02-21

2021-02-21

2021-02-21

2021-02-21

2021-02-21

2021-02-21


SELECT 
  TO_DATE(SUBSTRING(working_time FROM 1 FOR 10), 'DD.MM.YYYY') 
FROM ticket;
ERROR:  invalid value "_r" for "DD"
DETAIL:  Value must be an integer.


SELECT TO_DATE('20170103','YYYYMMDD'); 


to_date


2017-01-03


substring(string [from <str_pos>] [for <ext_char>])
ERROR:  syntax error at or near "substring"
LINE 1: substring(string [from <str_pos>] [for <ext_char>])
        ^

ticket_id	working_time
18	20.02.2021,15:00,17:00
18	20.02.2021,15:00,17:00
18	20.02.2021,15:00,17:00
20	20.02.2021,12:00,14:15
20	_rubbish__
20	20.02.2021,12:00,14:15
20	null
20	21.02.2021,12:00,14:15
20	_rubbish__
20	21.02.2021,12:00,14:15
20	null

try_cast_date	pg_typeof
2021-02-20	date
2021-02-20	date
2021-02-20	date
2021-02-20	date
null	date
2021-02-20	date
null	date
2021-02-21	date
null	date
2021-02-21	date
null	date

try_cast_time	pg_typeof
15:00:00+01	time with time zone
15:00:00+01	time with time zone
15:00:00+01	time with time zone
12:00:00+01	time with time zone
null	time with time zone
12:00:00+01	time with time zone
null	time with time zone
12:00:00+01	time with time zone
null	time with time zone
12:00:00+01	time with time zone
null	time with time zone

ticket_id	try_cast_date	ts_diff
18	2021-02-20	06:00:00
20	2021-02-20	04:30:00
20	2021-02-21	04:30:00

ticket_id	et_ts	st_ts	tp	ts_diff
18	2021-02-20 16:00:00+00	2021-02-20 14:00:00+00	timestamp with time zone	02:00:00
18	2021-02-20 16:00:00+00	2021-02-20 14:00:00+00	timestamp with time zone	02:00:00
18	2021-02-20 16:00:00+00	2021-02-20 14:00:00+00	timestamp with time zone	02:00:00
20	2021-02-20 13:15:00+00	2021-02-20 11:00:00+00	timestamp with time zone	02:15:00
20	null	null	timestamp with time zone	null
20	2021-02-20 13:15:00+00	2021-02-20 11:00:00+00	timestamp with time zone	02:15:00
20	null	null	timestamp with time zone	null
20	2021-02-21 13:15:00+00	2021-02-21 11:00:00+00	timestamp with time zone	02:15:00
20	null	null	timestamp with time zone	null
20	2021-02-21 13:15:00+00	2021-02-21 11:00:00+00	timestamp with time zone	02:15:00
20	null	null	timestamp with time zone	null

ticket_id	substring
18	15:00
18	15:00
18	15:00
20	12:00
20
20	12:00
20	null
20	12:00
20
20	12:00
20	null