fix time duration values minutes:seconds in postgresql-CodePudding

i have a boxscore data set of a basketball team with mm:ss played (ex. 44:46) when i uploaded the csv to the table the values whose greater than 24 somehow became hh:mm (ex. 44:46 became 44:46:00) while the others just stayed the same (ex. 5:04), it is currently saved as a varchar data type

how do i fix it so i can use the values to get for example the player with the highest time played in the team or the average time played for each player

CodePudding user response：

/*input*/
create table tab1( interval varchar(100));
insert into tab1 values( '44:46:00' );
insert into tab1 values( '5:04' );
/*Query*/  
select case when charindex(':',interval,4) = 0 then interval  
else left(interval,charindex(':',interval,4)-1) end from tab1

CodePudding user response：

WITH formatted_time AS (
    SELECT 
        player, 
        CAST(SUBSTRING(time_played, 1, 2) AS INT) * 60   CAST(SUBSTRING(time_played, 4, 2) AS INT) AS time_in_seconds
    FROM 
        your_table
)
SELECT 
    player, 
    time_in_seconds, 
    time_in_seconds / 60 || ':' || time_in_seconds % 60 AS time_played_formatted
FROM 
    formatted_time