Re: Hour counts
Eric Bergen wrote: This does make his code fall under the limitations of unix timestamps. In 30 years or so when we are all retired millionaires ;) some poor intern is going to have to figure out why the hour diff calculation is failing. Long before then we will all be using 64 bit processors and a 64 bit signed integer for the unix timestamps. That will move the problem out about 292 billion years :) -- Chris W Gift Giving Made Easy Get the gifts you want give the gifts they want http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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)/1)+ (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one day/ R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't understand/ start end R1R2 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 1 1 -75 Please help me...(exist a function for this situation?) Tnx!
Re: Hour counts
Hi, Check out the TIMEDIFF function. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com 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)/1)+ (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one day/ R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't understand/ start end R1R2 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 1 1 -75 Please help me...(exist a function for this situation?) Tnx! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Hour counts
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 MMDD 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:[EMAIL PROTECTED]
Sent: 27 July 2005 17:12
To: mysql@lists.mysql.com
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)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 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 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
mysql select *,timediff(end,start) from foo; +-+-++-+ | start | end | id | timediff(end,start) | +-+-++-+ | 2005-07-14 15:00:00 | 2005-07-14 17:30:00 | 1 | 02:30:00| | 2005-07-14 23:00:00 | 2005-07-15 01:30:00 | 2 | 02:30:00| | 2005-07-14 15:00:00 | 2005-07-15 02:30:00 | 3 | 11:30:00| | 2005-07-14 15:00:00 | 2005-07-14 16:00:00 | 4 | 01:00:00| +-+-++-+ On Wed, 27 Jul 2005, [iso-8859-2] Gyurasits Zolt?n wrote: 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)/1)+ (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one day/ R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't understand/ start end R1R2 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 1 1 -75 Please help me...(exist a function for this situation?) Tnx! -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
Hi,
You can use Timediff :
mysql SELECT TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30');
+--+
| TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30') |
+--+
| -01:30:00|
+--+
1 row in set (0.00 sec)
mysql
mysql
mysql
mysql
mysql SELECT TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00');
+--+
| TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00') |
+--+
| 01:30:00 |
+--+
1 row in set (0.00 sec)
more in http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
Mathias
Selon Gyurasits Zoltán [EMAIL PROTECTED]:
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)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 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 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
Hi,
You can use Timediff :
mysql SELECT TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30');
+--+
| TIMEDIFF('2005-07-27 18:00', '2005-07-27 19:30') |
+--+
| -01:30:00|
+--+
1 row in set (0.00 sec)
mysql
mysql
mysql
mysql
mysql SELECT TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00');
+--+
| TIMEDIFF('2005-07-27 19:30', '2005-07-27 18:00') |
+--+
| 01:30:00 |
+--+
1 row in set (0.00 sec)
more in http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
Mathias
Selon Gyurasits Zoltán [EMAIL PROTECTED]:
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)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 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 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
TIMESTAMP() is available as of MySQL 4.1.1. I can't use this version because replication working :( I use version 4.0.22 - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: Gyurasits Zoltán [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, July 27, 2005 6:31 PM Subject: Re: Hour counts Hi, Check out the TIMEDIFF function. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com 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)/1)+ (((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is in one day/ R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't understand/ start end R1R2 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 1 1 -75 Please help me...(exist a function for this situation?) Tnx! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
Gyurasits Zoltán [EMAIL PROTECTED] wrote on 07/27/2005 12:57:42 PM:
TIMESTAMP() is available as of MySQL 4.1.1.
I can't use this version because replication working :(
I use version 4.0.22
- Original Message -
From: Martijn Tonies [EMAIL PROTECTED]
To: Gyurasits Zoltán [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, July 27, 2005 6:31 PM
Subject: Re: Hour counts
Hi,
Check out the TIMEDIFF function.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS
SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
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)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is
in
one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 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 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Then your next option is to convert your datetime values to seconds since
epoch by using UNIX_TIMESTAMP() (see the same link everyone else has
already given you for details).
mysqlSELECT (unix_timestamp('2005-07-27 19:30:00') -
unix_timestamp('2005-07-27 18:00:00'))/3600;
+--+
| (unix_timestamp('2005-07-27 19:30:00') - unix_timestamp('2005-07-27
18:00:00'))/3600 |
+--+
|1.50 |
+--+
1 row in set (0.00 sec)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Hour counts
Working!!! Thank you!!!
- Original Message -
From: [EMAIL PROTECTED]
To: Gyurasits Zoltán [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Wednesday, July 27, 2005 7:09 PM
Subject: Re: Hour counts
Gyurasits Zoltán [EMAIL PROTECTED] wrote on 07/27/2005 12:57:42 PM:
TIMESTAMP() is available as of MySQL 4.1.1.
I can't use this version because replication working :(
I use version 4.0.22
- Original Message -
From: Martijn Tonies [EMAIL PROTECTED]
To: Gyurasits Zoltán [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, July 27, 2005 6:31 PM
Subject: Re: Hour counts
Hi,
Check out the TIMEDIFF function.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS
SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
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)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is
in
one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 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 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Then your next option is to convert your datetime values to seconds since
epoch by using UNIX_TIMESTAMP() (see the same link everyone else has
already given you for details).
mysqlSELECT (unix_timestamp('2005-07-27 19:30:00') -
unix_timestamp('2005-07-27 18:00:00'))/3600;
+--+
| (unix_timestamp('2005-07-27 19:30:00') - unix_timestamp('2005-07-27
18:00:00'))/3600 |
+--+
|1.50 |
+--+
1 row in set (0.00 sec)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hour counts
This does make his code fall under the limitations of unix timestamps.
In 30 years or so when we are all retired millionaires ;) some poor
intern is going to have to figure out why the hour diff calculation is
failing.
[EMAIL PROTECTED] wrote:
Gyurasits Zoltán [EMAIL PROTECTED] wrote on 07/27/2005 12:57:42 PM:
TIMESTAMP() is available as of MySQL 4.1.1.
I can't use this version because replication working :(
I use version 4.0.22
- Original Message -
From: Martijn Tonies [EMAIL PROTECTED]
To: Gyurasits Zoltán [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Wednesday, July 27, 2005 6:31 PM
Subject: Re: Hour counts
Hi,
Check out the TIMEDIFF function.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS
SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
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)/1)+
(((end-start)/1000-(ROUND((end-start)/1)*10))/6),1) /good if is
in
one
day/
R3 : ROUND((end-start)/1)-76 /-76 because from 14. to 15. I don't
understand/
start end R1R2 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 1 1 -75
Please help me...(exist a function for this situation?)
Tnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Then your next option is to convert your datetime values to seconds since
epoch by using UNIX_TIMESTAMP() (see the same link everyone else has
already given you for details).
mysqlSELECT (unix_timestamp('2005-07-27 19:30:00') -
unix_timestamp('2005-07-27 18:00:00'))/3600;
+--+
| (unix_timestamp('2005-07-27 19:30:00') - unix_timestamp('2005-07-27
18:00:00'))/3600 |
+--+
|1.50 |
+--+
1 row in set (0.00 sec)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
