Problems with indexes on Date/DateTime fields
Hi all We are having a weird problem with some queries which are not using some indexes in date fields. Query-1 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] Query-2 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] We have an index for dateField in tableX. However Query-1 is using the index but Query-2 is not. We have tested a lot of different combinations and every time we use any of these operators =, , , = Mysql stop using the index. We are using Mysql 4.1.12 binary distribution running on linux. Any help will be really appreciated. Thanks Javier ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with indexes on Date/DateTime fields
Hi Aftab Thanks for your prompt answer. Yes, second query is doing full scan. I don't understand why a change in the operator can make the parser think a full scan will be better than use the index. Nevertheless we know the best option is to use the index, unfortunately we can not use FORCE INDEX as what we really need to do are DELETE operations, and it seems FORCE INDEX is valid only for SELECT statements. Basically we have a huge database where we track different operations and every day we need to do some maintenance and delete records which are older than specific date. Thx Javier -Original Message- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:03 To: Javier Diaz Subject: Re: Problems with indexes on Date/DateTime fields Is not the second quary doing a full table scan? The parser may find this better than using the Index. --- Javier Diaz [EMAIL PROTECTED] wrote: Hi all We are having a weird problem with some queries which are not using some indexes in date fields. Query-1 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] Query-2 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] We have an index for dateField in tableX. However Query-1 is using the index but Query-2 is not. We have tested a lot of different combinations and every time we use any of these operators =, , , = Mysql stop using the index. We are using Mysql 4.1.12 binary distribution running on linux. Any help will be really appreciated. Thanks Javier ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problems with indexes on Date/DateTime fields
Thanks Aftab We already think of that option as possible solution but I was just wondering why is Mysql changing the logic when the operator changes. I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the case we will need to re-visit a few queries Thx Javier -Original Message- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:25 To: Javier Diaz Subject: RE: Problems with indexes on Date/DateTime fields I agree. Some time full table scan is faster than using the index and going row by row. Any way, try using a subquery and get the result on to a temp table or just del usnig it . Some thing like this: Del from table where Key field in (Sel Key from tabl1 where stored datesome date); --- Javier Diaz [EMAIL PROTECTED] wrote: Hi Aftab Thanks for your prompt answer. Yes, second query is doing full scan. I don't understand why a change in the operator can make the parser think a full scan will be better than use the index. Nevertheless we know the best option is to use the index, unfortunately we can not use FORCE INDEX as what we really need to do are DELETE operations, and it seems FORCE INDEX is valid only for SELECT statements. Basically we have a huge database where we track different operations and every day we need to do some maintenance and delete records which are older than specific date. Thx Javier -Original Message- From: Aftab Khan [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 11:03 To: Javier Diaz Subject: Re: Problems with indexes on Date/DateTime fields Is not the second quary doing a full table scan? The parser may find this better than using the Index. --- Javier Diaz [EMAIL PROTECTED] wrote: Hi all We are having a weird problem with some queries which are not using some indexes in date fields. Query-1 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] Query-2 SELECT [field list] FROM tableX WHERE dateField = [any date expression or constant value] We have an index for dateField in tableX. However Query-1 is using the index but Query-2 is not. We have tested a lot of different combinations and every time we use any of these operators =, , , = Mysql stop using the index. We are using Mysql 4.1.12 binary distribution running on linux. Any help will be really appreciated. Thanks Javier ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with indexes on Date/DateTime fields
Javier Diaz wrote: I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the case we will need to re-visit a few queries If you do a select instead of a delete, will the index be used? (You can check this by using EXPLAIN SELECT) If the index is used in that case MySQL must have a reason for not using the index for deleting a range. With MyISAM tables deleting a single date involves a single leave in the index tree, deleting multiple dates requires MySQL to merge index leaves during the delete. You could use DELETE QUICK to suppress the merging of index blocks, but you need to do an OPTIMIZE later on to reclaim the unused index space. The query optimizer might decide that using the index in this case is slower than a full table scan. If the index is not used with the select it might be because the index makes the optimizer think that more than approx. 30% of the records will be involved. In such a case it is usually faster to directly access the data than to use an index (which would require access to the index plus index to the data). It will be more likely for you to get relevant answers from this list if you supply the definition of the table(s), indexes, engine, etc. (a CREATE TABLE statement is very good for this purpose); the exact query which you use (and nog give a SELECT and later on introduce the fact that it was actually a DELETE query ;-) ); in case of a select the output from EXPLAIN SELECT... With this information the gurus here (and that does not include me :-) ) can more easily judge the situation and tell you what might be the cause of your problem. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
RE: Problems with indexes on Date/DateTime fields
Hi Jigal Thanks a lot for your answer. Sorry for the confusion about DELETE and SELECT. What we are trying to optimize are some DELETE statements, it was just that while investigating we found this behaviour of Mysql not using some date indexes if we change from using = operator to use = or = Please find below all the details. Mysql version: 4.1.12 Operating System: Linux Table Type: InnoDB Number of records in table: More than 10 million process_times CREATE TABLE `process_times` ( `ID` bigint(20) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', `time` time NOT NULL default '00:00:00', `subagent` char(3) NOT NULL default '', `client_id` varchar(128) NOT NULL default '', `status` int(11) NOT NULL default '0', `process_time` double NOT NULL default '0', `host` varchar(20) NOT NULL default '', `process` int(11) NOT NULL default '0', PRIMARY KEY (`ID`), KEY `client_idx` (`client_id`), KEY `status_idx` (`status`), KEY `subag_idx` (`subagent`), KEY `mias_idx2` (`host`), KEY `date_idx` (`date`), KEY `process` (`process`), KEY `date_proc_idx` (`date`,`process`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DELETE queries we want to optimize: DELETE FROM process_times WHERE (date = date_sub(now(), INTERVAL VariableX VariableY)) AND (process=VariableZ) While investigating we tried different SELECT statement to check is Mysql was using the Index: date_proc_idx See below what we found 1- Select using = operator. As you will see in the explain response not index is being used EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) Explain results: data row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeALL/type possible_keysdate_idx,date_proc_idx/possible_keys key(NULL)/key key_len(NULL)/key_len ref(NULL)/ref rows10778561/rows ExtraUsing where/Extra /row /data 2- Select using = operator. date_proc_idx index is used EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) Explain results: data row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeref/type possible_keysdate_idx,date_proc_idx/possible_keys keydate_idx/key key_len3/key_len refconst/ref rows1863456/rows ExtraUsing where/Extra /row /data 3- Adding condition for process field. EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 Explain results: - data - row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeref/type possible_keysdate_idx,process,date_proc_idx/possible_keys keydate_proc_idx/key key_len7/key_len refconst,const/ref rows550726/rows ExtraUsing where/Extra /row /data 4 - Again with condition for process field and changing operator from = to =. This cause Mysql to use a diffent index, in this case the index for process EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 Explain results: - data - row id1/id select_typeSIMPLE/select_type tableprocess_times/table typeref/type possible_keysdate_idx,process,date_proc_idx/possible_keys keyprocess/key key_len4/key_len refconst/ref rows1830334/rows ExtraUsing where/Extra /row /data After seeing this we are now worry about the fact maybe many queries we have based on date fields are not using the indexes in the way we were expecting. I would like also ask if is valid to expect that the results of the Explain statement for a query like this SELECT * FROM TABLE-X WHERE [CONDITIONS] Are valid for the equivalent DELETE FROM TABLE-X WHERE [CONDITIONS] Thanks a lot for your help Javier -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: 05 January 2006 14:39 To: Javier Diaz Cc: Aftab Khan; mysql@lists.mysql.com Subject: Re: Problems with indexes on Date/DateTime fields Javier Diaz wrote: I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the case we will need to re-visit a few queries If you do a select instead of a delete, will the index be used? (You can check this by using EXPLAIN SELECT) If the index is used in that case MySQL must have a reason for not using the index for deleting a range. With MyISAM tables deleting a single date
Re: Problems with indexes on Date/DateTime fields
Javier Diaz wrote: EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) possible_keysdate_idx,date_proc_idx/possible_keys key(NULL)/key rows10778561/rows EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) possible_keysdate_idx,date_proc_idx/possible_keys keydate_idx/key rows1863456/rows MySQL always tries to find the index that will result in the smallest number of rows to evaluate for the final result. These queries show that you have ca. 11 million rows in the table of which ca. 2 million rows are two days old. My guess is that at most 4 million rows are less than two days old, so at least 7 million rows are 2 days old or older. 7 million is far more than 30% of 11 million, so the select for = 2 days old will use a full table, which is likely to be faster in this case. In case of InnoDB the primary key is stored with the data (clustered indexes), so if date_idx were the primary index it would probably have used it, but first getting 65% of a secundary index and then looking it up in the primary index does not make sense. EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 possible_keysdate_idx,process,date_proc_idx/possible_keys keydate_proc_idx/key rows550726/rows The half a million or so records for this process that are exactly two days old can be retrieved efficiently by using the combined index date_proc_idx. EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 possible_keysdate_idx,process,date_proc_idx/possible_keys keyprocess/key rows1830334/rows The combined index cannot be used because you cannot make a range condition for this case: (-00-00 00:00:00, 1) (date, process) (2005-01-02 00:00:00, 1) This would include for example (2005-01-02 00:00:00 , 0) which does not meet the condition set in the query. [See: http://dev.mysql.com/doc/refman/5.0/en/range-access-multi-part.html ] As we've already seen earlier the date_idx would result in far more than 30% of the rows, so only the process index can be used (ca. 17% of the rows). I would like also ask if is valid to expect that the results of the Explain statement for a query like this SELECT * FROM TABLE-X WHERE [CONDITIONS] Are valid for the equivalent DELETE FROM TABLE-X WHERE [CONDITIONS] Not necessarily. In many cases it will be about the same, but I suspect that technical conditions such as updating indexes, etc. might influence the execution path that MySQL choses. You might investigate other methods such as moving the remaining records to a new table, dropping the old table and renaming the new table. This is just a quick idea, I haven't looked up how well InnoDB performs these operations and how it influences the other users of your data. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
# Seconds between two datetime fields
We have a table that has two datetime fields (start_time and end_time). We'd like to be able to calculate the number of seconds between these two fields in a query. However, a simple end_time - start_time does not yield a correct result. SELECT start_time, end_time, end_time - start_time FROM mailings_sendstats order by start_time desc; +-+-+---+ | start_time | end_time| end_time - start_time | +-+-+---+ | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 |53 | | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 | | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 |55 | | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 |53 | | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 | | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 | | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 | | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 | | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 | | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 | | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 |58 | | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 | etc As you can see, if the time difference is less than 1 minute, a correct result is returned. If the difference is 1 minute and 30 seconds, '130' is returned instead of 90. I tried playing with the end_time - start_time conversion using different calculations. What I came up with is below. mysql SELECT start_time, end_time, - end_time - start_time as cur, - FLOOR((end_time - start_time) / 100) as num, - (end_time - start_time) % 100 as mod, - (FLOOR((end_time - start_time) / 100) * 60) + (end_time - start_time) % 100 AS seconds - FROM mailings_sendstats - ORDER BY id desc; +-+-+-+--+--+-+ | start_time | end_time| cur | num | mod | seconds | +-+-+-+--+--+-+ | 2005-03-02 19:10:00 | 2005-03-02 19:11:07 | 107 |1 |7 | 67 | | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 | 53 |0 | 53 | 53 | | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 |1 | 31 | 91 | | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 | 55 |0 | 55 | 55 | | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 | 53 |0 | 53 | 53 | | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 |1 | 10 | 70 | | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 |1 | 80 | 140 | | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 |1 | 10 | 70 | | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 |1 | 20 | 80 | | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 |1 | 22 | 82 | | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 |1 | 26 | 86 | | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 | 58 |0 | 58 | 58 | | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 |1 | 28 | 88 | etc First, I calculated the number of minutes (num), then seconds (mod), then total seconds (seconds). If you scroll through the results, you'll see most of them are accurate. However, when the beginning time is just before a new minute, the entire calculation is thrown off. Examples: | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 |1 | 80 | 140 | | 2005-03-02 18:37:59 | 2005-03-02 18:38:53 | 94 |0 | 94 | 94 | So my question is, how can I fix this? I know there are several date/time functions that I could probably use, but they were not introduced until version 4.1 and I am stuck with version 3.23. We also modified the table to use timestamp(14) fields instead of datetime fields, but the same result occurs. Ultimately: a) Can someone think of a way to modify the query above so that it produces *correct* results every time (maybe I'm missing something simple after working on this for so long) b) We're using this table to track execution time of PHP Cron scripts. We may be approaching this entirely the wrong way. If someone has other ideas, please let me know. Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: # Seconds between two datetime fields
The return you are getting is correct for the format you are using. A 90 second difference is in fact 1 minute, 30 seconds(130). To get the time difference in seconds convert the datetime or timestamp to a julian date or unixtime and then process. SELECT start_time, end_time, UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) FROM mailings_sendstats order by start_time desc; I hope this helps... Pat... CocoNet Corporation SW Florida's 1st ISP - Original Message - From: Brian Erickson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 03, 2005 7:23 PM Subject: # Seconds between two datetime fields We have a table that has two datetime fields (start_time and end_time). We'd like to be able to calculate the number of seconds between these two fields in a query. However, a simple end_time - start_time does not yield a correct result. SELECT start_time, end_time, end_time - start_time FROM mailings_sendstats order by start_time desc; +-+-+---+ | start_time | end_time| end_time - start_time | +-+-+---+ | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 |53 | | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 | | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 |55 | | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 |53 | | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 | | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 | | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 | | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 | | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 | | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 | | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 |58 | | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 | etc As you can see, if the time difference is less than 1 minute, a correct result is returned. If the difference is 1 minute and 30 seconds, '130' is returned instead of 90. I tried playing with the end_time - start_time conversion using different calculations. What I came up with is below. mysql SELECT start_time, end_time, - end_time - start_time as cur, - FLOOR((end_time - start_time) / 100) as num, - (end_time - start_time) % 100 as mod, - (FLOOR((end_time - start_time) / 100) * 60) + (end_time - start_time) % 100 AS seconds - FROM mailings_sendstats - ORDER BY id desc; +-+-+-+--+--+-+ | start_time | end_time| cur | num | mod | seconds | +-+-+-+--+--+-+ | 2005-03-02 19:10:00 | 2005-03-02 19:11:07 | 107 |1 |7 | 67 | | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 | 53 |0 | 53 | 53 | | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 |1 | 31 | 91 | | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 | 55 |0 | 55 | 55 | | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 | 53 |0 | 53 | 53 | | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 |1 | 10 | 70 | | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 |1 | 80 | 140 | | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 |1 | 10 | 70 | | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 |1 | 20 | 80 | | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 |1 | 22 | 82 | | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 |1 | 26 | 86 | | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 | 58 |0 | 58 | 58 | | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 |1 | 28 | 88 | etc First, I calculated the number of minutes (num), then seconds (mod), then total seconds (seconds). If you scroll through the results, you'll see most of them are accurate. However, when the beginning time is just before a new minute, the entire calculation is thrown off. Examples: | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 |1 | 80 | 140 | | 2005-03-02 18:37:59 | 2005-03-02 18:38:53 | 94 |0 | 94 | 94 | So my question is, how can I fix this? I know there are several date/time functions that I could probably use, but they were not introduced until version 4.1 and I am stuck with version 3.23. We also modified the table to use timestamp(14) fields instead of datetime fields, but the same result occurs. Ultimately: a) Can someone think of a way to modify the query above so that it produces *correct* results every time (maybe I'm missing something simple after working on this for so long) b) We're using this table to track execution time of PHP Cron scripts. We may be approaching this entirely the wrong way. If someone
Re: # Seconds between two datetime fields
Brian Erickson wrote: We have a table that has two datetime fields (start_time and end_time). We'd like to be able to calculate the number of seconds between these two fields in a query. However, a simple end_time - start_time does not yield a correct result. SELECT start_time, end_time, end_time - start_time FROM mailings_sendstats order by start_time desc; [...] So my question is, how can I fix this? I know there are several date/time functions that I could probably use, but they were not introduced until version 4.1 and I am stuck with version 3.23. There are lots of date/time functions available in version 3.23, for example unix_timestamp(): SELECT start_time, end_time, unix_timestamp(end_time) - unix_timestamp(start_time) AS seconds FROM mailings_sendstats order by start_time desc; -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subtraction on datetime fields
hi i want to build a query in which i have 2 datetime fields which i subtract from one another. what is the result set? is it in (milli)seconds, is it a unix timestamp? what type of convertion do i have to apply to report the difference in minutes? tks tom _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - 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: subtraction on datetime fields
On Wed, 2002-12-11 at 04:44, Tom Roos wrote:
hi
i want to build a query in which i have 2 datetime fields which i subtract
from one another. what is the result set? is it in (milli)seconds, is it a
unix timestamp? what type of convertion do i have to apply to report the
difference in minutes?
I usually convert to unix time (seconds since 1970) then subtract. Then
use sec_to_time to get hours:mins:sec, or convert to a displayable value
in the client software.
select unix_timestamp('2002-12-02 15:30:00') -
unix_timestamp('2002-11-15 21:11:08') as tdiff;
+-+
| tdiff |
+-+
| 1448332 |
+-+
select sec_to_time(unix_timestamp('2002-12-02 15:30:00') -
unix_timestamp('2002-11-15 21:11:08')) as tdiff;
+---+
| tdiff |
+---+
| 402:18:52 |
+---+
Or to get minutes, rather than using sec_to_time, divide by 60.
You can't just subtract the datetime values from each other because
they'll be converted to integers (that is, '2002-12-02 15:30:00' becomes
20021202153000) and then subtraction doesn't work as each place in the
resultant integer doesn't fully hold all digits 0 through 9.
Fortunately, date comparsion (using and ) still work because the date
is arranged with the most signifcant portion being further left.
Andy.
-
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: subtraction on datetime fields
On Wednesday 11 December 2002 12:44, Tom Roos wrote: i want to build a query in which i have 2 datetime fields which i subtract from one another. what is the result set? is it in (milli)seconds, is it a unix timestamp? what type of convertion do i have to apply to report the difference in minutes? You can't apply arithmetics on datetime fields directly. You can do it like UNIX_TIMESTAMP(column1)-UNIX_TIMESTAMP(column2) and you get difference in seconds (UNIX_TIMESTAMP(column1)-UNIX_TIMESTAMP(column2))/60 - in minutes -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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: DateTime Fields
clarify exactly what you mean. show an example.. you question is confusing. cw --- shahana qureshi [EMAIL PROTECTED] wrote: Please tell me how can I get the date and time values diffently in a single query from 'DateTime' field and the difference of two DateTime fields in HH:MM:SS format. Thanks shahana __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - 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 __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - 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
DateTime Fields
Please tell me how can I get the date and time values diffently in a single query from 'DateTime' field and the difference of two DateTime fields in HH:MM:SS format. Thanks shahana __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com - 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: newby question about datetime fields
Hi ppl! I have a table with datetime type column in it. when I make request select * from my_table order by my_datetime I get the normal sort i.e. first the oldest dates but I want to get reverse sort to have most recent dates to be the first. How can I do this? http://www.bitbybit.dk/mysqlfaq/faq.html#ch7_20_0 / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - 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: newby question about datetime fields
select * from my_table order by my_datetime DESC -Original Message- From: brainheap [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 01, 2001 5:16 AM To: MySQL mailing list Subject: newby question about datetime fields Hi ppl! I have a table with datetime type column in it. when I make request select * from my_table order by my_datetime I get the normal sort i.e. first the oldest dates but I want to get reverse sort to have most recent dates to be the first. How can I do this? Thanks for any help :0) Alex - 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 - 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
MYSQL bug with if and datetime fields
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MYSQL bug with if and datetime fields Description: In queries where datetime field is in "WHERE" condition, the same field in "IF" statement causes incorrect query result. How-To-Repeat: #--- - mysql select * from test ; +--+-+ | id | dt | +--+-+ |1 | 2001-08-14 00:00:00 | |2 | 2001-08-15 00:00:00 | |3 | 2001-08-16 00:00:00 | +--+-+ 3 rows in set (0.00 sec) mysql select * from test where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); Empty set (0.95 sec) mysql select * from test where dt='2001-08-14' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); Empty set (0.01 sec) #--- Fix: #--- mysql select * from test where dt like '2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); +--+-+ | id | dt | +--+-+ |1 | 2001-08-14 00:00:00 | +--+-+ 1 row in set (0.76 sec) #--- - Submitter-Id: submitter ID Originator:Arturs Aboltins Organization: (Private) MySQL support: [none] Synopsis: Buf with DATETIME fields and IF. Severity: [ serious ] Priority: [ medium ] Category: mysql Class: [ sw-bug ] Release: mysql-3.23.36 (Binary distribution from RedHat 7.1) Environment: i686, Linux, System: Linux aaboltin.telekom.lv 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc - 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: MYSQL bug with if and datetime fields
Arturs Aboltins writes: From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MYSQL bug with if and datetime fields Description: In queries where datetime field is in WHERE condition, the same field in IF statement causes incorrect query result. How-To-Repeat: #--- - mysql select * from test ; +--+-+ | id | dt | +--+-+ |1 | 2001-08-14 00:00:00 | |2 | 2001-08-15 00:00:00 | |3 | 2001-08-16 00:00:00 | +--+-+ 3 rows in set (0.00 sec) mysql select * from test where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); Empty set (0.95 sec) Please try the above with 3.23.41. I think that it was fixed meanwhile. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - 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: MYSQL bug with if and datetime fields
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa Arturs Aboltins writes: From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MYSQL bug with if and datetime fields Description: In queries where datetime field is in WHERE condition, the same field in IF statement causes incorrect query result. How-To-Repeat: mysql select * from test ; +--+-+ | id | dt | +--+-+ |1 | 2001-08-14 00:00:00 | |2 | 2001-08-15 00:00:00 | |3 | 2001-08-16 00:00:00 | +--+-+ 3 rows in set (0.00 sec) mysql select * from test where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); Empty set (0.95 sec) Sinisa Please try the above with 3.23.41. I think that it was fixed Sinisa meanwhile. cut The reason the above is failing is that the WHERE is optimized to: t1.dt = 2001081400 and 2001081400 = if((t1.id = 1),'2001-08-14 00:00:00','1999-08-15') Because 'if' returns a string in this case, the later query doesn't match :( I will try to disable this with optimization for datetime in the future. In the mean time, you can fid this by either doing: select * from test where dt=2001081400 and dt=if(id=1,2001081400,1999081500); or: select * from test where dt='2001-08-14 00:00:00' and concat(dt) = if(id=1,'2001-08-14 00:00:00','1999-08-15'); The extra concat() will disable this optimization. Regards, Monty - 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: Difference of two datetime fields.
You can look up DATE_SUB in the manual for starters and combine them with other functions in the same section, to get what you want. - Original Message - From: "Laercio Brehmer" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 17, 2001 4:06 Subject: Difference of two datetime fields. Hello, How could I obtain the difference of two datetime fields giving the result in seconds, like the function datediff in MSsql ? I tried date_sub but I had no success. Ex: 2001-02-16 15:00:00 - 2001-02-16 14:00:00=3600 seconds Thanks, Laercio Brehmer CIASC [EMAIL PROTECTED] - 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 - 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
