Is there any determined date for mysql 6 release?
Hi Thank you for reading my post Is there any scheduled date mysql 6 release? I heard that it is based on falcon and can perform better... Thanks. -- View this message in context: http://www.nabble.com/Is-there-any-determined-date-for-mysql-6-release--tp14968233p14968233.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL SELECT Statement with Date help request
Hi List, I am wondering if someone can help me with a query to check what databases are on the MySQL server and then check which of those databases are either partially or completely within the date range i require. The scenario is as follows: db1 : 2007-01-01 to 2007-02-01 db2 : 2007-02-01 to 2007-03-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me that the databases that will have this information will be db2,db3,db4 and db5. My problem is that i have multiple servers running at different locations that uses the same app that writes to the MySQL db. However the amount of databases on each server differs in amount of db's and date ranges for each server. Is there a way of getting such a result with MySQL? Thanks in advance, Craig
Re: MySQL SELECT Statement with Date help request
Sounds like you should create a MERGE table that links all the underlying tables together. Then you just query the merge table and MySQL handles which tables it needs to pull data from. You also then don't need to query for the tables. On Jan 9, 2008, at 9:12 AM, Cx Cx wrote: Hi List, I am wondering if someone can help me with a query to check what databases are on the MySQL server and then check which of those databases are either partially or completely within the date range i require. The scenario is as follows: db1 : 2007-01-01 to 2007-02-01 db2 : 2007-02-01 to 2007-03-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me that the databases that will have this information will be db2,db3,db4 and db5. My problem is that i have multiple servers running at different locations that uses the same app that writes to the MySQL db. However the amount of databases on each server differs in amount of db's and date ranges for each server. Is there a way of getting such a result with MySQL? Thanks in advance, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL5.1 estimated release date?
Hi, A silly question. Is there anybody knowing estimated GA release date for MySQL 5.1? I heard it was planed to be released in december this year and the latest release 5.1.22 is RC, but seems that it will not happen soon. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird behavior with date(null) and if
Baron Schwartz schrieb: On Dec 13, 2007 4:18 PM, Daniel Mikic [EMAIL PROTECTED] wrote: Baron Schwartz wrote: Hi, On Dec 13, 2007 3:53 PM, Daniel Mikic [EMAIL PROTECTED] wrote: Hi, i hit a weird behavior: select date(null); #result is null select if(date(null) is null, 1, 2); #result is 2 (not null) select if(date(null) is not null, 1, 2); #result is 1 I use mysql version 5.0.32-Debian_7etch1-log. Is this a bug and if not, can anyone explain why? Seems to be a bug, but I can't reproduce on 5.0.45-Debian_1ubuntu3-log, where it works correctly. What do you get from this? mysql select date(null) is null; ++ | date(null) is null | ++ | 1 | ++ 1 row in set (0.00 sec) Hi, here is the result: db2:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1909348 Server version: 5.0.32-Debian_7etch1-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select date(null) is null; ++ | date(null) is null | ++ | 0 | ++ 1 row in set (0.00 sec) Definitely a bug. I'd see if it's been reported at http://bugs.mysql.com/ and if not, report it anyway. If it has been fixed accidentally there may not be a test case to prevent it from happening in the future. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Hi, this bug was fixed with version 5.0.36. http://bugs.mysql.com/bug.php?id=23938 Thanks Daniel -- Mit freundlichen Grüßen Daniel Mikic IT-Leiter / CIO logical line GmbH Hamburger Allee 23 30161 Hannover HRB 56320/AG Hannover Geschäftsführer: Dr. Rüdiger Lemke www.logicalline.com Fon: +49 511 936 208 - 32 Fax: +49 511 936 208 - 11
Weird behavior with date(null) and if
Hi, i hit a weird behavior: select date(null); #result is null select if(date(null) is null, 1, 2); #result is 2 (not null) select if(date(null) is not null, 1, 2); #result is 1 I use mysql version 5.0.32-Debian_7etch1-log. Is this a bug and if not, can anyone explain why? Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird behavior with date(null) and if
Hi, On Dec 13, 2007 3:53 PM, Daniel Mikic [EMAIL PROTECTED] wrote: Hi, i hit a weird behavior: select date(null); #result is null select if(date(null) is null, 1, 2); #result is 2 (not null) select if(date(null) is not null, 1, 2); #result is 1 I use mysql version 5.0.32-Debian_7etch1-log. Is this a bug and if not, can anyone explain why? Seems to be a bug, but I can't reproduce on 5.0.45-Debian_1ubuntu3-log, where it works correctly. What do you get from this? mysql select date(null) is null; ++ | date(null) is null | ++ | 1 | ++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird behavior with date(null) and if
Baron Schwartz wrote: Hi, On Dec 13, 2007 3:53 PM, Daniel Mikic [EMAIL PROTECTED] wrote: Hi, i hit a weird behavior: select date(null); #result is null select if(date(null) is null, 1, 2); #result is 2 (not null) select if(date(null) is not null, 1, 2); #result is 1 I use mysql version 5.0.32-Debian_7etch1-log. Is this a bug and if not, can anyone explain why? Seems to be a bug, but I can't reproduce on 5.0.45-Debian_1ubuntu3-log, where it works correctly. What do you get from this? mysql select date(null) is null; ++ | date(null) is null | ++ | 1 | ++ 1 row in set (0.00 sec) Hi, here is the result: db2:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1909348 Server version: 5.0.32-Debian_7etch1-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select date(null) is null; ++ | date(null) is null | ++ | 0 | ++ 1 row in set (0.00 sec) Thanks Daniel
Re: Weird behavior with date(null) and if
On Dec 13, 2007 4:18 PM, Daniel Mikic [EMAIL PROTECTED] wrote: Baron Schwartz wrote: Hi, On Dec 13, 2007 3:53 PM, Daniel Mikic [EMAIL PROTECTED] wrote: Hi, i hit a weird behavior: select date(null); #result is null select if(date(null) is null, 1, 2); #result is 2 (not null) select if(date(null) is not null, 1, 2); #result is 1 I use mysql version 5.0.32-Debian_7etch1-log. Is this a bug and if not, can anyone explain why? Seems to be a bug, but I can't reproduce on 5.0.45-Debian_1ubuntu3-log, where it works correctly. What do you get from this? mysql select date(null) is null; ++ | date(null) is null | ++ | 1 | ++ 1 row in set (0.00 sec) Hi, here is the result: db2:~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1909348 Server version: 5.0.32-Debian_7etch1-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select date(null) is null; ++ | date(null) is null | ++ | 0 | ++ 1 row in set (0.00 sec) Definitely a bug. I'd see if it's been reported at http://bugs.mysql.com/ and if not, report it anyway. If it has been fixed accidentally there may not be a test case to prevent it from happening in the future. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to emulate a DATE() in MySQL 4.0?
Hello mysql, I have a PHP script that I have written and it runs beautifully on current MySQL and PHP sites BUT, it barfs on MySQL 4.0 because I am using the DATE() to extract the Date from a timestamp. Does anyone remember how to do an equivalent function in 4.0? I have searched through hundreds of pages and can not find anything that looks like it might work. Essentially I need to extract the -MM-DD from a timestamp field that I populated in a PHP script so I can do a diff against a current timestamp. Here is the query that works on current and falls over dead on 4.0: SELECT count(SimulatedTimeSet) as count_live FROM `Simulator` WHERE DATE( `SimulatedTimeSet` ) = DATE(CURRENT_TIMESTAMP) I appreciate in advance any clue to get me in the right directions other than upgrading the system. I don't have any control over that one... TIA -- Best regards, mikesz mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to emulate a DATE() in MySQL 4.0?
[EMAIL PROTECTED] wrote: Hello mysql, I have a PHP script that I have written and it runs beautifully on current MySQL and PHP sites BUT, it barfs on MySQL 4.0 because I am using the DATE() to extract the Date from a timestamp. Does anyone remember how to do an equivalent function in 4.0? I have searched through hundreds of pages and can not find anything that looks like it might work. Essentially I need to extract the -MM-DD from a timestamp field that I populated in a PHP script so I can do a diff against a current timestamp. Here is the query that works on current and falls over dead on 4.0: SELECT count(SimulatedTimeSet) as count_live FROM `Simulator` WHERE DATE( `SimulatedTimeSet` ) = DATE(CURRENT_TIMESTAMP) Try FROM_UNIXTIME(). Have you read the manual section on date and time functions? There are quite a few. You don't need to read hundreds of pages. http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL date/time
Hello guys, I would like to get some registers on my database that are older than 90 days, and after delete it because the table is very larger: mysql select count(*) from max_ecardsent; +--+ | count(*) | +--+ | 1172330 | +--+ 1 row in set (0.01 sec) But I'm confusing when I use the FROM_UNIXTIME, the output is very confusable, like: mysql SELECT FROM_UNIXTIME(cs_date_create),FROM_UNIXTIME(cs_date_delete) \ FROM max_ecardsent ORDER BY cs_date_delete ASC LIMIT 5; +---+---+ | FROM_UNIXTIME(cs_date_create) | FROM_UNIXTIME(cs_date_delete) | +---+---+ | 2007-03-05 20:16:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | +---+---+ mysql SELECT FROM_UNIXTIME(cs_date_create),FROM_UNIXTIME(cs_date_delete) \ FROM max_ecardsent ORDER BY cs_date_delete DESC LIMIT 5; +---+---+ | FROM_UNIXTIME(cs_date_create) | FROM_UNIXTIME(cs_date_delete) | +---+---+ | 2006-06-07 23:55:37 | 2010-01-08 02:00:22 | | 2006-06-07 23:55:37 | 2010-01-08 02:00:22 | | 2006-06-15 15:16:17 | 2009-07-01 03:00:22 | | 2006-05-25 18:26:08 | 2009-06-09 03:00:22 | | 2006-05-25 18:29:59 | 2009-06-09 03:00:22 | +---+---+ 5 rows in set (2.56 sec) Someone can help me? Thanks! mysql desc max_ecardsent; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | cs_id | varchar(20) | NO | PRI | | | | cs_ec_id | mediumint(8) | NO | MUL | 0 | | | cs_message | text | YES | | NULL| | | cs_sent| tinyint(1) | NO | | 0 | | | cs_send_month | tinyint(2) | NO | | 0 | | | cs_send_mday | tinyint(2) | NO | | 0 | | | cs_send_year | mediumint(4) | NO | | 0 | | | cs_notify | tinyint(1) | NO | | 0 | | | cs_copy| tinyint(1) | NO | | 0 | | | cs_fmail | varchar(255) | NO | | | | | cs_fname | varchar(100) | NO | | | | | cs_from_email | varchar(255) | NO | | | | | cs_from_name | varchar(100) | NO | | | | | cs_date_create | int(11) | NO | MUL | 0 | | | cs_date_create_server_time | int(11) | NO | | 0 | | | cs_date_send | int(11) | NO | | 0 | | | cs_date_delete | int(11) | NO | | 0 | | | cs_lang| varchar(50) | YES | | NULL| | | cs_user_name_id| varchar(25) | NO | | | | | cs_pkdate | int(11) | NO | | 0 | | | cs_timezone| decimal(5,2) | NO | | 0.00| | | cs_music_filename | varchar(100) | YES | | NULL| | | cs_poem| varchar(100) | YES | | NULL| | | cs_skin_name | varchar(100) | YES | | NULL| | | cs_stamp_filename | varchar(100) | YES | | NULL| | | cs_java| varchar(50) | YES | | NULL| | | cs_poem_align | varchar(10) | YES | | NULL| | | cs_sender_ip | varchar(15) | YES | MUL | NULL| | ++--+--+-+-+---+ 28 rows in set (0.01 sec) -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date query
I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? cj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Baron Baron, Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. Thanks, cj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
On Aug 14, 2007, at 8:38 AM, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over- samples-that-wrap/ Baron Baron, Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. Thanks, cj Hi Christian, I don't know if you are in control of the data, But would it be possible to add a column to the database something like Read and have the value either 1 or 0? then do something like: Select * from view ordered by date where Read=1;?That way you wouldn't have any gaps for fields and could then just do the math fairly easily I think... But I'm just starting out with MySQL so I may have made a huge mistake :) In fact... It's quite probable :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
Hi, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. I misunderstood your question. I think what you need is something like the following. Working from the inside out, 1) find the most recent date previous to the current date select max(date) from t1 as inner_t1 where inner_t1.date ? This query will not run efficiently; MySQL can't yet optimize it. A logical equivalent that will be fast, if date is indexed, will be: select date from t1 as inner_t1 where inner_t1.date ? order by date desc limit 1 That's the most recent date before any given date and will be the inmots query. Now we need to find the corresponding scale_reading: select scale_reading from t1 as mid_t1 where mid_t1.date = ( ... inmost query ... ) That's the middle query. Now you can place that in a subquery: select date, scale_reading - ( ... middle query ... ) from t1 as outer_t1 Finally, resolve the ? reference in the correlated subquery: select date, scale_reading - ( select scale_reading from t1 as mid_t1 where mid_t1.date = ( select date from t1 as inner_t1 where inner_t1.date outer_t1.date order by date desc limit 1 ) ) from t1 as outer_t1 I'm not attempting to run this, just reasoning about it, so I might be wrong or there might be a syntax error. The next issue is t1 is a view -- it might perform terribly. You might be better off doing it another way, either querying the tables directly, or using a user variable: set @most_recent_reading := null; select date, scale_reading - @most_recent_reading, @most_recent_reading := scale_reading from t1; Again untested, but hopefully you get the idea. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over-samples-that-wrap/ Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. I misunderstood your question. I think what you need is something like the following. Working from the inside out, 1) find the most recent date previous to the current date select max(date) from t1 as inner_t1 where inner_t1.date ? This query will not run efficiently; MySQL can't yet optimize it. A logical equivalent that will be fast, if date is indexed, will be: select date from t1 as inner_t1 where inner_t1.date ? order by date desc limit 1 That's the most recent date before any given date and will be the inmots query. Now we need to find the corresponding scale_reading: select scale_reading from t1 as mid_t1 where mid_t1.date = ( ... inmost query ... ) That's the middle query. Now you can place that in a subquery: select date, scale_reading - ( ... middle query ... ) from t1 as outer_t1 Finally, resolve the ? reference in the correlated subquery: select date, scale_reading - ( select scale_reading from t1 as mid_t1 where mid_t1.date = ( select date from t1 as inner_t1 where inner_t1.date outer_t1.date order by date desc limit 1 ) ) from t1 as outer_t1 I'm not attempting to run this, just reasoning about it, so I might be wrong or there might be a syntax error. The next issue is t1 is a view -- it might perform terribly. You might be better off doing it another way, either querying the tables directly, or using a user variable: set @most_recent_reading := null; select date, scale_reading - @most_recent_reading, @most_recent_reading := scale_reading from t1; Again untested, but hopefully you get the idea. Baron Barron, That is exactly what I was looking for. I can work out whatever bugs or syntax errors there may be I just couldn't get my head completely around the logic. Thank you for your time. Cj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
Hi Christian, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? cj I have read through Baron's solution and I think I can help you do it just a bit quicker. It won't all be in a single statement, although it could be written that way. I am assuming that you are going to need to so this for a range of dates @start_date to @end_date. The variables aren't important except to help us limit how much work we need to do. step 1 - Start off by capturing a list of all of the dates and scale_readings between @start_date and @end_date. The last reading will have nothing to be subtracted from as the next date may not yet exist (if @end_date is today) so this table will serve as our source data for the next steps. This means that step 2 will have the smallest possible JOIN to perform. CREATE TEMPORARY TABLE tmp_source SELECT `date`, `scale_reading` FROM sourcetableorview -- this is wherever your data is coming from, now. WHERE `date` BETWEEN @start_date and @end_date; step2 - Build a pair table of dates. We need to index the tmp_source.`date` to speed this up. This step will become geometrically slower the more dates you want to process at once. The `scale_reading` is part of the index in order to speed up step 3. ALTER TABLE tmp_source ADD KEY(`date`,`scale_reading`); CREATE TEMPORARY TABLE tmp_datepairs SELECT t_s1.`date` as date1, MIN(t_s2.`date`) as date2 FROM tmp_source t_s1 INNER JOIN tmp_source t_s2 ON t_s1.`date` t_s2.`date` GROUP BY t_s1.`date`; step 3 - Use the tables we generated from steps 1 and 2 to build your final report. I indexed both columns on tmp_datepairs in order to speed this up. ALTER TABLE tmp_datepairs ADD KEY(date1), ADD KEY(date2); SELECT td.`date1` as startdate , td.`date2` as enddate , ts1.scale_reading as startwt , ts2.scale_reading as endwt , ts2.scale_reading - ts1.scale_reading as diff FROM tmp_datepairs td INNER JOIN tmp_source ts1 on td.date1 = ts1.`date` INNER JOIN tmp_source ts2 on ts.date2 = ts2.`date` step 4 - always (!!) clean up after yourself DROP TEMPORARY TABLE IF EXISTS tmp_source, tmp_datepairs; If you have any questions about the logic, please feel free to ask. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting rows by DATE ranges
On Monday 25 June 2007 10:32, Mogens Melander wrote: Looks like you have datetime fields makeing 2007-01-01 00:00:01 2007-01-01. Or use SELECT ... WHERE CONVERT(date,DATE)2007-01-01... if 'date' is a DATETIME field. Also using BETWEEN on date-ranges might help. On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting rows by DATE ranges
Looks like you have datetime fields makeing 2007-01-01 00:00:01 2007-01-01. Also using BETWEEN on date-ranges might help. On Sun, June 24, 2007 02:13, Miguel Cardenas wrote: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting rows by DATE ranges
date(), date_format() t Miguel Cardenas írta: Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting rows by DATE ranges
Hello list I found a little problem with an application am developing, in particular creating reports by DATE ranges. Examples: select ... where date2007-01-01; returns all records where date is greater (and equal inclusive) to 2007-01-01 select ... where date=2007-01-01; returns all records where date is greater/equal to 2007-01-01 the and = have the same effect select ... where date2007-01-01 and date2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 *** DOES NOT RETURN RECORDS FROM DAY *20* select ... where date2007-01-01 and date=2007-01-20; returns all records where date is greater/equal to 2007-01-01 and less than 2007-01-20 although I'm using = *** DOES NOT RETURN RECORDS FROM DAY *20* My doubts are: 1. how can I retrieve rows with a date... NOT INCLUDING the day of the specified date, I mean apply a strict GREATHER THAN 2. how can I retrieve rows with date=... INCLUDING the day of the specified date. currently I have to do a date=date1 and date=date2+1day I need to retrieve rows in this way dateX date=X dateX date=X dateX and dateY date=X and dateY date=X and date=Y dateX and date=Y and so... didn't find a function to specify ranges of dates and the LESS THAN/EQUAL operator does not include the last day, so my reports with = are done by adding one day but don't like to use it this way since it could be confusing and generate errors on reports. Thanks for any comment, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date function question
Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date function question
# of days since the first of the month from last month datediff(now(),date(concat(period_add(date_format(now(),'%Y%m'),-1),'01'))) the # of days since the end of last month (e.g. from 5/31/07) datediff(now(),date_sub(concat(date_format(now(),'%Y-%m-'),'01'),INTERVAL 1 DAY)) PB - Andrey Dmitriev wrote: Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date function question
There is also a LAST_DAY() function that returns the last day of the month: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Peter Brawley wrote: # of days since the first of the month from last month datediff(now(),date(concat(period_add(date_format(now(),'%Y%m'),-1),'01'))) the # of days since the end of last month (e.g. from 5/31/07) datediff(now(),date_sub(concat(date_format(now(),'%Y-%m-'),'01'),INTERVAL 1 DAY)) PB - Andrey Dmitriev wrote: Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
5.1 release date
Hi all, Are there any projections as to when mysql 5.1 will be released? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a function to convert a uk date to and from mysql date
Hi,
My UK dates are this format DD/MM/ I want it reversed and then the
seperator changed so it becomes -MM-DD
I use this PHP at the moment
$available_from = implode('/', array_reverse(explode('-', $available_from)));
Ta,
R.
Re: a function to convert a uk date to and from mysql date
My UK dates are this format DD/MM/ I want
it reversed and then the seperator changed so it becomes
-MM-DD
I use this PHP at the moment
$available_from = implode('/', array_reverse(explode('-',
$available_from)));
An even better solution would be:
$UKDate = '22/05/2007'
$USDate = date( 'Y-m-d', strtotime( $UKDate ));
echo 'Before: ' . $UKDate . 'br';
echo 'After: ' . $USDate . 'br';
thnx,
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: a function to convert a uk date to and from mysql date
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 22 May 2007 15:47
To: mysql@lists.mysql.com
Subject: a function to convert a uk date to and from mysql date
Hi,
My UK dates are this format DD/MM/ I want it reversed and
then the seperator changed so it becomes -MM-DD
I use this PHP at the moment
$available_from = implode('/', array_reverse(explode('-',
$available_from)));
Ta,
R.
First question, if the PHP works, why are you asking here for an answer?
Assuming you now want to do this transformation at the database layer,
select the three date components out separately with the string functions
and then use DATE_FORMAT() to print them how you want.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function
_date-format
Edward
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a Week ( date ) query
The first part of the WHERE clause gives you the week number of a record's date, and compare it with today's week number. Note that WEEK(2008-01-01,7) will return 53, indicating that because 2008-01-01 is a Tuesday it is part of the last week of 2007. That, I think, is what you want. The second clause is supposed to make sure that we aren't finding records that are in the same week but in previous years. In other words, if we are in week 23 we don't want to find records that are from week 23 ten years ago. I think I got the arguments to DATEDIFF backwards, though. The query should read SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7) AND DATEDIFF(NOW(),specials.start_date) 7; Let me know if that works. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Joey [mailto:[EMAIL PROTECTED] Sent: Saturday, March 31, 2007 4:18 AM To: 'Jerry Schwartz' Subject: RE: Help With a Week ( date ) query Hi Jerry, This part of the query is working, but something in the AND part isn't and is failing. SELECT * FROM special WHERE WEEK(NOW(), 7) = WEEK(special.date, 7) AND DATEDIFF(specials. date,DATE(NOW()) 7; Thanks for your help! -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 10:29 AM To: 'Joey'; 'MySQL DB' Subject: RE: Help With a Week ( date ) query I'm not sure if this gets you all the way, or not. There is a WEEK() function that converts a date into its week of the year. There isn't any obvious way to turn it back into a date, but it doesn't sound like you need it for your particular application. SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7) AND DATEDIFF(specials.start_date,DATE(NOW)) 7; Since you have the start date for your special, then you will be getting it back from your query anyways so you don't need to convert back. You can make your queries more efficient if you store the week the special starts as well as its date, saving one function call in your query and allowing you to index on that week field. The second part of the WHERE clause should keep you within the right year, if I did it correctly. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Joey [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 9:46 AM To: MySQL DB Subject: Help With a Week ( date ) query Hi Guys, I'm kind of at a standstill in coming up with how to get a query I need to write. I'm trying to find a record which matches the week we are in. Example today is Thursday the 29th, it is within the week which has the 26th through the 1st, and if the day is within this week display the record that has the date 3/26/2007. Basically we are returning a special which is dated each Monday, any day within that week should show the Monday value. I appreciate your help! Joey -- 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]
Help With a Week ( date ) query
Hi Guys, I'm kind of at a standstill in coming up with how to get a query I need to write. I'm trying to find a record which matches the week we are in. Example today is Thursday the 29th, it is within the week which has the 26th through the 1st, and if the day is within this week display the record that has the date 3/26/2007. Basically we are returning a special which is dated each Monday, any day within that week should show the Monday value. I appreciate your help! Joey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a Week ( date ) query
I'm not sure if this gets you all the way, or not. There is a WEEK() function that converts a date into its week of the year. There isn't any obvious way to turn it back into a date, but it doesn't sound like you need it for your particular application. SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7) AND DATEDIFF(specials.start_date,DATE(NOW)) 7; Since you have the start date for your special, then you will be getting it back from your query anyways so you don't need to convert back. You can make your queries more efficient if you store the week the special starts as well as its date, saving one function call in your query and allowing you to index on that week field. The second part of the WHERE clause should keep you within the right year, if I did it correctly. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Joey [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 9:46 AM To: MySQL DB Subject: Help With a Week ( date ) query Hi Guys, I'm kind of at a standstill in coming up with how to get a query I need to write. I'm trying to find a record which matches the week we are in. Example today is Thursday the 29th, it is within the week which has the 26th through the 1st, and if the day is within this week display the record that has the date 3/26/2007. Basically we are returning a special which is dated each Monday, any day within that week should show the Monday value. I appreciate your help! Joey -- 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: Help With a Week ( date ) query
Jerry: We do this exactly at: http://www.WeeklyRentals.com Jim I'm not sure if this gets you all the way, or not. There is a WEEK() function that converts a date into its week of the year. There isn't any obvious way to turn it back into a date, but it doesn't sound like you need it for your particular application. SELECT * FROM specials WHERE WEEK(NOW(), 7) = WEEK(specials.start_date, 7) AND DATEDIFF(specials.start_date,DATE(NOW)) 7; Since you have the start date for your special, then you will be getting it back from your query anyways so you don't need to convert back. You can make your queries more efficient if you store the week the special starts as well as its date, saving one function call in your query and allowing you to index on that week field. The second part of the WHERE clause should keep you within the right year, if I did it correctly. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Joey [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 9:46 AM To: MySQL DB Subject: Help With a Week ( date ) query Hi Guys, I'm kind of at a standstill in coming up with how to get a query I need to write. I'm trying to find a record which matches the week we are in. Example today is Thursday the 29th, it is within the week which has the 26th through the 1st, and if the day is within this week display the record that has the date 3/26/2007. Basically we are returning a special which is dated each Monday, any day within that week should show the Monday value. I appreciate your help! Joey -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date format question
Hi All Is it possible in mysql to create a date field that stores year and month only (-MM) without having to zero out the day or use varchar type fields Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Is it possible in mysql to create a date field that stores year and month only (-MM) without having to zero out the day or use varchar type fields Best here is to just use a DATE field, then use DATE_FORMAT when you want to pull up the customized date. It will get stored as a timestamp (integer), so you really won't notice that much of a storage difference. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Thanks... My issue is not storage, it is confidentiality. I am not allowed to store the day of birth as it is considered identifying information (in medical records). I do not even have the day, I want to pass a date in format (-MM) to a date field if possible. On 1/15/07 11:37 AM, Chris White [EMAIL PROTECTED] wrote: Is it possible in mysql to create a date field that stores year and month only (-MM) without having to zero out the day or use varchar type fields Best here is to just use a DATE field, then use DATE_FORMAT when you want to pull up the customized date. It will get stored as a timestamp (integer), so you really won't notice that much of a storage difference. - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Olaf Stein wrote: Thanks... My issue is not storage, it is confidentiality. I am not allowed to store the day of birth as it is considered identifying information (in medical records). I do not even have the day, I want to pass a date in format (-MM) to a date field if possible. Pass the date in format (-MM-00). -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Assign all dates to have a day of 01 Store in a date field, use DATE_FORMAT to just extract the MM and . As you don't have the real day information it doesn't matter what day is used, so long as it present in all months. Hope this helps Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
OK, thank you. How is the speed of this index compared with an indexed date column if I do: year_number='x' and month_number='y' and day_number='z'; They should have about the same cardinality, right? Thanks, Anders Chris wrote: Anders Lundgren wrote: One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Depends on your queries. If your clause is: year_number='x' and month_number='y' and day_number='z'; then create the index as #1. If your query is in a different order (month first for example), adjust the index accordingly. Multiple key indexes go left to right, so if the index is (year_number,month_number,day_number) then queries using year_number='a' and month_number='b' will be able to use that index. But year_number='a' and day_number='b' will only be able to use it for the year_number part, not the other. -- Anders Lundgren Viba IT Handelsbolag Web: http://www.vibait.se E-mail: [EMAIL PROTECTED] Cell: +46 (0)70-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number=2000 and month_number=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on = first day of the month and the first day of the next month. That will use an index. - Original Message - From: Anders Lundgren [EMAIL PROTECTED] To: Dan Buettner [EMAIL PROTECTED] Cc: Thomas Bolioli [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Anders Lundgren Viba IT Handelsbolag Webb: http://www.vibait.se E-post: [EMAIL PROTECTED] Mobil: 070-55 99 589 -- 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: Date v. DateTime index performance
Yes, of course. Thank you! - Anders Brent Baisley wrote: Splitting out your values will cause problems where doing greater than/less than searching. If you search on year_number=2000 and month_number=6, that's not going to give you everything from 6/2000 on. It will return really only the second half of each year from 2000 on. To include 2/2002, you'll need to add an OR statement, which will slow things down. If you want to search on just year and month for a date field, just add the first day of the month. If you want an entire month, search on = first day of the month and the first day of the next month. That will use an index. - Original Message - From: Anders Lundgren [EMAIL PROTECTED] To: Dan Buettner [EMAIL PROTECTED] Cc: Thomas Bolioli [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, January 09, 2007 8:34 PM Subject: Re: Date v. DateTime index performance One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Thanks, Anders Dan Buettner wrote: Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Anders Lundgren Viba IT Handelsbolag Webb: http://www.vibait.se E-post: [EMAIL PROTECTED] Mobil: 070-55 99 589 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Anders Lundgren wrote: One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. Resulting question, what if I have three colums named year_number, month_number and day_number. How should I create the keys on these columns? I. (year_number, month_number, day_number) - or - II. (year_number) (month_number) (day_number) If I create the key as of I. above and in the Where clause I just compare year and month, can the index still be used? Depends on your queries. If your clause is: year_number='x' and month_number='y' and day_number='z'; then create the index as #1. If your query is in a different order (month first for example), adjust the index accordingly. Multiple key indexes go left to right, so if the index is (year_number,month_number,day_number) then queries using year_number='a' and month_number='b' will be able to use that index. But year_number='a' and day_number='b' will only be able to use it for the year_number part, not the other. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
find date an time of a table update
Hi, I didn't find a way to know the time and date of the last update of table, not the data but the table info itself. Is this possible? I want to use this info in order to show it in a web page. Mysql 4.1 Apache Myisam Regards Marcelo Fabiani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find date an time of a table update
Hi, mysql show table status like 'tablename'\G will report you the date and time of creation, updation,etc of the specified table. 'SHOW STATUS' enables only view. Note 1: For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its tablespace and the data file timestamp does not apply. As you have specified that you want it to display it in the webpage, then retrieve the same from the 'tables' table of information_schema database. mysql use information_schema; mysql show tables; mysql select * from TABLES where TABLE_NAME='tablename'; Note 2: Check the user privilege to access the database. If not grant it. Thanks ViSolve DB Team. - Original Message - From: Marcelo Fabiani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 22, 2006 6:21 AM Subject: find date an time of a table update Hi, I didn't find a way to know the time and date of the last update of table, not the data but the table info itself. Is this possible? I want to use this info in order to show it in a web page. Mysql 4.1 Apache Myisam Regards Marcelo Fabiani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
interesting date/time query issue
I have data that is broken into anything from 30 sec to 15 minute time series (with a DATETIME field). I need to transform all of this into 15 minute data. Does anyone know off the top of their head if there a way I could use GROUP BY to make this happen? Nothing I have tried thus far has worked but it seems as though all of the pieces are there, but there does not appear to be any way to do the comparison in a way that GROUP BY can use it. Otherwise I can write a script to select all of the data and loop over it but you can see why I want to do this in SQL. Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting a field or converting a date?
People, I am not very savy with SQL and I need help. I have a char field that contains a date and the date is in DD-MM- and I want to sort it but the sort is wrong because 01-04-2007 comes out before 10-22-2006. Is there an easy way to provide a correct sorted output list or do I need to edit my table and convert all my fields to -MM-DD before I can sort it. Any help is welcome. Thanks, Néstor :-)
Re: Converting a field or converting a date?
In the last episode (Dec 05), Nstor said: I am not very savy with SQL and I need help. I have a char field that contains a date and the date is in DD-MM- and I want to sort it but the sort is wrong because 01-04-2007 comes out before 10-22-2006. Is there an easy way to provide a correct sorted output list or do I need to edit my table and convert all my fields to -MM-DD before I can sort it. Take a look at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html and the STR_TO_DATE() function. You can use it directly in an ORDER BY clause, or better yet, create a real DATETIME column, copy your date into that, and use the new field from here on. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Date Issues
Jesse,
Try the following:
asp:TemplateColumn
HeaderTemplatebDate/b/HeaderTemplate
ItemTemplate
%# DataBinder.Eval(Container.DataItem, StartDateTime ,
{0:d}) %
/ItemTemplate
/asp:TemplateColumn
Hope this helps.
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Price, Randall [mailto:[EMAIL PROTECTED]
Sent: Friday, December 01, 2006 5:17 PM
To: Jesse; MySQL List
Subject: RE: MySQL Date Issues
Not sure this is your problem, but do you have the Allow zero datetime
option on your connect string? For example,
connectionString=Server=localhost;
User ID=some_user;
Password=some_password;
Database=some_database;
Pooling=false;
Allow Zero Datetime=true;
Thanks,
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Jesse [mailto:[EMAIL PROTECTED]
Sent: Friday, December 01, 2006 4:45 PM
To: MySQL List
Subject: MySQL Date Issues
OK, I'm about to pull my hair out with this one. I know it's simple,
but I
can't find a way to do this other than switching it to a string and
parsing
it out manually (something I should have to be forced to do).
I've got a simple MySQL Table with a DateTime field in it. I want to
display it as separate fields in a DataGrid, so I've got a column like
this
in there:
asp:TemplateColumn
HeaderTemplatebDate/b/HeaderTemplate
ItemTemplate
%# Container.DataItem(StartDateTime) %
/ItemTemplate
/asp:TemplateColumn
It displays a blank.
Also, I've got code where the user clicks an Edit link and it brings up
the
date and time part separately. When I try to run the following code:
StartDate.Text = FormatDateTime(RS(StartDateTime),2)
StartTime.Text = FormatDateTime(RS(StartDateTime),3)
I get the error, Cast from type 'MySqlDateTime' to type 'Date' is not
valid.. I change change the above code to this:
StartDate.Text = FormatDateTime(RS(StartDateTime).ToString,2)
StartTime.Text = FormatDateTime(RS(StartDateTime).ToString,3)
and it works... However, what can I do with the DataGrid column above?
Thanks,
Jesse
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Date Issues
Jesse,
There are many ways to convert the datetime to its various parts; short
date, short time, month, day, year, hour, minute, seconds, etc.
Here is ShortDate (mm/dd/):
%# Convert.ToDateTime(DataBinder.Eval(Container.DataItem,
AddedDate)).ToShortDateString() %
Here is ShortTime (hh:mm am/pm):
%# Convert.ToDateTime(DataBinder.Eval(Container.DataItem,
AddedDate)).ToShortTimeString() %
When you are entering this in the Visual Studio IDE and type the dot
(.) for the ToShortDateString() portion, you will see a list of
available formats.
Hope this helps.
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Jesse [mailto:[EMAIL PROTECTED]
Sent: Monday, December 04, 2006 10:44 AM
To: Price, Randall
Subject: Re: MySQL Date Issues
This displays it, but it displays both the date time. Is there a
format
string that will display just the date part, and then just the time
part?
Thanks,
Jesse
- Original Message -
From: Price, Randall [EMAIL PROTECTED]
To: Price, Randall [EMAIL PROTECTED]; Jesse [EMAIL PROTECTED]; MySQL
List mysql@lists.mysql.com
Sent: Monday, December 04, 2006 9:39 AM
Subject: RE: MySQL Date Issues
Jesse,
Try the following:
asp:TemplateColumn
HeaderTemplatebDate/b/HeaderTemplate
ItemTemplate
%# DataBinder.Eval(Container.DataItem, StartDateTime ,
{0:d}) %
/ItemTemplate
/asp:TemplateColumn
Hope this helps.
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Price, Randall [mailto:[EMAIL PROTECTED]
Sent: Friday, December 01, 2006 5:17 PM
To: Jesse; MySQL List
Subject: RE: MySQL Date Issues
Not sure this is your problem, but do you have the Allow zero datetime
option on your connect string? For example,
connectionString=Server=localhost;
User ID=some_user;
Password=some_password;
Database=some_database;
Pooling=false;
Allow Zero Datetime=true;
Thanks,
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Jesse [mailto:[EMAIL PROTECTED]
Sent: Friday, December 01, 2006 4:45 PM
To: MySQL List
Subject: MySQL Date Issues
OK, I'm about to pull my hair out with this one. I know it's simple,
but I
can't find a way to do this other than switching it to a string and
parsing
it out manually (something I should have to be forced to do).
I've got a simple MySQL Table with a DateTime field in it. I want to
display it as separate fields in a DataGrid, so I've got a column like
this
in there:
asp:TemplateColumn
HeaderTemplatebDate/b/HeaderTemplate
ItemTemplate
%# Container.DataItem(StartDateTime) %
/ItemTemplate
/asp:TemplateColumn
It displays a blank.
Also, I've got code where the user clicks an Edit link and it brings up
the
date and time part separately. When I try to run the following code:
StartDate.Text = FormatDateTime(RS(StartDateTime),2)
StartTime.Text = FormatDateTime(RS(StartDateTime),3)
I get the error, Cast from type 'MySqlDateTime' to type 'Date' is not
valid.. I change change the above code to this:
StartDate.Text = FormatDateTime(RS(StartDateTime).ToString,2)
StartTime.Text = FormatDateTime(RS(StartDateTime).ToString,3)
and it works... However, what can I do with the DataGrid column above?
Thanks,
Jesse
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date v. DateTime index performance
If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date v. DateTime index performance
Thomas, I do not think in this case that one is better than the other, for the most part, because both require using a value computed from the column. Computing month from a DATE field should be just as fast as computing from a DATETIME column I would think. Also splitting into DATE and TIME columns can make your SQL a bit trickier depending on your needs. That being said, one difference that might come up in extreme cases is that the size of an index on a DATE column will be smaller than on a DATETIME (fewer unique values, less cardinality) so if you have a lot of records you might be able to keep all or more of the index in memory. One potential solution might be to use an extra column that tracks month_number, and populate it with a trigger on insert or update. Index that field and then use it in your WHERE clause. One possibility anyway. HTH, Dan On 12/4/06, Thomas Bolioli [EMAIL PROTECTED] wrote: If one has a large number of records per month and normally searches for things by month, yet needs to keep things time coded, does anyone know if it make sense to use datetime or separate date and a time columns? Thanks, Tom -- 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]
MySQL Date Issues
OK, I'm about to pull my hair out with this one. I know it's simple, but I can't find a way to do this other than switching it to a string and parsing it out manually (something I should have to be forced to do). I've got a simple MySQL Table with a DateTime field in it. I want to display it as separate fields in a DataGrid, so I've got a column like this in there: asp:TemplateColumn HeaderTemplatebDate/b/HeaderTemplate ItemTemplate %# Container.DataItem(StartDateTime) % /ItemTemplate /asp:TemplateColumn It displays a blank. Also, I've got code where the user clicks an Edit link and it brings up the date and time part separately. When I try to run the following code: StartDate.Text = FormatDateTime(RS(StartDateTime),2) StartTime.Text = FormatDateTime(RS(StartDateTime),3) I get the error, Cast from type 'MySqlDateTime' to type 'Date' is not valid.. I change change the above code to this: StartDate.Text = FormatDateTime(RS(StartDateTime).ToString,2) StartTime.Text = FormatDateTime(RS(StartDateTime).ToString,3) and it works... However, what can I do with the DataGrid column above? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Date Issues
Not sure this is your problem, but do you have the Allow zero datetime option on your connect string? For example, connectionString=Server=localhost; User ID=some_user; Password=some_password; Database=some_database; Pooling=false; Allow Zero Datetime=true; Thanks, Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Friday, December 01, 2006 4:45 PM To: MySQL List Subject: MySQL Date Issues OK, I'm about to pull my hair out with this one. I know it's simple, but I can't find a way to do this other than switching it to a string and parsing it out manually (something I should have to be forced to do). I've got a simple MySQL Table with a DateTime field in it. I want to display it as separate fields in a DataGrid, so I've got a column like this in there: asp:TemplateColumn HeaderTemplatebDate/b/HeaderTemplate ItemTemplate %# Container.DataItem(StartDateTime) % /ItemTemplate /asp:TemplateColumn It displays a blank. Also, I've got code where the user clicks an Edit link and it brings up the date and time part separately. When I try to run the following code: StartDate.Text = FormatDateTime(RS(StartDateTime),2) StartTime.Text = FormatDateTime(RS(StartDateTime),3) I get the error, Cast from type 'MySqlDateTime' to type 'Date' is not valid.. I change change the above code to this: StartDate.Text = FormatDateTime(RS(StartDateTime).ToString,2) StartTime.Text = FormatDateTime(RS(StartDateTime).ToString,3) and it works... However, what can I do with the DataGrid column above? Thanks, Jesse -- 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]
optional date field and NULL
I have a table like so:
enrollments
-
ID - INT
Name - VARCHAR(45)
DateOfBirth - DateTime
The DateOfBirth field should be optional and allow NULLs if there is
nothing present.
How do I do this in my INSERT?
I've tried:
INSERT INTO enrollments (Name, DateOfBirth)
VALUES ('Joe', (SELECT IF(LENGTH({date_of_birth}) = 0, NULL,
'{date_of_birth}')))
The {date_of_birth} is a variable and is provided by the client
application. I can assure that is either a valid date format or empty
string.
Error I am getting:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ') = 0,
NULL, '')),
Is there a better way to handle optional dates that I am missing?
Constructive criticism welcome.
Thanks,
--
James Neff
Technology Specialist
Tethys Health Ventures
4 North Park Drive, Suite 203
Hunt Valley, MD 21030
office: 410.771.0692 x103
cell:443.865.7874
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optional date field and NULL
This always happens to me. I end up solving my own problem a second
after I hit the send button to the mailing list.
The answer was that I needed single quotes around my first parameter
'{date_of_birth}' like this:
INSERT INTO enrollments (Name, DateOfBirth)
VALUES ('Joe', (SELECT IF(LENGTH('{date_of_birth}') = 0, NULL,
'{date_of_birth}')))
Thanks again,
Jim
James Neff wrote:
I have a table like so:
enrollments
-
ID - INT
Name - VARCHAR(45)
DateOfBirth - DateTime
The DateOfBirth field should be optional and allow NULLs if there is
nothing present.
How do I do this in my INSERT?
I've tried:
INSERT INTO enrollments (Name, DateOfBirth)
VALUES ('Joe', (SELECT IF(LENGTH({date_of_birth}) = 0, NULL,
'{date_of_birth}')))
The {date_of_birth} is a variable and is provided by the client
application. I can assure that is either a valid date format or empty
string.
Error I am getting:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ') = 0, NULL, '')),
Is there a better way to handle optional dates that I am missing?
Constructive criticism welcome.
Thanks,
--
James Neff
Technology Specialist
Tethys Health Ventures
4 North Park Drive, Suite 203
Hunt Valley, MD 21030
office: 410.771.0692 x103
cell:443.865.7874
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max date in recordset
Hello, i've a table with a lot of field and in particular: InsertDate,Box,Prt Example: InsertDate, Box, PRT 2006-11-01, BXT, 34 2006-11-01, TTS, 33 2006-11-01, RRT, 55 2006-11-02, BXT, 22 2006-11-02, TTS, 99 2006-11-02, SAR, 75 I'd like to find all record inserted in the last day... In this example the last three records... I do not know which is the last day before Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max date in recordset
select * from table where InsertDate = date_sub(now,interval 1 day) limit 3; Not tested, but i think it will work fine. Vittorio Zuccalà [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hello, i've a table with a lot of field and in particular: InsertDate,Box,Prt Example: InsertDate, Box, PRT 2006-11-01, BXT, 34 2006-11-01, TTS, 33 2006-11-01, RRT, 55 2006-11-02, BXT, 22 2006-11-02, TTS, 99 2006-11-02, SAR, 75 I'd like to find all record inserted in the last day... In this example the last three records... I do not know which is the last day before Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max date in recordset
If you're looking for the records from the last full day contained in the data, not the past 24 hours according to the clock, then this ought to work: select * from table where InsertDate = date_sub( (select max(InsertDate from table),interval 1 day) order by InserDate desc Dan On 11/14/06, Vittorio Zuccalà [EMAIL PROTECTED] wrote: Hello, i've a table with a lot of field and in particular: InsertDate,Box,Prt Example: InsertDate, Box, PRT 2006-11-01, BXT, 34 2006-11-01, TTS, 33 2006-11-01, RRT, 55 2006-11-02, BXT, 22 2006-11-02, TTS, 99 2006-11-02, SAR, 75 I'd like to find all record inserted in the last day... In this example the last three records... I do not know which is the last day before Any suggestions? -- 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: Max date in recordset
Assuming that insertdate is a DATE column, SELECT * FROM t WHERE t.insertdate = (SELECT MAX(t.insertdate) FROM t)); would do it. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Vittorio Zuccalà [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 14, 2006 11:44 AM To: mysql@lists.mysql.com Subject: Max date in recordset Hello, i've a table with a lot of field and in particular: InsertDate,Box,Prt Example: InsertDate, Box, PRT 2006-11-01, BXT, 34 2006-11-01, TTS, 33 2006-11-01, RRT, 55 2006-11-02, BXT, 22 2006-11-02, TTS, 99 2006-11-02, SAR, 75 I'd like to find all record inserted in the last day... In this example the last three records... I do not know which is the last day before Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max date in recordset
From what I know, your solution would only work if you have exactly three records to find (there might be 1 or 100), only if the latest records fall within the last three days. I think Vittorio said he didn't know ahead of time what most recent date would be. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: João Cândido de Souza Neto [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 14, 2006 12:00 PM To: mysql@lists.mysql.com Subject: Re: Max date in recordset select * from table where InsertDate = date_sub(now,interval 1 day) limit 3; Not tested, but i think it will work fine. Vittorio Zuccalà [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hello, i've a table with a lot of field and in particular: InsertDate,Box,Prt Example: InsertDate, Box, PRT 2006-11-01, BXT, 34 2006-11-01, TTS, 33 2006-11-01, RRT, 55 2006-11-02, BXT, 22 2006-11-02, TTS, 99 2006-11-02, SAR, 75 I'd like to find all record inserted in the last day... In this example the last three records... I do not know which is the last day before Any suggestions? -- 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]
Check date interval in SELECT
Hi I have 3 fields like this: id date_in date_out 1 2006-09-05 2006-09-10 2 2006-09-15 2006-09-20 3 2006-09-25 2006-09-30 Example: if I have an time interval like '2006-09-05' and '2006-09-21', I have to search, in the same time, only the records that not included in time interval and that they are different is from date_in that from date_out. In the example above, I would have to obtain only last record (id 3). Thanks in advance -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Check date interval in SELECT
I'm not 100% sure I'm understanding your requirements, but this query: select id from datetest where date_in not between '2006-09-05' and '2006-09-21' and date_out not between '2006-09-05' and '2006-09-21'; should do exactly as you want for getting id3 if I'm understanding the requirements correctly and that's the idea that you want an event that doesn't start or end in that interval. -David Thole On Oct 29, 2006, at 4:14 AM, spacemarc wrote: Hi I have 3 fields like this: id date_in date_out 1 2006-09-05 2006-09-10 2 2006-09-15 2006-09-20 3 2006-09-25 2006-09-30 Example: if I have an time interval like '2006-09-05' and '2006-09-21', I have to search, in the same time, only the records that not included in time interval and that they are different is from date_in that from date_out. In the example above, I would have to obtain only last record (id 3). Thanks in advance -- http://www.spacemarc.it -- 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: change format of date fields during LOAD DATA INFILE?
At 21:39 -0400 10/14/06, Ferindo Middleton wrote: Is there a way to change the format of date fields MySQL is expecting when LOADing data from a file? I have no problem with the format MySQL saves the date but most spreadsheet programs I use don't make it easy to export text files with date fields in the format -MM-DD even if I formated the field that way on-screen. It would be great if you could tell MySQL on the command line to expect dates in the format Month/Day/Year or something like that and be able to interpret that and convert the date to the format it's expecting on the fly. If you're using MySQL 5.0 or higher, you can read a column into a user variable and use SET to reformat the column value before inserting it into the table. Example: LOAD DATA LOCAL INFILE 'newdata.txt' INTO TABLE t (name,@date,value) SET date = STR_TO_DATE(@date,'%m/%d/%y'); The format string depends on the format of your input data, of course. http://dev.mysql.com/doc/refman/5.0/en/load-data.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: change format of date fields during LOAD DATA INFILE?
I just tested it with Excel, as it will save the date as seen if you save the worksheet to a text file. I do this quite a bit, actually, to put spreadsheet data into MySQL. Often I use Excel macros to construct entire UPDATE or INSERT statements, and save those into a text file for MySQL to inhale. I can't speak for OpenOffice. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ferindo Middleton [mailto:[EMAIL PROTECTED] Sent: Saturday, October 14, 2006 9:40 PM To: mysql Subject: change format of date fields during LOAD DATA INFILE? Is there a way to change the format of date fields MySQL is expecting when LOADing data from a file? I have no problem with the format MySQL saves the date but most spreadsheet programs I use don't make it easy to export text files with date fields in the format -MM-DD even if I formated the field that way on-screen. It would be great if you could tell MySQL on the command line to expect dates in the format Month/Day/Year or something like that and be able to interpret that and convert the date to the format it's expecting on the fly. -- Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change format of date fields during LOAD DATA INFILE?
I was using OpenOffice... And I couldn't get it to keep the format -mm-dd I saw on screen in that format when I went to save it as a text file I was able to I suppose this should be reported to their developers as an enhancement. There's no way to get MySQL to accept dates in a different format when performing the operation on the command line though? Ferindo On 10/16/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I just tested it with Excel, as it will save the date as seen if you save the worksheet to a text file. I do this quite a bit, actually, to put spreadsheet data into MySQL. Often I use Excel macros to construct entire UPDATE or INSERT statements, and save those into a text file for MySQL to inhale. I can't speak for OpenOffice. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ferindo Middleton [mailto:[EMAIL PROTECTED] ] Sent: Saturday, October 14, 2006 9:40 PM To: mysql Subject: change format of date fields during LOAD DATA INFILE? Is there a way to change the format of date fields MySQL is expecting when LOADing data from a file? I have no problem with the format MySQL saves the date but most spreadsheet programs I use don't make it easy to export text files with date fields in the format -MM-DD even if I formated the field that way on-screen. It would be great if you could tell MySQL on the command line to expect dates in the format Month/Day/Year or something like that and be able to interpret that and convert the date to the format it's expecting on the fly. -- Ferindo
Re: change format of date fields during LOAD DATA INFILE?
At 08:39 PM 10/14/2006, Ferindo Middleton wrote: Is there a way to change the format of date fields MySQL is expecting when LOADing data from a file? I have no problem with the format MySQL saves the date but most spreadsheet programs I use don't make it easy to export text files with date fields in the format -MM-DD even if I formated the field that way on-screen. It would be great if you could tell MySQL on the command line to expect dates in the format Month/Day/Year or something like that and be able to interpret that and convert the date to the format it's expecting on the fly. -- Ferindo Ferindo, If you don't want to change the input file to the proper date format, then you'll need to read the data into a temporary table and manipulate the string date into a MySQL date '-mm-dd'. I belive MaxDb has the ability to change the date format before loading data. There used to be a page where you could submit suggestion but I was only able to come up with this one: http://www.mysql.com/company/contact/. I think MySQL AB deliberately hides the suggestions page.g There is also a comment by Remco Wendt at http://dev.mysql.com/doc/refman/5.0/en/load-data.html which shows you how to load European dates that may be of help to you. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change format of date fields during LOAD DATA INFILE?
Is there a way to change the format of date fields MySQL is expecting when LOADing data from a file? I have no problem with the format MySQL saves the date but most spreadsheet programs I use don't make it easy to export text files with date fields in the format -MM-DD even if I formated the field that way on-screen. It would be great if you could tell MySQL on the command line to expect dates in the format Month/Day/Year or something like that and be able to interpret that and convert the date to the format it's expecting on the fly. -- Ferindo
Re: Need to find last price and date product was sold
Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Section 3.6.2 of the 4.1 manual has this example using a subselect: SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); I use this basic syntax with max(date) alot. - Original Message - From: João Cândido de Souza Neto [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 28, 2006 8:39 AM Subject: Re: Need to find last price and date product was sold Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- 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: Need to find last price and date product was sold
Mike, What I need to do is find the last price_sold for each product_code. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND t1.price_sold t2.price_sold WHERE t2.product_code IS NULL ORDER BY t1.product_code; There's a bit of discussion at http://www.artfulsoftware.com/queries.php#7/ PB - mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
Neat-o. I think that's much better than the query I proposed with the subselect. However, it doesn't give you price from the last sale of the product, instead it gives you highest price the product was sold for. Also, it can give you multiple rows for each product_code if there are multiple sales at the same price. Here is a small modification to Peter's query which will give you exactly one row for each product code showing the price at the last sale of that product. (Assuming you have a synthetic key, perhaps an autoincrement field, called id) Also, an index on the product_code field will help the speed of this query a lot. (I don't understand why the subselect query is still faster - I don't think it should be.) SQL is rather fun. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND (t1.date_sold t2.date_sold OR (t1.date_sold=t2.date_sold AND t1.idt2.id) WHERE t2.product_code IS NULL ORDER BY t1.product_code; Douglas Sims [EMAIL PROTECTED] On Sep 28, 2006, at 10:12 AM, Peter Brawley wrote: Mike, What I need to do is find the last price_sold for each product_code. SELECT t1.product_code,t1.date_sold,t1.price_sold FROM trans AS t1 LEFT JOIN trans AS t2 ON t1.product_code = t2.product_code AND t1.price_sold t2.price_sold WHERE t2.product_code IS NULL ORDER BY t1.product_code; There's a bit of discussion at http://www.artfulsoftware.com/ queries.php#7/ PB - mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- 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: Need to find last price and date product was sold
At 07:39 AM 9/28/2006, you wrote: Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code Unfortunately that doesn't guarantee that the price_sold will match the row with the max(date_sold). Someone gave me the solution via email using a subselect that works well. It goes something like this: select t1a.account, maxdate, amount from (select account, max (date_xact) maxdate from transactions t1 group by account) t1a left join transactions t2 on t1a.account=t2.account and maxdate=t2.date_xact order by t1a.account; Mike mos [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- 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]
Need to find last price and date product was sold
This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
You could do something like that by using a derived table (subselect) to pick the max date for each product sale and then joining that on the products table again to pick up the amounts. Note that you must use mysql 5.x to be able to use subselects. Also this will return multiple rows for a given account if there are more than one sale on the last day each item has been sold. (There are several ways around that if it's a problem). I just ran such a query on the table I'm working on right now to test the idea... there are about 3000 rows in this table so the 0.52 second query time seems high, but there's only a primary key index. I think indexing on the transaction date (date_xact) would probably help in this example... mysql select t1a.account, maxdate, amount from (select account, max (date_xact) maxdate from transactions t1 group by account) t1a left join transactions t2 on t1a.account=t2.account and maxdate=t2.date_xact order by t1a.account; +-++---+ | account | maxdate| amount| +-++---+ | 0 | 2005-08-17 | -15.06| | 1 | 2006-07-24 | 26790.00 | | 2 | 2006-07-14 | 1500.00 | | 2 | 2006-07-14 | 2040.00 | | 2 | 2006-07-14 | 2520.00 | | 3 | 2006-07-14 | -193.98 | | 3 | 2006-07-14 | -328.98 | | 3 | 2006-07-14 | -418.21 | | 4 | 2006-07-14 | -186.00 | | 4 | 2006-07-14 | -252.96 | | 4 | 2006-07-14 | -312.48 | | 5 | 2006-07-14 | -43.50| | 5 | 2006-07-14 | -59.16| | 5 | 2006-07-14 | -73.08| | 9 | 2006-06-27 | 60.06 | | 9 | 2006-06-27 | 196.77| | 10 | 2006-03-27 | 60.04 | | 11 | 2006-04-13 | 65.00 | | 12 | 2006-06-23 | -272.03 | | 13 | 2006-02-16 | 100.00| | 14 | 2006-07-14 | 114.75| | 14 | 2006-07-14 | 156.06| | 14 | 2006-07-14 | 192.78| | 15 | 2006-07-24 | -18240.00 | | 15 | 2006-07-24 | -8550.00 | | 16 | 2006-07-11 | -800.00 | | 17 | 2004-07-07 | -51.87| | 17 | 2004-07-07 | -50.49| | 17 | 2004-07-07 | -27.31| | 18 | 2006-06-01 | 288.77| | 19 | 2006-05-11 | 175.00| | 20 | 2006-01-05 | 50.00 | | 21 | 2006-07-15 | 152.90| | 22 | 2006-07-19 | -600.00 | | 23 | 2006-05-31 | 10.00 | | 24 | 2005-07-29 | -277.83 | | 25 | 2005-11-08 | -178.00 | | 26 | 2006-03-24 | 94.24 | | 26 | 2006-03-24 | 74.40 | | 26 | 2006-03-24 | 248.00| | 27 | 2006-03-24 | 22.04 | | 27 | 2006-03-24 | 17.40 | | 27 | 2006-03-24 | 58.00 | | 28 | 2006-06-07 | 185.00| | 29 | 2006-03-27 | 136.00| | 30 | 2006-07-18 | 398.16| | 31 | 2006-02-04 | 500.00| | 32 | 2006-04-06 | 64.00 | | 35 | 2006-04-15 | 1000.00 | | 37 | 2005-12-23 | 200.00| | 38 | 2006-05-12 | -51.04| | 39 | 2005-07-31 | 1191.00 | | 40 | 2006-05-29 | 65.00 | +-++---+ 53 rows in set (0.52 sec) Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 11:36 PM, mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- 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]
finding row with latest date in a range from joined tables in v4.0.26
my ISP is using mysql v4.0.26 I am trying to do the following SQL and cannot get it to parse without an #1064 error. select r.prog_id, r.prog_name, p.show_id, p.show_title, p.show_desc, p.show_speaker, p.show_date_recorded, s.spk_name from programmes p, series r, speaker s where r.prog_id = p.show_prog_id and p.show_speaker = s.spk_id and r.prog_id 5 and p.show_date_recorded between '2006-08-25' and '2006-09-22' and p.show_date_recorded = (select max(show_date_recorded) from programmes q where q.show_id = p.show_id) Tables are joined as shown by first two where statements; r has 6 unique series, p has multiple programmes and s has multiple speakers I'm trying to pick out the latest programme in each series in a date range and include the series full name (prog_name) and speaker full name (spk_name) from the other tables. I've played around with joins but cannot seem to get round it, Any suggestions apart from ISP using different mySQL greatly appreciated! Rob - Email sent from www.ntlworld.com Virus-checked using McAfee(R) Software Visit www.ntlworld.com/security for more information -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding row with latest date in a range from joined tables in v4.0.26
Rob - sub-selects aren't supported prior to version 4.1. This page may be of some assistance: http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html Have you tried using a MAX(p.show_date_recorded) and appropriate GROUP BY ? Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: my ISP is using mysql v4.0.26 I am trying to do the following SQL and cannot get it to parse without an #1064 error. select r.prog_id, r.prog_name, p.show_id, p.show_title, p.show_desc, p.show_speaker, p.show_date_recorded, s.spk_name from programmes p, series r, speaker s where r.prog_id = p.show_prog_id and p.show_speaker = s.spk_id and r.prog_id 5 and p.show_date_recorded between '2006-08-25' and '2006-09-22' and p.show_date_recorded = (select max(show_date_recorded) from programmes q where q.show_id = p.show_id) Tables are joined as shown by first two where statements; r has 6 unique series, p has multiple programmes and s has multiple speakers I'm trying to pick out the latest programme in each series in a date range and include the series full name (prog_name) and speaker full name (spk_name) from the other tables. I've played around with joins but cannot seem to get round it, Any suggestions apart from ISP using different mySQL greatly appreciated! Rob - Email sent from www.ntlworld.com Virus-checked using McAfee(R) Software Visit www.ntlworld.com/security for more information -- 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: Is there a way to load non-native Date type with LOAD DATA
Use a command line tool to change the date. I am only familiar with perl and it would be a very short script to change to -MM-DD. At 10:48 PM 9/2/2006, David Perron wrote: I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? Thanks in advance for any help! David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Chris Jones 14 Oneida Avenue Toronto, ON M5J 2E3. Tel. 416-203-7465 Fax. 416-946-1005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to load non-native Date type with LOAD DATA
David Perron wrote: I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? in VI the following should work depending on the other data in the file :%s/ \(\d\d\)\/\(\d\d\)\/\(\d\d\d\d\)/ \3-\1-\2/ :%s/ \(\d\d\)\/\(\d\)\/\(\d\d\d\d\)/ \3-\1-0\2/ :%s/ \(\d\)\/\(\d\d\)\/\(\d\d\d\d\)/ \3-0\1-\2/ :%s/ \(\d\)\/\(\d\)\/\(\d\d\d\d\)/ \3-0\1-0\2/ this only works if there is a space in front of the dates. If the date is the first thing on the line, replace the first space on each line with a ^ and remove the second space. If the date is quoted, replace both spaces on each line with a quote. If the date is preceded by just a tab, replace both spaces on each line with a \t -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to load non-native Date type with LOAD DATA
I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? If you are using MySQL 5.0.3 or greater, you should be able to transform your existing string date datq as you use LOAD DATA statement... Read up on the syntax for utilizing column lists, user variables and a SET clause in conjunction with LOAD DATA --- http://dev.mysql.com/doc/refman/5.0/en/load-data.html Then the you can use the SET clause to transform your existing string date data into MySQL's date type using the STR_TO_DATE(str,format) function --- http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Basically what you will need to do is specify the column list in your LOAD DATA statement but for your string date data you will want to substitute a user variable, then include that user variable in your SET clause inside the STR_TO_DATE function with the relevant format. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way to load non-native Date type with LOAD DATA
I have a pretty large file with a Date column in the format M/D/. Is there a way to either change the Date data type in the table or a method to indicate the date format in the LOAD DATA statement in order to handle this? Thanks in advance for any help! David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select between date
On 8/3/06, Penduga Arus [EMAIL PROTECTED] wrote: On 8/1/06, Chris [EMAIL PROTECTED] wrote: Did you look at the link David sent you? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html thanks.. I manage to do that.. below is my solution. please advice if there is any better solution SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as a017tkhlahir, MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh FROM a017 MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY)) AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY)) ORDER BY bulan, tarikh I have notice problem with my sql statment above, when it run on the 25th for the month which have 31 days the statment isnt valid anymore. FYI my a017tkhlahir is in date format (-mm-dd) Please advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select between date
You have a table containing birthdates (date field, including year)
and you want to display all rows for which the birthday will occur in
the next week (seven days).
You tried this query:
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
but found a problem - that sometimes birthdates on or after the 25th
in months with 31 days will not show.
This could happen in December, after December 25, because then the
month field will be 12 but the month field for DATEADD(CURDATE(),
INTERVAL 7 DAY) will be 1 and so nothing will match the clause
BETWEEN 12 and 1
I don't immediately see that this would be a problem in other
months. For example:
mysql select 'fish' from t1 where 3 between 12 and 1;
Empty set (0.00 sec)
mysql select 'fish' from t1 where 3 between 1 and 12;
+--+
| fish |
+--+
| fish |
+--+
1 row in set (0.03 sec)
One solution is to create a new date from the birthday in the table
by taking the year from the current date and the month and day from
the birthdate and then checking to see if that date is in the next
seven days, that is, between CURDATE() and DATEADD(CURDATE(),
INTERVAL 7 DAY). Here is an example of how that might work:
mysql show create table birthdays;
+---
+---
---+
| Table | Create
Table
|
+---
+---
---+
| birthdays | CREATE TABLE `birthdays` (
`name` varchar(32) default NULL,
`birthdate` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---
+---
---+
1 row in set (0.00 sec)
mysql select * from birthdays;
+++
| name | birthdate |
+++
| P.G. Wodehouse | 1881-10-15 |
| John Marquand | 1893-11-10 |
| Ian Flemming | 1908-05-28 |
| John Grisham | 1955-02-08 |
| Jeffrey Archer | 1940-04-15 |
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+++
7 rows in set (0.00 sec)
mysql SELECT * FROM birthdays WHERE STR_TO_DATE(CONCAT_WS('-', YEAR
(CURDATE()), MONTH(birthdate), DAY(birthdate)), '%Y-%m-%d') BETWEEN
CURDATE() AND ADDDATE(CURDATE(), INTERVAL 7 DAY);
+++
| name | birthdate |
+++
| Keanu Reeves | 1964-09-02 |
| Fred MacMurray | 1908-08-30 |
+++
2 rows in set (0.00 sec)
Good luck!
Douglas Sims
[EMAIL PROTECTED]
On Aug 29, 2006, at 10:13 PM, Penduga Arus wrote:
On 8/3/06, Penduga Arus [EMAIL PROTECTED] wrote:
On 8/1/06, Chris [EMAIL PROTECTED] wrote:
Did you look at the link David sent you?
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
thanks.. I manage to do that.. below is my solution. please advice if
there is any better solution
SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as
a017tkhlahir,
MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh
FROM a017
MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and
MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY))
AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and
DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY))
ORDER BY bulan, tarikh
I have notice problem with my sql statment above, when it run on the
25th for the month which have 31 days the statment isnt valid anymore.
FYI my a017tkhlahir is in date format (-mm-dd)
Please advice.
--
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]
Why does MySQL accept fake date?
Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does MySQL accept fake date?
Hello Mark, in Versions of MySQL prior to 5.0.2 it is only checked that the year-part ranges from 1000-, the month-part from 1-12 and the day-part ranges from 1-31 within the date column. With 5.0.2 of MySQL the Dates must be legal, so 2006-02-31 is no more possible by default. You can however turn on that behavior again by using |the option ALLOW_INVALID_DATES when starting the MySQL Server. So, either you take care that valid dates are entered in your Column by checking before inserting data with your favorite scripting/programming language or upgrade to MySQL 5.0.2 which however will prevent you from inserting invalid dates in the first place. Greets Rocco | Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does MySQL accept fake date?
Hello Rocco, Thank you for your reply. Clear and simple. :) I couldn't upgrade the MySQL server on my production server just yet, but I upgraded the local test MySQL server on a Windoze machine. And it worked like a charm. :) I just needed to create a one-time table of how many days there are in each month for the next ten years (that's what the 'invalid date' test was supposed to do), and at what day of the week they start. In MySQL 5.0.2 this really worked very well. Thank you! :) Hello Mark, in Versions of MySQL prior to 5.0.2 it is only checked that the year-- part ranges from 1000-, the month-part from 1-12 and the day-part ranges from 1-31 within the date column. With 5.0.2 of MySQL the Dates must be legal, so 2006-02-31 is no more possible by default. You can however turn on that behavior again by us- ing |the option ALLOW_INVALID_DATES when starting the MySQL Server. So, either you take care that valid dates are entered in your Column by checking before inserting data with your favorite scripting/programming language or upgrade to MySQL 5.0.2 which however will prevent you from inserting invalid dates in the first place. Greets Rocco Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ddmmyyyy-format date hangs MySQL - 5.0.22/InnoDB/WinXP-SP2
Hi,
In my earlier post, I was making a mistake (though I didn't do so in
the posted text!) - I was passing the dob (the date field) in the
ddmm format. When I passed the date field in mmdd format, the
stored procedure ran fine and the record got inserted. The problem is
MySQL hangs/goes in limbo quitely instead of throwing back an error.
Certainly smells like a bug. ???
I have psted the text of my original post at the end of this message.
--
Asif
I have the following database objects in a purely InnoDB database:
--
CREATE TABLE Person (
PersonIDint not null PRIMARY KEY,
Ttl char(15),
FllNm varchar(50),
frstNm varchar(15) not null,
midNm varchar(15),
lstNm varchar(15) not null,
Gender char(1) not null DEFAULT 'M' CHECK
Gender in ('M','F'),
dob dateCHECK DOB '19000101',
nicNo varchar(13),
mrtlSttschar(1) not null DEFAULT 'M' CHECK
mrtlStts in ('S','M','D','W'),
cellNo varchar(15),
website varchar(80),
Sttsint not null DEFAULT 1, /*
1=Active, 2=Inactive */
index PersonDob_ndx (dob),
index PersonNIC_ndx (nicno),
index PersonCell_ndx (cellNo),
index PersonFllNm_ndx (FllNm),
index PersonNm_ndx (frstNm, midNm, lstNm)
) ENGINE=InnoDB;
CREATE FUNCTION CharValIsNumeric (v VARCHAR(15))
RETURNS boolean
BEGIN
declare i, l int(2);
set l=char_length(v);
set i=1;
while (i = l) and (substring(v,i,1) in
('1','2','3','4','5','6','7','8','9','0')) do
set i=i+1;
end while;
IF i l THEN
return 1;
else
return 0;
end if;
END;
create procedure PersonAdd (Ttl_char(15),
frstNm_ varchar(15),
midNm_ varchar(15),
lstNm_ varchar(15),
Gender_ char(1),
dob_char(8),/* mmdd */
nicNo_ varchar(13),
mrtlStts_ char(1),
cellNo_ varchar(15),
website_varchar(80),
machine_no_ int)
begin
insert into Person (PersonID, Ttl, frstNm, midNm, lstNm, Gender, dob,
nicNo, mrtlStts,
cellNo, website)
select (machine_no_ * 100) + (CASE WHEN ((max(PersonID)) -
((max(PersonID) div 100) * 100)) is null Then
0
ELSE
((max(PersonID)) - ((max(PersonID) div 100)
* 100))
END) + 1 as PersonID, Ttl_, frstNm_, midNm_, lstNm_,
Gender_, dob_, nicNo_,
mrtlStts_, cellNo_, website_
from Person;
end;
create trigger Person_Check_bi
before insert on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
set @errmsg = 'Date of birth set far back in time!
Less than 1900';
insert into tmp1 (checkCol) values (1);
end if;
if new.mrtlStts 'S' and
new.mrtlStts 'M' and
new.mrtlStts 'D' and
new.mrtlStts 'W' then
set @errmsg = 'Marital Status not equal to one of
S,M,D,W - Single,
Married, Divorced and Widowed';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.nicno)))=0 then
set @errmsg = 'NIC No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
if CharValIsNumeric (ltrim(rtrim(new.cellno)))=0 then
set @errmsg = 'Cell/Mobile No contains non-numeric characters';
insert into tmp1 (checkCol) values (1);
end if;
end;
create trigger Person_Check_bu
before update on Person
for each row
begin
if new.gender 'M' and new.gender 'F' then
set @errmsg = 'Gender value not equal to either M or F';
insert into tmp1 (checkCol) values (1);
end if;
if new.dob '19000101' then
set @errmsg = 'Date of birth set far back in time!
Less than 1900';
insert into tmp1 (checkCol) values (1
Re: select between date
On 8/1/06, Chris [EMAIL PROTECTED] wrote: Did you look at the link David sent you? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html thanks.. I manage to do that.. below is my solution. please advice if there is any better solution SELECT a017namaper, DATE_FORMAT(a017tkhlahir, '%e/%c/%Y') as a017tkhlahir, MONTH(a017tkhlahir) as bulan, DAY(a017tkhlahir) as tarikh FROM a017 MONTH(a017tkhlahir) BETWEEN MONTH(CURDATE()) and MONTH(ADDDATE(CURDATE(), INTERVAL 7 DAY)) AND DAY(a017tkhlahir) BETWEEN DAY(CURDATE()) and DAY(ADDDATE(CURDATE(),INTERVAL 7 DAY)) ORDER BY bulan, tarikh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select between date
I want to do a program to display birthday for our staff. I have a field named birthday with date format (-mm-dd), from this field I want to display the staff who will have their birthday start from current date to 7 days a head. please help, thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select between date
Hi, Try here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Penduga Arus [mailto:[EMAIL PROTECTED] Sent: Monday, 31 July 2006 9:03 PM To: mysql@lists.mysql.com Subject: select between date I want to do a program to display birthday for our staff. I have a field named birthday with date format (-mm-dd), from this field I want to display the staff who will have their birthday start from current date to 7 days a head. please help, thanks in advance -- 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: select between date
What version of MySQL do you have? Depending on that, there are different methods. -Original Message- From: Penduga Arus [mailto:[EMAIL PROTECTED] Sent: Monday, July 31, 2006 6:33 PM To: mysql@lists.mysql.com Subject: select between date I want to do a program to display birthday for our staff. I have a field named birthday with date format (-mm-dd), from this field I want to display the staff who will have their birthday start from current date to 7 days a head. please help, thanks in advance -- 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: select between date
Penduga Arus wrote: On 7/31/06, Peter Lauri [EMAIL PROTECTED] wrote: What version of MySQL do you have? Depending on that, there are different methods. MySQL 5, can you please show me how to do it.. Did you look at the link David sent you? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html If you can't work it out, post the SQL you are trying to use and the results you expect to get and someone might be able to help you further. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to query on part of a date column?
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
Barry, I think you've got too many quotes in your SQL - the db is
trying to find the year from the string 'date paid'. You want to use
it as a column name, so drop the quotes:
Select * from Capclave2005reg
Where Year(Date Paid) = 2004;
If you've really got a space in your column name, try enclosing it in
backticks instead of quotes - ` instead of ', as in
Select * from Capclave2005reg
Where Year(`Date Paid`) = 2004;
Regards,
Dan
On 7/20/06, Barry Newton [EMAIL PROTECTED] wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious tests
like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
--
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: How to query on part of a date column?
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious
tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a SELECT,
but can't seem to use it in a WHERE clause at all. There has to be
something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character _. You
are only creating problems for yourself if you leave the blanks in the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
As long as backticks are used around fieldnames, spaces and/or reserved
words are fine, tho it does tend to create more work for the user ;)
mos wrote:
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each
person has a registration date, kept in a standard date field. How do
I select for people who registered in a particular month or year? The
obvious tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a
SELECT, but can't seem to use it in a WHERE clause at all. There has
to be something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character _.
You are only creating problems for yourself if you leave the blanks in
the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to query on part of a date column?
At 06:20 PM 7/20/2006, Martin Jespersen wrote:
As long as backticks are used around fieldnames, spaces and/or reserved
words are fine, tho it does tend to create more work for the user ;)
Correct. But how many people want to create more work for themselves? A
show of hands please!bg
Mike
mos wrote:
At 12:02 PM 7/20/2006, you wrote:
I've got a table of people who registered for a convention. Each person
has a registration date, kept in a standard date field. How do I select
for people who registered in a particular month or year? The obvious
tests like:
Select * from Capclave2005reg
Where Year('Date Paid') = 2004;
return no rows. I can extract any piece of that date I want in a
SELECT, but can't seem to use it in a WHERE clause at all. There has to
be something really obvious that I'm missing?
Barry Newton
Barry,
I strongly recommend getting rid of the spaces in your
column/table names and replace them with an underscore character _.
You are only creating problems for yourself if you leave the blanks in
the names.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date comparisons
I've found something that works (in MySQL 5, anyway), but I don't know whether it's accepted practice. If I want to find all records with a date in, say, March 2006, it works if I use datefield like '2006-03%' because it's a string. This seems kind of obvious and a lot tidier than doing datefield = '2006-03-01' and datefield = '2006-03-31', but are there pitfalls I should know about? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Is there something that sticks out that makes you an exceptional pole-vaulter? -- Adrian Chiles (to Sergei Bubka), BBC Radio 5 Live -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date comparisons
I've found something that works (in MySQL 5, anyway), but I don't know whether it's accepted practice. If I want to find all records with a date in, say, March 2006, it works if I use datefield like '2006-03%' because it's a string. This seems kind of obvious and a lot tidier than doing datefield = '2006-03-01' and datefield = '2006-03-31', but are there pitfalls I should know about? Speed would be my first thought... I'd time them. I'd also prepend 'explain' as I'm guessing the first won't use an index and the second will (assuming you have indexes). -p -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
