Date Comparison Trouble
Spent the last 30 mins looking for a solution and haven't found one so:
The table looks like:
ID
aDate
Days - days in advance
1 | 2008-03-10 12:00:00 | 7
Im trying to do the following:
SELECT table. *
FROM table
WHERE aDate >= '2008-03-(03+Days) 11:30:00'
Basically so i can feed a date and it uses that date plus whatever is in the days fields, simple i know!
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Date Comparison 2
I want to compare dates in sequential records (in mysql format YYYY-MM-DD) in a db. I have a loop to iterate through a set of records. In this loop, how can I refer to the previous record in the loop? As in: for ($i=0; $i <$num_results; i++) { $row = mysql_fetch_assoc($result); if ($row('thisDate') != the previous record's date) { do something } }
View Replies !
View Related
Date Comparison Problem
I have a website with a diary of events. I have just come across this problem: The diary is listed on the site month by month - you can click a month and events for that month will show up. Events have a from date and to date and can span across multiple months. This has all been working fine until I added an event running from december 06 to january 07 - it no longer showed up. Here is how my query looked: (using december 2006 as the selected page as an example) SELECT * FROM diary WHERE (MONTH(from_date) <= MONTH(��-12-01') AND MONTH(to_date) >= MONTH(��-12-01') AND YEAR(from_date) = YEAR(��-12-01')) OR (MONTH(from_date) <= MONTH(��-12-01') AND MONTH(to_date) >= MONTH(��-12-01') AND YEAR(to_date) = YEAR(��-12-01')) ORDER BY from_date, to_date Now looking back it is obvious that this will not work as it is looking for a match in the years. After some research and trial and error I have come up with the following query: SELECT * FROM diary WHERE (UNIX_TIMESTAMP(��-12-01') BETWEEN UNIX_TIMESTAMP(from_date) AND UNIX_TIMESTAMP(to_date)) ORDER BY from_date, to_date However this is still not working. This is really bugging me and any help will be greatly appreciated. Thanks!
View Replies !
View Related
Fake Date Comparison
Code: select * from tareas where STR_TO_DATE( fecha, '%d/%m/%Y' ) = STR_TO_DATE( 19/11/2007, '%d/%m/%Y' )' order by STR_TO_DATE( fecha, '%d/%m/%Y' ) DESC Yes, an operator wrong, but why ?
View Replies !
View Related
Date And Unix Timestamp Comparison
i have a field in my database called 'release' that contains a date in the format YYYY-MM-DD, except its stored as text and not in the date format. I need to exclude certain results from my query based on how recent it was released and im having a hard time. ive been trying to use this... SELECT title FROM products WHERE release< FROM_UNIXTIME(unix_timestamp(now()) - 183*86400,'%Y-%m-%d') ;
View Replies !
View Related
Date Comparison Not Working (dammit!)
My date_added field is a DATETIME mySQL 4.1.10 I'm trying to get any record that was created over 72 hours ago and has not activated their account. PHP SELECT fname,email,uname,passwd FROM customers WHERE date_added >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 72 HOUR) AND activate = 0 That dog just don't wanna hunt. NULL is returned. The query returned in phpmyadmin is PHP SELECT fname, email, uname, passwd FROM customers WHERE date_added >= DATE_SUB( CURRENT_TIMESTAMP , INTERVAL 5 MINUTE ) LIMIT 0 , 30 I never signed up for a limit! And, while I have your attention I want to do the same, except get any record that has been active for 27 days (got to send an email, because I'm gonna dump their a$$ on day 30!)
View Replies !
View Related
Trouble With Date Formatting And Searching
I have a date field in my database that is called 'the_date' type = 'date' default = '0000-00-00' the problem is I am struggling to search a date range as the user can't understand the 0000-00-00 format. I am trying this; ------------------------------------------------------------------------ SELECT DATE_FORMAT(the_date, '%m/%d/%Y') as the_date, order_no, status, sales_rep, cust_id FROM orders where order_id > '0' and (the_date between DATE_FORMAT('01/01/06', '%Y-%m-%d') and DATE_FORMAT('03/25/06', '%Y-%m-%d')) ------------------------------------------------------------------------- Is that even possible?
View Replies !
View Related
Md5 Comparison
UPDATE admin_users SET password = md5(?') WHERE id = 1; SELECT password = md5(?') FROM admin_users WHERE id = 1; The result of the last query is "0", which means the values don't match. How can that be? What am I missing? The hashes look just the same when I echo them. Is it a data type problem? EDIT: I should mention the m5 password hash is stored in a binary(64) column.
View Replies !
View Related
Comparison In Db.
Don't know if it's even do-able in MySQL but I kinda think it is. lets make an example table over a log. day - action 1 ---- foo 1 ---- bar 2 ---- foo 2 ---- foo 2 ---- bar 2 ---- bar 2 ---- foo Now I want, with one query, most of all, select the increase in percent and the name of the action that has increased the most from day one to day two. If this isn't possible I would like to select the name, the amount for the action on day one and the increase of the top increaser.
View Replies !
View Related
Comparison
I want to compare a text box value with the one stored in the mysql table. Here is the input of text box. HTML Code: 352969678 and the mysql table record is like HTML Code: 352 969678 a space is there after the first 3 digits. So i want to compare it via SQL. Suppose that the data from the text box is in $data and the column in table is "phone" so i want to do this (in PHP): HTML Code: "select * from some_table where phone = ".$data But it will not work as the data in the $data has not space after the first 3 digits while the record in table in database is having a space after the first 3 digits...
View Replies !
View Related
Database Comparison
I am looking for some papers/information that compare relational databases such as oracle, mysql, sql server etc. I am particularly interested in their features such as locking mechanisms, integrity constraints, views... Anyone know where I can find the information?
View Replies !
View Related
Text Comparison
I'm having a problem with one query. My database has the following fields: id(int), time(int), and groupfield(enum - 5 possibilities). I want to select all the rows where the time is between let's say 12 and 24 and the group is specified by the user using a html drop list. The variable is named $group. Code:
View Replies !
View Related
Comparison Optimization
On to my problem. I'm running a query on a table with over 100,000 rows in it. Basically, my query is performing a "similar" check on books that people have logged in their "book list". It looks through the table and checks to see who has the most number of books similar to you. Here's the query: select count(a.mem_id) as numMatches,m.username,m.mem_id as memberID from book_list a LEFT JOIN book_list_members m ON a.mem_id=m.mem_id where a.book_id in(224,164,30,43,47,1,6,52,90,45,120,270,...,2442) GROUP BY a.mem_id ORDER BY numMatches DESC LIMIT 50 In the in() comparison, this can be anywhere from 1 integer to 1000 integers, it all depends on how many books a user has in their list. I've indexed "book_id", but unfortunately if there are more than say, 100 elements in the IN() comparison, the query takes anywhere from 1-2 seconds to execute. That's a long time when I have hundreds of users hitting a website and running that query. Is there a better way to do this? Is there a way I can "store" this query result somewhere, and only have it updated every couple hours (cached in other words, for immediate access).
View Replies !
View Related
Float Comparison
I'm writing a simple database with one table and a few fields that lists some books. One of the fields is an Unsigned Float field that lists the book's reading level. Then I have a PHP/web page front end that users can search for books listed in the database. Basically this is the probelm I'm having: If a user asks for all the books between the reading levels 3.0 and 3.3 -- MySQL returns every record that has a reading level of 3 up to and including all the books with a reading level of 3.3. However, if a user types in the form they want all the books between 3.0 and 3.2 -- MySQL returns all the 3.0 and 3.1 books, but returns none of the 3.2 level books. The code is a simple select query that ends up being: SELECT * FROM MyTable WHERE ReadingLevel >= 3 AND ReadingLevel <= 3.x
View Replies !
View Related
Comparison Query?
I have two tables in my database that collect membership/user info. How do I write a query to compare the two and have the query spit out the users that are on one but not the other?
View Replies !
View Related
Previous Row Comparison
I have a table in this format: Column A | Column B 1 | 2 1 | 3 1 | 4 2 | 5 ..... ..... What I want to do is something like this: If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?
View Replies !
View Related
Comparison Query
I have two tables, I have a company table and a client table. The fields in question are as follows Company.companyid Company.maincontactid Contact.contactid Due to bad design and bad data entry we have a bunch of companies with a contact number but no entry in the contact table. I want to run a query to dump a list of Company.companyid and Company.maincontactid where Company.maincontactid has no corrisponding Contact.contacid
View Replies !
View Related
Sql Comparison Tools
We've got a bunch of fairly long and complex sql statements that we need to compare between our dev and prd environments. The dev environment has been changed by a bunch of patches, while prd has been changed by an incompetent BA. Does anyone know of any tools that can help do this? Standard text/file comparison tools haven't been any help, so we're down to eyeballing them, which is both no fun and prone to error...
View Replies !
View Related
Dates Comparison??
I'm trying to find people from my db based on a given min_date and max_date using: SELECT . . . WHERE birthdate BETWEEN '".$dd_min_age."' AND '".$dd_max_age."' but if a range is specified there are no results returned. If I choose though the same date for min and max then I get the corresponding results.
View Replies !
View Related
Trouble With MAX
I can't seem the get the MAX to work. I'm not getting any errors, but it doesn't show any results. I want to show the last Samples order ID that was used for putting in new sample sales, so that we know which number is next. All sample order ids begin with S. Here is the query: Code:
View Replies !
View Related
Multiple LIKE Comparison Using IN Operator
Is there anyway to compare a field with multiple string values using LIKE? Here's the statement I have now: SELECT * FROM list WHERE email LIKE CONVERT( _utf8 'hotmail@hotmail.com' USING latin1 ) OR email LIKE CONVERT( _utf8 'john@hotmail.com' USING latin1 )"; I know how to do it using the IN operator, SELECT * FROM list WHERE email IN ('hotmail@hotmail.com', 'john@hotmail.com') but I need to be able to use the LIKE operator with the CONVERT function. The reason I am asking is because I am trying to match 50+ different email addresses.
View Replies !
View Related
String Comparison Implementation
how string comparison is implemented in mysql. (Both in Physical and Application layer) How queries on string (for example '>' or like or strcmp) are implemented. Any idea where can I find the open source for string comparison/ ordering/ implementation? Does it use the sme structre as strcmp in c?
View Replies !
View Related
Database Comparison Tools
I am looking for an easy way to be able to compare two mysql databases for their difference in tables and fields. It would also be nice for an easy way to make changes to the databases and port over information easily.I did search on this and found a couple of products that I found difficult to work with. The two main ones i tried, one froze when it tried to "sync" the databases and the supposed log of the "sync" wasn't very forthcoming and the other product just plain didn't work.I was wondering if anyone out there had this similar need and what the products/scripts were that they used? I would really thin kthere has to a least be a script that compares databases, even if you can't make changes right then and there.
View Replies !
View Related
String Comparison (substr)
I am trying to update some data between two tables but the only consistent data in both tables is the model number However the problem is that the supplier has altered some of the model number's last 6 digits which is in date format Example - in my database I have model numbers: ABC34030309 AGHDNNEJ040309 In the new table with the data I have ABC34040109 AGHDNNEJ040109 If I could somehow strip the last 6 digits off each I would have a perfect match. I have looked at substring and this appears to only allow me to start at the beginning of any string unless I know the length. as each product model varies in length I cannot use this. I looked at trim but this looks like you have to specify what to trim rather than a number of characters.
View Replies !
View Related
Comparison Of Data Types
I have a large map that is represented in a sql database with an incremental index and a value for each entry representing the type of area on the map.I'm looking to optimize the code that stores and retrieves this information.In an attempt to minimize the number of characters that are sent out from the db, I am storing the types as ascii characters that represent the number type and converting them to numbers once retrieved. In this way, I hope to speed it up by not having to send two-digit numbers.So, my question is whether or not this is a good approach. Would sending just simple numbers be just as fast or faster?Or, more basically, I need a comparison of mysql performance when handling the various types of data. it seems like such a comparison has to exist somewhere.
View Replies !
View Related
Query By Column Comparison
I'm trying to pull a row by comparing that the numerical value of one column is greater than the numerical value of other columns. The manual is quite confusing as to how to phrase such a thing, and I haven't done much more than simple queries up until now. MySQL version: 4.0.22 What I started with that doesn't work: select * from someTable where col1 > col2 and col1 > col3 and col1 > col4 order by someThing limit 1 I'm basically trying to grab one row where the value of one column is greater than the values of the remaining similar columns.
View Replies !
View Related
Equality Comparison Of Char
Is there any difference in speed between an equality comparison of char (or varchar) column values with a constant, and comparison of int column values with a constant? i.e. would this: select * from table where col1 = 'blah'; be much slower or faster than select * from table where col1 = 6; this? Obviously the difference is probably miniscule, but this is a query I am planning on running in a lot of loops so it could be important
View Replies !
View Related
String Comparison To A Given Prefix
I would like to search a column of strings for matches to a given prefix. Is there a way to use existing string comparison functions to do this? Specifically I'm looking for the number of leading characters identical to my (variable) search string: Example: looking for matches to prefix "Robb" SELECT names, some_functions(names, "Robb") AS m FROM etc, etc, etc... | names | m | | Roger | 2 | | Robert | 3 | | Roseanne | 2 | | Cary | 0 | | Randy | 1 | | Robby | 4 | | Allison | 0 | Return the highest value of m for which LEFT(names,m)=LEFT("Robb",m). I've been reading through the section on string comparison function, but can't find anything suitable. But perhaps there is some combination of funtions you could use?
View Replies !
View Related
Database Comparison Tool
I have 2 different servers on different machines. I would like to compare database from this server to each other. Example: I would like to compare database1 (on server 1) to database 1 (on server 2)
View Replies !
View Related
Comparison With Other Rows While Searching
I've a table which looks like below: pk_id,group_id, mysqllate_id, category 1, 1, 1, 'a' 2, 1, 3, 'b' 3, 1, 5, 'c' 4, 2, 1, 'a' 5, 2, 2, 'c' 6, 2, 4, 'd' 7, 3, 1, 'a' 8, 3, 2, 'b' 9, 3, 3, 'd' I want to perform a query like this on table: Get me all 'mysqllate_id' for a 'category'='a', which are common for all 'group_id' found in array (let's say this array has 3 elements 1,2,3). So basically I'm looking for 'Common' mysqllate_ids across group_ids where category is 'a'. So this query should return '1' as mysqllate_id, which is common among all 3 group_id (mentioned in array) and have category 'a'. What would be the best way to do this? It seems like some sort of recursion is required.
View Replies !
View Related
Time Comparison Issue
I have a field storing date/time information in the TIMESTAMP form (mm/dd/yyyy hh:mm:ss AM). Is there a way to compare the current date with this stored date? I am trying to select all of the rows where the saved date is before the current date. I used php to generate the current date and tried using < or <=, but they do not seem to be working.
View Replies !
View Related
Datetime Comparison Working
I'm having a simple sql to extract data from time to time (service application in Delphi) and am storing the last record's datetime (MySQL datatype) as an example: 20/08/2005 6:04:21 PM I want to get the newer data and so, next time i run, i'm comparing the last retrieved record's datetime with the SQL data and want to get only the records having a >= value of DTColumn. *** Laststoredvalue := '20/08/2005 6:04:21 PM'; (am storing as a string) My sql string is built at runtime where i use the following: SQL.Add('WHERE t1.DTColumn >= CAST(' + QuotedStr(Laststoredvalue) + ' AS DATETIME)'); *** When i execute the query and read the first record, It's still getting ALL the records right from the 1st one available that has DTColumn value of '10/12/1981'.
View Replies !
View Related
Comparison Of Database Performance
I'm trying to justify using MS Access for a simple database where some other folks in our company are pumped on SQL Server. They only know that someone else told them SQL Server is the answer to everything, when in fact it's extreme overkill here IMO. This database will have a few tables and is mainly used for storing information regarding CAD drawings. We're not talking about a ton of transactions here! Maybe 100 per day PEAK new records or edits. That's a big maybe, on average probably 10 per day or less. I have a similar system I started 3 years ago which has about 1500 records today. I know Access has a 2gig limit on data and to me this is the only limiting factor to be concerned about. When comparing Access to SQL Server (not even a comparison) it's really about performance right? There will never be tons of transactions and many concurrent users on this database.
View Replies !
View Related
How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username. delimiter $$ create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int ) deterministic begin if length(m_username) = 0 then set m_username = 'jason' #set temporary name if empty end if set m_username_matched = 0; #finds number of matches for the given username select count(*) into m_username_matched #store into the variable to be returned from sampleDB.sampleTable where userName like '%m_username%' #this is the statement that does not work# end$$
View Replies !
View Related
Text With Tab To MySQL - Db Comparison
sorry... i did post first in databases forum... do not know how to delete my thread... so here my questions: i have a text file with TAB separation. in this file, i have some products with lets say: RefNr. / Name / Option / Amount when i open it i see: SOURCE: RefNr.NameOptionAmount 001Product 1Black2 001Product 1Black1 001Product 1Black0 001Product 1Green1 001Product 1Green4 001Product 1Green1 002Product 2Black3 002Product 2Black1 002Product 2Black1 002Product 2Green2 002Product 2Green2 002Product 2Green0 etc etc do you nkow a way to get this instead, after running a script for instance (my question is “how does the script should look like”!): RESULT: RefNr.NameOptionAmount 001Product 1Black3 001Product 1Green6 002Product 2Black5 002Product 2Green4 (i could save this text file first to EXCEL if it helps.) once i’ve done it, i’ll have in a second step to “compare” this file to a mySQL Database. but in the selected DB table, i won’t have exactly the same structure. i mean in my text file i’ll have for instance not only RefNr. / Name / Option / Amount but RefNr. / Name / Option / Amount / Description / DeliverDate / ImgName and in my table in the DB i’ll have: RefNr. / Name / Option / Amount / DeliverDate / Info i will need to compare the amounts between ‘RESULT’ and my mySQL table, to get the amount which changed updated in my DB table. any idea welcome... even if the process is not automated and if i need to do everything manually in phpMyAdmin for instance (i do not even know how to do it in phpMyAdmin!).
View Replies !
View Related
TIMESTAMP Operations & Comparison
I'm trying to write a query that will remove all entries older than 30 days, using MySQL 5. The date is stored as TIMESTAMP. I'm stumped on just how to express "30 days earlier than now". I'm familar with the NOW() function but don't know how to add/subtract dates. The query is otherwise very simple, something like DELETE FROM posts WHERE datecreated < [????]
View Replies !
View Related
String Comparison, Faster With LIKE ?
i use a string that corespond to a page name stored in a DB. I have to search the DB to find the page name and then retreive the rest of needed data. Should i use a simple comparison : WHERE page_name = 'my_string' or should i use a LIKE statement? And what kind of index should i put on field 'page_name' to help make it faster?
View Replies !
View Related
Using A Comparison Variable From One Join To Another
The first join ($query) searches through my team table and finds the number of conferences. In this case there are six of them. This part seems to be working fine. The second one I am trying to pull all the teams out that belong to that conference and list them under it. Each conference doesn't have the same number of teams within it. I seem to be having troubles with this comparison...
View Replies !
View Related
Trouble With A Query
Im dealing with two tables student(columns are sno, sname, address, and sex) and results(columns are cno, sno, and score). I need a query of he snames of femal (sex = 'f') students who take every course taken by J. Brown (sname). So far I have: Code: SELECT student.sname FROM student, result WHERE student.sno = result.sno AND student.sex = 'F'; Now what I am having trouble with is finding the females who are in every course with J. Brown. Now cno from the result table is the course number I need to do something with that but I'm not sure what. Do I need to use a GROUP BY or something along those lines?
View Replies !
View Related
Connection Trouble
Im having trouble connecting to a database on a remote server, i didnt have problems before and cant understand why im suddenly having problems now. The error message i get is "Cant connect to MySQL server on (name of server here) " Im using a program called MySQL-Front as ive not got phpmyadmin installed on the remote host.
View Replies !
View Related
Trouble With 'insert'
I'm trying to insert multiple names in a table and it doesn't want to work... I am a complete noob, sorry. this is the code that doesn't work, i get an unspecified error. insert into delegates (delname) VALUES (Watkins M Abbitt Jr, David B Albo, Kenneth C Alexander)
View Replies !
View Related
Trouble After Going To New Server
Our website has recently been moved to another server. Since then I've noticed that my MySql queries look like this: WHERE `firstname` LIKE CONVERT( _utf8 'john' USING latin1 ) Instead of this: WHERE `firstname` LIKE 'john' I'm not sure what is happening or why. Everything seems to be working fine, but I'm wondering if this is a problem I need to fix to avoid issues down the road.
View Replies !
View Related
Having Trouble With An SQL Statement
I have the following tables (only relevant fields are shown): competitions id = competition ID portfolios id = portfolio ID comp_id = competition ID actions port_id = portfolio ID What I want to do is put together an SQL statement that, when given the competition ID, will delete all portfolios that have that comp_id and all actions have the port_id of one the deleted portfolios. That is, there is many portfolios for each competition. There is many actions for each portfolio (and therefore with each competition). I want to delete everything that has to do with the specified competition.
View Replies !
View Related
Trouble With Exists
This query looks perfectly fine but I keep getting an error. MySQL SELECT jobId FROM jobs WHERE EXISTS ( SELECT jobId FROM workOrders WHERE workOrders.jobId=jobs.jobId AND workOrders.status <> 7 AND workOrders.status <> 6 ) Here is the error #1064 - You have an error in your SQL syntax near 'EXISTS ( SELECT jobId FROM workOrders WHERE workOrders.jobId=jobs.jobId ' at line 3 Does anyone see anything wrong with this? I'm starting to think I'm brain dead.
View Replies !
View Related
Comma Seperated List Comparison
is it possible to compare acomma separated list aginst another: eg comma list 1 => 1,2,3,4,5 comma list 2 => 3,5 can you check that 3 is in both, and 5 is in both, therfore they match??? the comparison is to check that if product a who supplies products 1,2,3,4,5 can be used instead of product b who supplies 3,5 as product a already supplies them.
View Replies !
View Related
|
TRACKER
