List:General Discussion« Previous MessageNext Message »
From:Terry Spencer Date:July 27 2005 4:37pm
Subject:RE: Hour counts
View as plain text  
There are a few options, for more information see
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html


"TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2) 

Returns the integer difference between the date or datetime expressions
datetime_expr1 and datetime_expr2. The unit for the result is given by the
interval argument. The legal values for interval are the same as those
listed in the description of the TIMESTAMPADD() function. 

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
        -> -1

TIMESTAMPDIFF() is available as of MySQL 5.0.0. "

It appears you require the answer in fraction hours. Set the interval to
seconds and divide the result by 3600 (60*60 = seconds in an hour)

---

" UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date) 

If called with no argument, returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is
called with a date argument, it returns the value of the argument as seconds
since '1970-01-01 00:00:00' GMT. date may be a DATE string, a DATETIME
string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD in local
time. 

mysql> SELECT UNIX_TIMESTAMP();
        -> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580"

Convert both dates to seconds using UNIX_TIMESTAMP() and subtract one form
the other. Divide the result by 3600 (60*60 = seconds in an hour) to obtain
the fractional hours.

Terry


-----Original Message-----
From: Gyurasits Zoltán [mailto:gyz@stripped] 
Sent: 27 July 2005 17:12
To: mysql@stripped
Subject: Hour counts

Hello All!


I would like to calculate the hour counts from 2 'datetime'.
Example:   2005-07-27 18:00 and 2005-07-27 19:30  => 1,5 hour

I try this....  but not good!

R1 : munkaido_end-munkaido_start  /simple substract/
R2 : ROUND(ROUND((end-start)/10000)+
(((end-start)/1000-(ROUND((end-start)/10000)*10))/6),1)  /good if is in one
day/
R3 : ROUND((end-start)/10000)-76  /-76 because from 14. to 15. I don't
understand/


start               end                  R1                R2          R3
07-14 15:00     07-14 17:30     23000           2.5         -74
07-14 23:00     07-15 01:30     783000         78.5         2
07-14 15:00     07-15 02:30     873000         87.5         11
07-14 15:00     07-14 16:00     10000           1             -75

Please help me...    (exist a function for this situation?)


Tnx!
Thread
Hour countsGyurasits Zoltán27 Jul
  • Re: Hour countsMartijn Tonies27 Jul
  • Re: Hour countsJason Pyeron27 Jul
  • Re: Hour countsmfatene27 Jul
    • Re: Hour countsmfatene27 Jul
  • Re: Hour countsGyurasits Zoltán27 Jul
    • Re: Hour countsSGreen27 Jul
      • Re: Hour countsGyurasits Zoltán27 Jul
      • Re: Hour countsEric Bergen27 Jul
        • Re: Hour counts2wsxdr528 Jul
RE: Hour countsTerry Spencer27 Jul