Parsing A String In Both Postgresql And Micrsoft Sql Server

I have the following data (all of the table DDL and data DML is available on the fiddle here (SQL Server) and here (PostgreSQL): I already have solutions, this question is about ef

Solution 1:

So you have a working version, which, as unwieldy as it is, is not necessarily badly-performing just because of its verboseness.

You asked however if there's a more efficient way, and for SQL Server (I can't comment on Postgres) you can greatly both simplify and improve performance by adding persisted computed columns and a supporting index on the date.

This removes the non-sargability of the query and allows the optimizer to fully utilise indexes for filtering and aggregating, and avoids the minimal overhead of parsing and casting the string values because that work is now done when the row is inserted/updated.

Add the computed columns:

altertable ticket add WorkingDate as Try_convert(date,Concat(Substring(working_time, 7, 4),SUBSTRING(working_time, 4, 2),SUBSTRING(working_time, 1, 2)),112) persisted 
altertable ticket add WorkingDuration as  DateDiff(minute,Try_convert(time,Substring (working_time, 12, 5),114 ) , Try_convert(time, Substring (working_time, 18, 5),114 )) persisted

Add a supporting index

create clustered index Ix_Id_WorkingDuration on ticket(ticket_id,workingdate)

And then your query becomes:

with w as (
    select ticket_Id, workingDate, Sum(workingDuration) d
    from ticket
    groupby ticket_id, workingDate
)
select ticket_id,
  workingdate as [The date],
  format(d / 60 * 100 + d % 60, '#:0#') hrs_worked_per_ticketfrom w
where d>0;

See amended Fiddle

Comparing to your original query is not going to yield any noticable improvments on such few rows but would be significantly better performing on a large data set, particulary if you needed to further filter by dates or ranges.

However the estimated execution plan suggests 18% for this version vs 82% for your original version.

Solution 2:

I have a PostgreSQL solution here - try_cast_time and try_cast_date are functions that I wrote, inspired by this post (the whole thread is helpful!):

SELECTDISTINCT
  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 (PARTITIONBY ticket_id, try_cast_date(working_time)::DATE)
  AS ts_diff
FROM ticket
WHERE try_cast_date(working_time)::DATEISNOTNULLORDERBY ticket_id, try_cast_date(working_time)::DATE

Result:

ticket_idtry_cast_datets_diff182021-02-20   06:00:00202021-02-20   04:30:00202021-02-21  04:30:00

Solution 3:

I have a SQL Server solution here (it's pretty horrible!):

WITH cte AS
(
  SELECT
    ticket_id,
    CAST
    (
      TRY_CONVERT
      (
        DATE, 
        SUBSTRING(working_time, 7, 4) +'.'+SUBSTRING(working_time, 4, 2) +'.'+SUBSTRING(working_time, 1, 2)
      ) AS DATETIME
    ) 
    +CAST
    (
      CAST
      (
        SUBSTRING
        (
          working_time, 12,  5
        ) ASTIME
      ) AS DATETIME
    ) AS st_dt,
    CAST
    (
      TRY_CONVERT
      (
        DATE, 
        SUBSTRING(working_time, 7, 4) +'.'+SUBSTRING(working_time, 4, 2) +'.'+SUBSTRING(working_time, 1, 2)
      ) AS DATETIME
    ) 
    +CAST
    (
      CAST
      (
        SUBSTRING
        (
          working_time, 18,  5
        ) ASTIME
      ) AS DATETIME
    ) AS et_dt
  FROM
    ticket
)
SELECT
  ticket_id AS "Ticket ID",
  TRY_CONVERT(date, et_dt) AS "The date",
  TRY_CONVERT
  (
    VARCHAR(8), 
    dateadd
    (
      second, 
      COALESCE(SUM
      (
        DATEDIFF(SECOND, st_dt, et_dt)
      ), 0), 
      0
    ),  
    108
  ) AS hrs_worked_per_ticket
FROM 
  cte
WHERE TRY_CONVERT(DATE, et_dt) ISNOTNULLGROUPBY ticket_id, TRY_CONVERT(DATE, et_dt)
ORDERBY ticket_id, TRY_CONVERT(DATE, et_dt);

Result:

TicketIDThedatehrs_worked_per_ticket182021-02-20               06:00:00202021-02-20               04:30:00202021-02-21               04:30:00