FW: if statement help
Just forwarding this to the list. On Wednesday, March 30, 2005 10:43, Christopher Vaughan wrote: > Tom Crimmins on Wednesday, March 30, 2005 at 11:31 AM -0500 wrote: >> Look at the functions HOUR(time), MINUTE(time), SECOND(time). These >> will give you interger output for each part of the time field. Also >> TIME_TO_SEC(time) may be useful for you. I believe that all of these >> are supported in 3.23. If your times aren't greater than 838:59:59 >> this should work for you. > > Thanks for the help: > > SELECT sum( HOUR ( job_walltime ) ) Hours, sum( > MINUTE ( job_walltime ) ) Minutes, sum( > SECOND ( job_walltime ) ) Seconds > FROM `Jobs` > > This cold medicine that I'm on has slowed me down a bit. > > -Chris Vaughan > > www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if statement help
"Christopher Vaughan" <[EMAIL PROTECTED]> wrote on 30/03/2005 16:48:47: > I have data in a table listed as > 44:22:22 > 333:33:33 > It stands for hhh:mm:ss > I want to break each part of the data into different parts based on > the ':' to separate them. Then I want to take that data and sum it. > I wrote an if statement to parse through this table but I can't get > it to work. I am not sure If my syntax is > wrong because I can't find anything to check against it. > > Here is the syntax: > > IF > (SELECT job_walltime > FROM time > WHERE CHAR_LENGTH( job_walltime ) >=9) > THEN > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds > FROM `time`) > ELSE > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds > FROM `time`) > END > > I know this isn't the only way to do this but this but this is the > first suggestion that comes to mind. Any input would be great. IF is an operator, not a command, so it comes after the SELECT. In C terms, it is more like the "?:" operator than an "if()...else". Thus you can do SELECT x, IF (x > y, "IS BIGGER THAN", "IS SMALLER THAN"), y FROM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if statement help
I think I remember a match_at(":") or pat_index(":") UDF which would
return the position of the first ":" for you, but I can't find it if it
does exist.
On Wed, 30 Mar 2005, Christopher Vaughan wrote:
>I have data in a table listed as
>44:22:22
>333:33:33
>It stands for hhh:mm:ss
>I want to break each part of the data into different parts based on the ':' to
>separate them. Then I want to take that data and sum it. I wrote an if
>statement to parse through this table but I can't get it to work. I am not
>sure If my syntax is
>wrong because I can't find anything to check against it.
>
>Here is the syntax:
>
>IF
>(SELECT job_walltime
>FROM time
>WHERE CHAR_LENGTH( job_walltime ) >=9)
>THEN
>(SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` ,
>4, 2 )) , sum( right( job_walltime, '2' ) ) seconds
>FROM `time`)
>ELSE
>(SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` ,
>3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds
>FROM `time`)
>END
>
>I know this isn't the only way to do this but this but this is the first
>suggestion that comes to mind. Any input would be great.
>
>Further info MySQL 3.23.58 running on RedHat 9
>
>-Chris Vaughan
>
>www.clusters.umaine.edu
>
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 10:24, Christopher Vaughan wrote: > Tom Crimmins on Wednesday, March 30, 2005 at 11:10 AM -0500 wrote: >> If you convert it to a time field you can use mysql built-in >> functions to do what you want. You are limited to the range >> -838:59:59 to 838:59:59 though. >> >> http://dev.mysql.com/doc/mysql/en/time.html >> http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html > > I'm not sure that this is going to work. Since the length of the > data ranges from 8-9 characters in length and I also need to sum all > the times on hour, minute and second. I looked at the addtime > function but for the version of MySQL we have installed it does not > work and upgrading it would be an unwanted hassle. > > > -Chris Vaughan > > www.clusters.umaine.edu Look at the functions HOUR(time), MINUTE(time), SECOND(time). These will give you interger output for each part of the time field. Also TIME_TO_SEC(time) may be useful for you. I believe that all of these are supported in 3.23. If your times aren't greater than 838:59:59 this should work for you. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 09:49, Christopher Vaughan wrote: > I have data in a table listed as > 44:22:22 > 333:33:33 > It stands for hhh:mm:ss If you convert it to a time field you can use mysql built-in functions to do what you want. You are limited to the range -838:59:59 to 838:59:59 though. http://dev.mysql.com/doc/mysql/en/time.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html > I want to break each part of the data into different parts based on > the ':' to separate them. Then I want to take that data and sum it. > I wrote an if statement to parse through this table but I can't get > it to work. I am not sure If my syntax is wrong because I can't find > anything to check against it. > > Here is the syntax: > > IF > (SELECT job_walltime > FROM time > WHERE CHAR_LENGTH( job_walltime ) >=9) > THEN > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) > seconds > FROM `time`) > ELSE > (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( > `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) > seconds > FROM `time`) > END > > I know this isn't the only way to do this but this but this is the > first suggestion that comes to mind. Any input would be great. > > Further info MySQL 3.23.58 running on RedHat 9 > > -Chris Vaughan -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if statement help
I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) >=9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. Further info MySQL 3.23.58 running on RedHat 9 -Chris Vaughan www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
