comparing two tables
If I have two similar tables, with identical columns how would I select rows that are not duplicated between both tables? Any easy way to do this? Thanks, -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with a TIME calculation - Can I do this?
I have a table that has the following structure: mysql describe time_sheet; +--+---+--+-+ ++ | Field| Type | Null | Key | Default| Extra | +--+---+--+-+ ++ | tdate| date | | | 2000-01-01 || | callslip | text | | | || | customer | text | | | || | time_in | time | | | 00:00:00 || | time_out | time | | | 00:00:00 || | item_sold| text | YES | | NULL || | amount | decimal(10,2) | YES | | NULL || | citem_sold | text | YES | | NULL || | camount | decimal(10,2) | YES | | NULL || | amount_collected | decimal(10,2) | YES | | NULL || | commision| decimal(10,2) | YES | | NULL || | tsid | int(11) | | PRI | NULL | auto_increment | | dispatch_time| time | | | 00:00:00 || | ctype| text | YES | | NULL || +--+---+--+-+ ++ 14 rows in set (0.00 sec) I am trying to use the following select statement: select ctype, count(tsid), sum(amount), sum(amount_collected), sum (camount), sum(commision), subtime(time_out,dispatch_time) as time from time_sheet where tdate='2005-06-22' group by ctype; It gives me the following results: +---+-+-+--- +--++--+ | ctype | count(tsid) | sum(amount) | sum(amount_collected) | sum (camount) | sum(commision) | time | +---+-+-+--- +--++--+ | CMP | 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:15:00 | | INS | 2 |0.00 | 0.00 | 0.00 | 0.00 | 03:00:00 | | PMNR | 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:30:00 | | SC| 1 |0.00 | 0.00 | 0.00 | 0.00 | 01:45:00 | +---+-+-+--- +--++--+ 4 rows in set (0.05 sec) The Time column at the end should not just be for 1 entry - it needs to show the time_out minus the dispatched time for the group. In other words if in the INS calltype I have 2 calls that: Dispatch TimeTime_out 12:00 14:00 14:00 15:00 My total Time Column should read 3:00 What I would like to use is: select ctype, count(tsid), sum(amount), sum(amount_collected), sum (camount), sum(commision), sum(subtime(time_out,dispatch_time)) as time from time_sheet where tdate='2005-06-22' group by ctype; NOTE: This adds a SUM() to the time column. Is this do-able - (doesn't work this way - maybe in another manner? Thanks. -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with a TIME calculation - Can I do this?
Thanks! Worked perfectly. And also thanks to the other people who responded. Being unfamiliar with how MySQL seems to handle time your responses really helped! -Chris On Jun 27, 2005, at 1:16 PM, [EMAIL PROTECTED] wrote: That would have worked if TIME values weren't converted to numbers by packing their components together. (This is one of my very few peeves with MySQL. Packing works well for sorting but not at all for date math). To get good date math working, you need to use the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions to convert your time differences into and out of actual numeric values (ones that are SUM ()-able). http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html You could also use time_to_sec() and sec_to_time() to do the same down-conversion/restoration. (Any pair of functions that convert the a time into some kind of numeric value and back will work but these come to mind first.) Try this: SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , FROM_UNIXTIME(sum(UNIX_TIMESTAMP(subtime (time_out,dispatch_time as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; -- or -- SELECT ctype , count(tsid) , sum(amount) , sum(amount_collected) , sum(camount) , sum(commision) , SEC_TO_TIME(sum(TIME_TO_SEC(subtime (time_out,dispatch_time as time FROM time_sheet WHERE tdate='2005-06-22' GROUP BY ctype; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Images to MySql Database in PHP or JAVA
Hello list- I am programing both in Java and PHP. Can anyone point me to sample code for either on saving an image to a table? Thanks, -Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subtracting 2 times
Hello, I need to ask for some help and I can't find anything in the manuals. I am writing a php based app that as one of it's functions keeps an employee time sheet. The times are stored in a mysql database with 2 columns - time_in and time_out. What I need to do is subtract these to columns to show a job_time (which should be in hh:mm form). Can anyone give me any suggestions? Or am I better off doing this on the PHP side rather than the mysql side? Thanks, -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Subtracting 2 times
Thanks for the quick response. The times are stored in TIME format. If I do the following: SELECT *, time_out-time_in as job_time from time_sheet; the job_time column gives me the right time, but is not formatted, for example: time_in = 11:00 time_out = 13:15 job_time= 21500 Any pointers? -Chris On Saturday, August 10, 2002, at 09:36 AM, DL Neil wrote: Hello Christopher, I need to ask for some help and I can't find anything in the manuals. I am writing a php based app that as one of it's functions keeps an employee time sheet. The times are stored in a mysql database with 2 columns - time_in and time_out. What I need to do is subtract these to columns to show a job_time (which should be in hh:mm form). Can anyone give me any suggestions? Or am I better off doing this on the PHP side rather than the mysql side? My general rule is that when pulling data out of an RDBMS it is best to do as much as possible there, than to transfer data into (say) PHP and post-process it. Are the times stored as DATETIME/TIME, timestamp, or other format data? The answer will thus vary! Assuming we're not talking about timestamps (trivial exercise) then SEC_TO_TIME() and its counterpart may be of interest. Manual ref: http://www.mysql.com/doc/en/Date_and_time_functions.html Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
