Douglas Brantz wrote:
>
> I am trying to calculate the time difference to keep track of monitors
> time in a computer lab using
> Mysql version 3.22.15 on a Redhat 5.2 Linux OS. I searched and found
> this statement and I believe that
> it could work for me.
> >----------------------------------- Statement Below
> -----------------------------------------------
> Actually you should have get an error for the insert, but MySQL is
> nice and stores it anyway :)
>
> The 'TIME' type is stored in a 3 byte int and only acts like a nice
> formated integer. If you want subctract times, you must convert them
> to seconds. You can do this with:
>
> TIME_TO_SEC(timeout)-TIME_TO_SEC(timein).
>
> If you want this back in hours,minutes and seconds you should do:
>
> SEC_TO_TIME(TIME_TO_SEC(timeout)-TIME_TO_SEC(timein))
>
> Yours,
> Monty
>
> PS: It looks like the current release doesn't have TIME_TO_SEC(). I
> have now added this to MySQL 3.21.16-beta
> ----------------------------------------------------------- Statement
> Ends ---------------
>
> I have 2 timestamp fields that I use for intime and out time. I know
> that the 2nd timestamp field does not get updated automatically. I also
> have a tdin field for intime seconds and and tdout for out time seconds
> and then a field for totaltime where intime is subtracted from outtime.
> This is probably over kill with all the fields. What would the exact
> statement be for getting the seconds to hours and seconds. Do I need to
> add a line like this
> $time =SEC_TO_TIME(tdout-tdin) ?? How would this work? Do I need
> this on the command .=
> I guess I am asking how to invoke this SEC_TO_TIME in my Perl CGI script
> with the mysql module. I will try it on the command line in mysql to
> see what I get back.
>
> Maybe I could make a query line in the perl script like
> print Query->SEC_TO_TIME(tdout-tdin) or something like that.
>
> Thanks for any help in advance.
>
> Chow,
>
> --
>
> Douglas Brantz
Hi Douglas
Use:
SELECT
SEC_TO_TIME( totaltime )
FROM
computer_lab
OR:
SELECT
SEC_TO_TIME( tdout - tdin )
FROM
computer_lab
OR:
SELECT
SEC_TO_TIME( UNIX_TIMESTAMP( out_timestamp ) - UNIX_TIMESTAMP( in_timestamp) )
FROM
computer_lab
Tschau
Christian
PS: Sorry for the late answer, I was on vacation
