Dúvida sobre manipulação de minutos e segundos

[MR7]

Boa noite pessoal. Seguinte, estou cursando pela primeira vez na vida uma disciplina relacionado a banco de dados, e nesse caso, Oracle. Aprendi até o momento coisas simples como criar tabelas, inserir valores e principalmente o uso e abuso do select, hehe. Então, preciso fazer um exercício que está me deixando com muitas dúvidas na questão do tempo. Pra deixar bem mastigado, aqui tem uma imagem com as tabelas que preciso criar:



Com base nela, montei isso aqui:

Selecionar tudo

create table CD
(codigo number(5) not null,
nome varchar2(50) not null,
dataCompra date not null,
valorPago number(5,2),
localCompra varchar2(50),
album varchar2(50),
constraint pk_cd primary key (codigo));

create table Musicas
(codCD number(5) not null,
numero number(2) not null,
nome varchar2(50) not null,
artista varchar2(50) not null,
tempo date not null,
constraint pk_musicas primary key (codCD, numero),
constraint fk_musicas foreign key (codCD) references CD (codigo));

Agora, para inserir os valores do tempo, na tabela Musicas, usei esse formato:

Selecionar tudo

to_date('05:22','mi:ss')

Até aqui tudo ok, mas agora as questões:

h) Mostre o tempo total de músicas cadastradas.
j) Mostre o tempo total de músicas por CD.
l) Mostre a média de duração das músicas cadastradas.

E é isso que preciso fazer, tenho que manipular esses dados que inseri para responder essas questões, só que está bem complicado, tentei tudo que aprendi até o momento e não consegui resolver. Agradeço antecipado pela ajuda, desculpem pela quantidade enorme de texto. Valeu... \o

[rogenaro]

Vamos lá:

Como seu campo tempo é do tipo date, vale lembrar que a data também será armazenada na base (se omitida, será inserida o primeiro dia do mês da data atual). Ou seja, o comando

Selecionar tudo

to_date('05:22','mi:ss')

irá inserir, hoje, o valor '01/05/2008 00:05:22' na base. Mas se rodado em um mês, irá inserir '01/06/2008 00:05:22', e assim por diante.

Se for necessário utilizar o campo do tipo date, seria interessante especificar um dia, mês e ano iguais para todos os registros, apenas para que fiquem consistentes.

De qualquer forma, para obter a duração de uma música, você fará:

Selecionar tudo

select tempo - trunc(tempo) from Musicas;

O comando trunc em um campo date irá retornar a data à 00:00. Desta forma, você irá obter esta diferença em dias.

Para converter estes dias em minutos novamente, você poderá fazer:

Selecionar tudo

select trunc(intervalo_em_dias)||' dias, ' 
     ||to_char( to_date('00010101000000', 'yyyymmddhh24miss') 
              + intervalo_em_dias
              , 'hh24:mi:ss'
              )
from 
(
  select tempo - trunc(tempo) intervalo_em_dias 
  from   Musicas
);

A consulta acima irá truncar a parte decimal do tempo de duração de determinada música, informando quantos dias de duração ela tem.
Em seguida, pegamos uma data qualquer (à meia noite - isto é importante) à qual somamos o tempo de duração da música, que por fim convertemos de volta para char, pegando apenas as horas, minutos e segundos, referentes ao tempo de duaração desta música.

Os itens h, j e l são apenas variações desta lógica, bastando somar os intervalos para depois converter novamente para o formato de saída.

Espero ter ajudado um pouco.

[MR7]

Valeu Rafael, já ajudou bastante. Gostaria de saber só mais um detalhe, no início do teu post tu ressaltou que meu tempo era date então por isso era esse o método para resolver. Tu acha que teria outra maneira de implementar o tempo? Porque ele não precisa necessariamente ser do tipo date, botei assim porque na verdade tentei fazer number e não saiu como esperava. Obrigado... o/

[dr_gori]

O bom de usar DATE é que é possível SUBTRARIR duas datas. (retornando assim a qtd de dias entre as duas)...

Exemplo:
DATA1 - DATA2, digamos que retorne 0,5 -> isso significa que são 12 horas entre as duas...

[rodfbar]

eu tive q fazer um trabalho onde utilizava datas..

na verdade era um modulo novo para controle de ponto eletronico...

depois de penar um tempo com essas conversoes, acabei decidindo usar os campos de hora e minuto como number, sendo que a parte inteira do meu campo representa as horas e os valores decimais os minutos...

não tive problemas... apenas tive q ter controles para minutos < 60 e horas < 24 (horas < 24 pois não contava horas corridas, e sim armazenava a hora do dia em que foi gerado movimento do ponto)

bem é uma opção...

abrçs

[rogenaro]

MR7, na verdade não tem nada de errado na sua implementação, mesmo usando um outro tipo para o campo, 98% da forma de realizar estes cálculos seria igual.

Na verdade é mais uma questão de gosto, já que eu acho meio estranho consultar um campo de duração em uma tabela e encontrar uma data qualquer com as horas (já que o oracle sempre insere alguma data neste tipo de campo, mesmo que você informe apenas as horas em seu insert).

Pessoalmente, costumo criar um campo number, que armazena a duração em segundos/minutos/horas/dias, dependendo da precisão necessária.

Com esta informação, basta tranformar o número armazenado em dias (multiplicando por 60*60*24, caso o campo guarde o tempo em segundos, por exemplo), e então usando este valor para realizar a conversão, +- assim:

Selecionar tudo

select trunc(intervalo_em_dias)||' dias, '
     ||to_char( to_date('00010101000000', 'yyyymmddhh24miss')
              + intervalo_em_dias
              , 'hh24:mi:ss'
              )
from
(
  select duracao_em_segundos * (60*60*24) intervalo_em_dias
  from   Musicas
); 

A única vantagem prática em armazenar intervalos nesta forma é que não existe um limite para o intervalo que pode ser inserido (exceto pelo tamanho do campo, claro), enquanto que caso o campo seja do tipo date, será necessário definir uma data "padrão", que todas as aplicações que atualizam e consultam esta tabela deverão utilizar, para que seja possível recuperar intervalos superiores a 1 dia (o que, é claro, não será necessário em seu caso).

[MR7]

Valeu gente, obrigado por todas as respostas, abraços...