nlafferty has asked for the wisdom of the Perl Monks concerning the following question:

I need to know how to take the data stored in a SQL database for "start_hour, start_minute, end_hour, end_minute" to find the total amount between the two. Pass that amount to a variable so i can write that to the database.

Replies are listed 'Best First'.
Re: Totaling Time
by lhoward (Vicar) on Jul 25, 2001 at 22:11 UTC
    You'd probably be better off if you had stored your times using your database's native date/time format (instead of munging with integer col's). But in short: 
      my $tdiff = ($hour1 - $hour2) * 60 + ($min1 - $min2);
  my $diff_hour = int(tdiff / 60);
  my $diff_min = $tdiff % 60;

    [download]
[reply]
[d/l]

      I have some comments to add: specifically, about using the database date/time format/type.

      If you are going to do any sort of transformation/computation of date/time within perl, I recommend storing the data as an integer ( seconds since epoch time ) in order to reduce overhead.

      You may argue that you can do this transformation in the SQL statements, but it's still a huge performance hit ( as far as I could tell ) when you are dealing with tens and hudreds of thousands of record which each needs to have the epoch time format in order to do some calculations

      In my case, I needed to fit a certain API that expected epoch time to do its processing. I tried it with a) store date/time in db format, covert upon fetch b) sotre data/time in db format, convert from within SELECT statement c) store data/time as seconds since epoch time, no conversion

      This yielded in roughly a) and b) taking the same amount of time, where as c) took about half of the time that it took for a) and b).

      So, depending on your application, I strongly urge you to use epoch time instead of native date/time format

[reply]
      Of course that assumes that the start and stop occured on the same day. If nlafferty's data crosses day boundaries (e.g. it started at 10:55PM and ended at 2:30AM) then the answer becomes much more complicated.
[reply]
        Complications aside, without dates it is impossible to know if more than one day has elapsed between the start and end times.
[reply]
Re: Totaling Time
by lestrrat (Deacon) on Jul 25, 2001 at 22:01 UTC

    No offense, but this seems way to vague. We can't tell what the table scheme is, what data you may or may not already have in your script etc. Even if that wasn't a problem, I can't tell if you want the SQL statement that does something or you need a perl snippet

    Please write exactly what you need. And details, please.... details...

[reply]
      In the TIME TOTAL CALCULATION SUBROUTINE i need to take the SQL fields (start_hour, start_minute, end_hour, end_minute) from a row consistion of (oid,emp_id,date,f_name,l_name,start_hour, start_minute,start_stamp, end_hour, end_minute, end_stamp, comment). Then update the 'total_time' field WHERE oid = $sav_oid Field with that total. Oh yes. 24 hour format would be good. 
      
#!/usr/bin/perl
### clock_out.cgi

use CGI qw/:standard/;
require "common.sub";


$oid = $ENV{QUERY_STRING};
$sav_oid = $oid;

$end_hour = &filter(param(hour));
$end_minute = &filter(param(minute));
$comment = &filter(param(comment));

print header;

&Create_DB_Connection;
&clock_out;
&print_output;

# Disconnect from the database
$sth->finish;
$dbh->disconnect;

################  BEGIN CLOCKOUT SUBROUTINE

sub clock_out{

  $SQL = "UPDATE timeclock SET 
         end_hour = '$hour', 
         end_minute   = '$minute',  
         end_stamp    = CURRENT_TIMESTAMP, 
                  
        WHERE
         oid      = '$sav_oid'
       ";  
  &Do_SQL;
} # End of clock_out

################  END CLOCKOUT SUBROUTINE

################  BEGIN TIME TOTAL CALCULATION SUBROUTINE

sub calc_total{

  $SQL = "select oid,* FROM timeclock WHERE oid = $sav_oid";
           
  &Do_SQL;



################  END WRITE DATA SUBROUTINE




################  BEGIN PRINT OUTPUT SUBROUTINE
sub print_output{
  print<<HTML;
   <BODY BGCOLOR="#F1EDD3">   
   <CENTER><FONT SIZE=5 FACE=ARIAL>
    Record added to database
     <P>
    <CENTER>
</CENTER>
     </P>
   </FONT></CENTER>
  </BODY></HTML>
HTML

} # End of subroutine
################  END PRINT OUTPUT SUBROUTINE

      [download]
[reply]
[d/l]

        So if I read this correctly, you want to figure out the time that elapsed between start_hour, start_min, end_hour, and end_min, and then store it back in your db

        I think I can safely say that it's impossible to detemine ( correctly ) the amount of time that has elapsed by just knowing the hour and minutes, as RheTbull says.

        You would need to have some sort of date/time entries for start time and end time, then I think Postgres provides you with ways to calculate an interval type.

[reply]
Re: Totaling Time
by clemburg (Curate) on Jul 26, 2001 at 14:10 UTC

    Chapter 4 in Joe Celko's SQL for Smarties deals with "Temporal Datatypes in SQL".

    Christian Lemburg
    Brainbench MVP for Perl
    http://www.brainbench.com

[reply]
Re: Totaling Time
by nlafferty (Scribe) on Jul 25, 2001 at 23:27 UTC
    Ok. I think my problem is that i need to convert the time to a decimal. That should solve all of my problems
[reply]
      You really should consider a timestamp or epoch time. I don't know what you mean by "convert the time to decimal", but as others have pointed out, if whatever you're measuring could conceivably cross midnight, your calculations will be wrong.
      Please, either tell us :
      1. that this will never happen, ever.
      2. that the decimal format you refer to is a timestamp.
[reply]
Re: Totaling Time
by wardk (Deacon) on Jul 26, 2001 at 02:11 UTC

    if you have an oracle database, and are using date columns, then you can use PL/SQL to convert and do math on the dates (see "Datatype Conversion" section of the Fundamentals chapter of PL/SQL users guide).

    then again if you aren't using oracle, this post is certainly not much help...

[reply]
Re: Totaling Time
by rrwo (Friar) on Jul 27, 2001 at 06:17 UTC
[reply]

Back to Seekers of Perl Wisdom