Questions about handling 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
  

Poston Tue, 06 May 2008 7:05 pm

Good night guys. Following, I'm studying for the first time in my life a discipline related to database, and in this case, Oracle. I'vê learned so far, simple things like create tables, insert values and especially the use and abuse of the select, hehe. So, I need to do an exercise that is leaving me with many questions in question time. To leave well chewed, here's an image with the tables that need to create: [img]http%20:%20.%20.%20photobucket//img%20com/albums/v475/MR7/jpg%20.%20tables[/img] based on it, rode it here:
Code: 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 values of the time, in the Songs, used this format:
Code: Select all
to_date('05:22','mi:ss')
so far so ok, but now the issues: h) Show the total time of songs registered.
j) Show the total time of songs per CD.
l) Show the average duration of songs registered.

And that's what I need to do, I have to handle this data to insert for answering these questions, it's pretty complicated, tried everything I'vê learned so far and haven't been able to solve. Thank you in advance for your help, sorry for the huge amount of text. Thanks ... o
MR7
Location: Caxias do Sul, RS

Poston Tue, 06 May 2008 7:47 pm

Here we go: As their field time is of type date, remember that the data will be stored at the base (if omitted, will be inserted into the first day of the month of the current date). IE, the command
Code: Select all
to_date('05:22','mi:ss')
will insert, today, the value ' 5/1/2008 00:05:22 ' on the base. But if shot in a month, will insert ' 6/1/2008 00:05:22 ', and so on.

If necessary use the field of type date, it would be interesting to specify a day, month and year the same for all records, just to stay consistent.

Anyway, for the duration of a song, you will:
Code: Select all
select tempo - trunc(tempo) from Musicas;
trunc command in a date field will return the date to 00:00. In this way, you will get this difference in days .

To convert these days in minutes again, you can do:
Code: 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 query will truncate the decimal portion of the duration of a particular song, stating how many days long she has.
Then we get a date any (midnight-this is important) to which we add the duration time of the music, which finally convert back to char, catching only the hours, minutes and seconds, for the duaração time this song.

The items h, j and l are just variations of this logic, simply add the ranges to then convert again for the output format.

I hope I helped a little.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Wed, 07 May 2008 1:56 am

Thanks Rafael, has already helped a lot. I would like to know just one more detail, at the beginning of your post you said that my time was date so why was that method to resolve. You think I'd get another way to implement the time? Because it does not have to necessarily be of type date, put it like that because I actually tried to do number and didn't go as expected. Thanks ... o/
MR7
Location: Caxias do Sul, RS

Poston Wed, 07 May 2008 9:49 am

The good thing about using DATE is that it is possible to SUBTRARIR two dates. (returning the number of days between the two).

Example: DATA1 DATA2-Let's say return 0.5-> This means that are 12:0 between the two.
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Wed, 07 May 2008 11:22 am

I had to do a job where used dates

was actually a new module for controlling electronic point ... after languishing some time with these converts ended up deciding to use the hour and minute fields as number, the integer part of my field represents the hours and the minutes decimal values ... didn't have problems ... just had q have controls to minutes and hours < 60 < 24 (hours not counted 24 hours < racesbut at the time of day that was generated) point movement is an option ... abrçs
rodfbar
Location: Batatais - SP

Poston Wed, 07 May 2008 7:05 pm

MR7, actually there is nothing wrong in their implementation, even using another type for the field, 98% of the way to perform these calculations would be equal.

Actually, it's more a matter of taste, since I find kind of strange to see a duration field in a table and find a date any time (since the oracle always inserts some data in this type of field, even if you notify 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 required accuracy.

With this information, simply turn the number stored in days (multiplying by 60 * 60 * 24, if the field save time in seconds, for example), and then using this value to perform the conversion, +-like this:
Code: Select all
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
);
the only practical advantage in store ranges in this way is that there is no limit to the range that can be inserted (except for the size of the fieldof course), while if the field is of type date, you will need to set a \"default\" date, all applications that update and consult this table should use, so that you can retrieve ranges greater than 1 day (which, of course, it will not be necessary in your case).
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Thu, 08 May 2008 12:19 am

Thanks guys, thanks for all the answers, hugs ...
MR7
Location: Caxias do Sul, RS


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 7 guests