-1

I have a column that return seconds from a task and I'd like to convert it into hours and minutes Example: if the column has 16200 seconds I'd like to get the result as 4h30m

Also, need to get sum of all the values as hr and min,....sum(column name)=sum(hr)sum(min)

Surabhi Singh
  • 15
  • 5

1 Answers1

1

If your seconds column is a numeric type, you can use this:

SELECT TO_CHAR((16200 || ' second')::interval, 'fmHH24hfmMIm')

If its a string type, use this:

SELECT TO_CHAR('16200'::interval, 'fmHH24hfmMIm')

Of course, replace 16200 with your appropriate column name. Precede HH and MI with fm for Fill Mode. This will remove the preceding zero values from hour and minute.

Output: 4h30m

See Fiddle.

To get the SUM of all values, add the SUM function to your seconds column like so:

SELECT TO_CHAR(SUM(seconds_column::interval), 'fmHH24hfmMIm') FROM test_table

See Fiddle.

griv
  • 2,098
  • 2
  • 12
  • 15
  • how to get sum of all the values as hr and min,....sum(column name)=sum(hr)sum(min) – Surabhi Singh Sep 02 '22 at 18:27
  • Please update your question to include that, you only asked for the conversion of seconds into hours/minutes. – griv Sep 02 '22 at 18:30
  • I've updated my answer to include the `SUM`, but I would advise editing your question to make it more clear what you're asking. Your question may be viewed many times in the future by many people, so its important to create a clear question with examples, etc. See [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) for more information. – griv Sep 02 '22 at 18:46
  • using the above query gives error: Error: SQL parse failed Encountered ":" at line 1, column 41. More... org.apache.calcite.sql.parser.SqlParseException – Surabhi Singh Sep 04 '22 at 14:08
  • Can you add the query you tried to your question? – griv Sep 04 '22 at 14:21
  • I ran your query under your question comments. It works fine for me. Is there something wrong with your column you're using, should it be paidDurationInSeconds instead of durationInSeconds (based on your other comment query)? If that's not the problem, please revise your question. We need more sample data and your table structure or create a fiddle like I did so we can see exactly what's happening. – griv Sep 05 '22 at 04:14