Hi,

I need to display a number of seconds in the <minutes>:<seconds>
notation, e.g. 100 seconds shall return '01:40'.
Hours shall NOT be displayed, so for example 7201 seconds shall return
'120:01', not '02:00:01'...

Has anyone a hint how I can achieve this?

Thanx and best regards,
Jens

On Feb 29, 8:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
As an example:

select trunc(to_number(to_char(sysdate, 'SSSSS'))/60) ||':'||
round((to_number(to_char(sysdate, 'SSSSS'))/60 -
trunc(to_number(to_char(sysdate, 'SSSSS'))/60))*60, 0)
from dual;

Produces the following output:

580:25

where the first number is minutes and the last number is seconds. The
example uses sysdate; you can alter that to use whatever time value
you've been supplied.


David Fitzjarrell

On Feb 29, 8:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
As an example:

select trunc(to_number(to_char(sysdate, 'SSSSS'))/60) ||':'||
round((to_number(to_char(sysdate, 'SSSSS'))/60 -
trunc(to_number(to_char(sysdate, 'SSSSS'))/60))*60, 0)
from dual;

Produces the following output:

580:25

where the first number is minutes and the last number is seconds. The
example uses sysdate; you can alter that to use whatever time value
you've been supplied.


David Fitzjarrell

On Feb 29, 8:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
As an example:

select trunc(to_number(to_char(sysdate, 'SSSSS'))/60) ||':'||
round((to_number(to_char(sysdate, 'SSSSS'))/60 -
trunc(to_number(to_char(sysdate, 'SSSSS'))/60))*60, 0)
from dual;

Produces the following output:

580:25

where the first number is minutes and the last number is seconds. The
example uses sysdate; you can alter that to use whatever time value
you've been supplied.


David Fitzjarrell

On Feb 29, 8:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
As an example:

select trunc(to_number(to_char(sysdate, 'SSSSS'))/60) ||':'||
round((to_number(to_char(sysdate, 'SSSSS'))/60 -
trunc(to_number(to_char(sysdate, 'SSSSS'))/60))*60, 0)
from dual;

Produces the following output:

580:25

where the first number is minutes and the last number is seconds. The
example uses sysdate; you can alter that to use whatever time value
you've been supplied.


David Fitzjarrell

On Feb 29, 9:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
It looks like David Fitzjarrell has provided a much more easy to
follow solution, but here is something that you can experiment with:
SELECT
TO_CHAR(SYSDATE,'HH24:MI:SS') CTIME,
TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60 TEST1,
TO_NUMBER(TO_CHAR(SYSDATE,'MI')) TEST2,
TO_CHAR(SYSDATE,':SS') TEST3
FROM
DUAL;

CTIME TEST1 TEST2 TES
-------- ---------- ---------- ---
10:43:33 600 43 :33

Just add TEST1 to TEST2, convert the result using TO_CHAR, and append
TEST3 using ||
643:33

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Feb 29, 9:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
It looks like David Fitzjarrell has provided a much more easy to
follow solution, but here is something that you can experiment with:
SELECT
TO_CHAR(SYSDATE,'HH24:MI:SS') CTIME,
TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60 TEST1,
TO_NUMBER(TO_CHAR(SYSDATE,'MI')) TEST2,
TO_CHAR(SYSDATE,':SS') TEST3
FROM
DUAL;

CTIME TEST1 TEST2 TES
-------- ---------- ---------- ---
10:43:33 600 43 :33

Just add TEST1 to TEST2, convert the result using TO_CHAR, and append
TEST3 using ||
643:33

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Feb 29, 9:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
It looks like David Fitzjarrell has provided a much more easy to
follow solution, but here is something that you can experiment with:
SELECT
TO_CHAR(SYSDATE,'HH24:MI:SS') CTIME,
TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60 TEST1,
TO_NUMBER(TO_CHAR(SYSDATE,'MI')) TEST2,
TO_CHAR(SYSDATE,':SS') TEST3
FROM
DUAL;

CTIME TEST1 TEST2 TES
-------- ---------- ---------- ---
10:43:33 600 43 :33

Just add TEST1 to TEST2, convert the result using TO_CHAR, and append
TEST3 using ||
643:33

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Feb 29, 9:32*am, Jens Riedel <Jens... (AT) gmx (DOT) de> wrote:
It looks like David Fitzjarrell has provided a much more easy to
follow solution, but here is something that you can experiment with:
SELECT
TO_CHAR(SYSDATE,'HH24:MI:SS') CTIME,
TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))*60 TEST1,
TO_NUMBER(TO_CHAR(SYSDATE,'MI')) TEST2,
TO_CHAR(SYSDATE,':SS') TEST3
FROM
DUAL;

CTIME TEST1 TEST2 TES
-------- ---------- ---------- ---
10:43:33 600 43 :33

Just add TEST1 to TEST2, convert the result using TO_CHAR, and append
TEST3 using ||
643:33

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Jens Riedel schrieb:
You already got some ideas how to solve your problem, just in case, your
input is not time but simply a number of seconds, you can do something
like this

SQL> with t as (
2 select 100 s from dual union all
3 select 7201 from dual)
4 select s,trunc(s/60)||':'||mod(s,60) "M:S" from t;

S M:S
----------
---------------------------------------------------------------------------------
100 1:40
7201 120:1

Best regards

Maxim