Doubt about manipulation of minutes and seconds

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
MR7
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 06 May 2008 6:52 pm
Location: Caxias do Sul, RS

Good night people. Next, I am currently attending a discipline related to the database, and in this case, Oracle. I learned so far simple things like creating tables, inserting values ??and mainly the use and abuse of SELECT, hehe. So I need to do an exercise that is leaving me with many doubts in the matter of time. To make well chewed, here has an image with the tables that I need to create:

[IMG http://img.photobucket.com/albums/v475/MR7/tabelas.jpg[/img]
Based on it, I set it up here:

Select all

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));
Now, to insert the time values, in the Music table, I used this format:

Select all

to_date('05:22','mi:ss')
Everything ok, but now the questions:

h) Show the total time of songs registered.
j) Show the total time of songs per CD.
L) Show the average duration of the songs registered.

And that's what I need to do, I have to manipulate this data I entered to answer these questions, but it's very complicated, I tried everything I'vê learned so far and I could not solve it. Thanks anticipated for help, sorry for the huge amount of text. Thanks ... \ o
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Fri, 30 Mar 2007 7:26 pm
Location: Londrina - PR
Rafael O. Genaro

Come on:

As your field time is Date type, it is worth remembering that the date also [/ u] will be stored at the base (if omitted, The first day of the current date is inserted). That is, the command

Select all

 
to_date('05:22','mi:ss') 
will enter, today, the value '01 / 05/2008 00:05:22 'at the base. But if it turns in a month, you will insert '01 / 06/2008 00:05:22 ', and so on.

If you need to use the Date type field, it would be interesting to specify a day, month and year equal to all records, just so that they are consistent.

In any case, to get the duration of a song, you will:

Select all

 
select tempo - trunc(tempo) from Musicas; 
The truncation command in a Date field will return to Date at 00:00. In this way, you will get this difference in days .

To convert these days into minutes again, you can do:

Select all

 
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 
); 
The above consultation will truncate the decimal part From the duration of a certain song, informing how many days of duration it has.
Then we took any date (at midnight - this is important) to which we add the duration of music, which finally convert back to char, picking up only the hours, minutes and seconds, referring to at the time of duaration of this song.

H, J and L items are only variations of this logic by simply adding the ranges to then convert back to the output format.

I hope I have helped a little.
MR7
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 06 May 2008 6:52 pm
Location: Caxias do Sul, RS

Thanks Rafael, it already helped a lot. I would like to know only one more detail, at the beginning of your post you stressed that my time was date so so that was the method to solve. Do you think it would have another way to implement time? Because he does not necessarily need to be date type, I put it like that because I actually tried to do number and did not leave as expected. Thank you ... O /
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

The good to use date is that it is possible to subtract two dates. (Thus returning the QTD of days between the two) ...

Example:
Date1 - Date2, let's say Return 0.5 -> This means that it is 12 hours between both...
rodfbar
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 241
Joined: Tue, 09 Oct 2007 11:15 am
Location: Batatais - SP

I had to do a job where I used dates ..

In fact it was a new module for electronic point control ...

After pending a time With these conversions, I ended up deciding to use the fields of time and minute as a number, and the entire part of my field represents the hours and decimal values ??the minutes ...

I had no problems. .. I just had to have controls for minutes <60 and hours <24 (hours <24 because it did not count hours, but it stored the time of the day when it was generated movement of the point)
Well é an option ...

Abrçs
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Fri, 30 Mar 2007 7:26 pm
Location: Londrina - PR
Rafael O. Genaro

MR7, in fact there is nothing wrong with its implementation, even using another type for the field, 98% of the way to perform these calculations would be the same.

In fact it's more a matter of taste, since I find it kind of strange to consult a duration field on a table and find any date with the hours (since Oracle always inserts some date In this field type, even if you inform only the hours in your insert).

Personally, I usually create a number field, which stores the duration in seconds / minutes / hours / days, depending on the precision required.

With this information, simply transform the stored number on days (multiplying by 60 * 60 * 24, if the field saves the time in seconds, for example), and then using this value to perform Conversion, + - thus:



The only practical advantage in storing ranges in this form is that there is no limit for the interval that can be inserted ( Except for field size, clear), while if the field is type of type, you need to set a "standard" date, that all applications that update and consult this table must use, so that it is possible to recover ranges above 1 Day (which, of course, will not be necessary in your case).
MR7
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Tue, 06 May 2008 6:52 pm
Location: Caxias do Sul, RS

Thanks for all answers, hugs ...
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 15 guests