List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 17 1999 4:25pm
Subject:Re: Converting Seconds to Hours and Minutes in Mysql
View as plain text  
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

Thread
Converting Seconds to Hours and Minutes in MysqlDouglas Brantz13 Jul
  • Re: Converting Seconds to Hours and Minutes in MysqlChristian Mack17 Jul