Question 89865 in PostgreSQL Question posted in
The official documentation can be found here.

fix time duration values minutes:seconds in postgresql

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?

Tags:

2

Answers


  1. 0 
    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
    Login or Signup to reply.
  2. 0 
    /*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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search