How can I insert minute:seconds format time interval to postgresql in right way?-CodePudding

I need to enter the time into the database in the format minutes:seconds. I have created the table as follows:

'CREATE TABLE IF NOT EXISTS players (id SERIAL PRIMARY KEY, DATE DATE NOT NULL, NAME varchar(32), TIME_ON_ICE interval)

I enter the information into the database as follows:

sql_string = """ INSERT INTO players (DATE, NAME, TIME_ON_ICE) VALUES (%s,%s,%s) """

The output is as follows:

cur.execute("SELECT * FROM players ORDER BY TIME_ON_ICE DESC ")
[(177,
  datetime.date(2022, 1, 16),
  'Morgan Rielly',
  datetime.timedelta(days=1, seconds=13260)),
 (201,
  datetime.date(2022, 1, 18),
  'Roman Josi',
  datetime.timedelta(days=1, seconds=13080)),
 (250,
  datetime.date(2022, 1, 29),
  'Neal Pionk',
  datetime.timedelta(days=1, seconds=7860))]

How do I get the minutes and seconds in the output? And how do I correctly enter the minutes and seconds in the table in my case?

CodePudding user response：

if you want to convert your timedelta to minutes and seconds, do like this :

import datetime

duration = datetime.timedelta(days = 1, seconds=13080)
totalsecondes = duration.total_seconds()
hours = totalsecondes //3600
minutes = (totalsecondes 600) // 60
secondes =(totalsecondes 600)`
print ("%d:%d" %(minutes ,secondes))

CodePudding user response：

You can save total time as seconds quantity in INT column and convert in minute:second format when retrieving data, something like:

CREATE TABLE IF NOT EXISTS players (
    id SERIAL PRIMARY KEY, 
    DATE DATE NOT NULL, 
    NAME varchar(32), 
    TIME_ON_ICE INT
);


INSERT INTO players (DATE, NAME, TIME_ON_ICE) 
VALUES 
('2000-01-01', 'Morgan Rielly', 1661) , 
('2000-01-01', 'Roman Josi', 1289) , 
('2000-01-01', 'Neal Pionk', 774);


SELECT 
ID, DATE, NAME, TIME_ON_ICE, 
TO_CHAR((TIME_ON_ICE||'second')::interval, 'MI:SS') AS minute_second
FROM players ORDER BY TIME_ON_ICE DESC;