Select And Where Statement Differences Using Date Interval
Why am I getting a NULL / Empty set result when I say:
mysql> select date,last from table where date = date + interval 1 minute;
Empty set (.63 sec)
But when I select date+interval 1 second, it gives me an answer?
mysql> select date,date+interval 1 second from table;
+---------------------+------------------------+
| date | date+interval 1 second |
+---------------------+------------------------+
| 2008-07-02 12:46:27 | 2008-07-02 12:46:28 |
| 2008-07-02 12:46:28 | 2008-07-02 12:46:29 |
| 2008-07-02 12:46:28 | 2008-07-02 12:46:29 |
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Select Statement With Date
Basically My events lister page sends a query to my database and so far i have it returning my event(s) in order of startDay and displaying events in the future. $query='SELECT * FROM events WHERE startDay >= CURRENT_DATE ORDER BY events . startDay ASC LIMIT 0, 30 ' ; I now want to display the current events for the current month. I guess I would have to use AND but am unsure of how to to actually filter out the current month. Would this be better done in php? I had a similar problem with the DATE_FORMAT function as i was trying to convert the dates to a European format. I eventually used php to convert the dates instead.
View Replies !
View Related
Date Differential Select Statement
I have a database table with a date field in it. What I would like to do is select the number of days between the date field in my table and the current date. I think I now the structure of the command but I'm having some trouble with the syntax. Logically I see the Select statement as something like: SELECT tbl.date-<Current Date> AS NumofDays FROM tbl
View Replies !
View Related
Date Interval With Format
Code: SELECT NAME,DATE_FORMAT(DATEBORN,'%d/%m/%Y') AS BDAY WHERE WEEK(DATEBORN) = WEEK(CURRENT_DATE()) ORDER BY DATEBORN ASC This script is executed every friday with cron, and shows all the people whom had birthday in last 7 days... Now, How could I modify query to show something like: "Listing of people whom had birthday between 20/07/2005 and 27/07/2005" (I'd like to know how to get the date limits in this interval of 7 days selected) I mean, how to show date interval/range of people whom were selected in the script execution, no matter which day script was execd.
View Replies !
View Related
Query Date Interval Question
I have used MySQL for quite some time but have just recently started making more advanced queries. I hope someone can help me with this. The problem is this: I have a table of Orders with Price, Date, Salesman and ID. To get the total sales of one user for say week 2 or February is not very hard, that would be something like: SELECT SUM(Price) AS SumPrice FROM Order WHERE OrderDate BETWEEN "2007-02-01" AND "2007-02-28" AND Salesman="Bob" GROUP BY Salesman The problem now is that I would like to have a result with like weeks 1-10 or Jan-May, with each week or month and its correspongding SUM on a separate row. Can anyone help me and say what is missing please? I reckon that my query above would be a subquery in a larger query specifying the intervals, but I can't figure out how to do it.
View Replies !
View Related
Select Dates After A Specified Interval
The code i have come up with thus far is as follows: SET @StartDate='2005-11-22', @StopDate1='2007-11-22', @date1= '2006-01-31'; #Note: do not set StopTime greater than 23:59:59 SELECT Table1.StopDate As Date FROM dbase1.Table1 where StopDate= @date1 @date1 = DATE_ADD('2006-01-31', Interval 2 DAY) WHERE ((dbase1.Table1.StopDate)>= @StartDate) AND ((dbase1.Table1.StopDate)<= @StopDate1) GROUP BY Table1.StopDate; date1 is the first date in the series. Can someone point out where i am going wrong or if there is an easier way to do this?
View Replies !
View Related
Select And Displaying 90 Day Interval Over A Period
SELECT DATE_ADD('2008-09-07', INTERVAL 90 DAY) What I'm basically after is to display a range of dates with a 90 day interval till a given date. As per the nultiple select statements below. SELECT DATE_ADD('2008-09-07', INTERVAL 90 DAY) SELECT DATE_ADD('2008-12-06', INTERVAL 90 DAY) SELECT DATE_ADD('2009-03-06', INTERVAL 90 DAY) SELECT DATE_ADD('2009-06-04', INTERVAL 90 DAY) SELECT DATE_ADD('2009-09-02', INTERVAL 90 DAY) SELECT DATE_ADD('2009-12-01', INTERVAL 90 DAY) SELECT DATE_ADD('2009-12-01', INTERVAL 90 DAY)
View Replies !
View Related
Date :: Select X Number Of Months Ahead Of Current Date
I am trying to query dates stored in my mysql database... eg: 2005-12-13 (13th Dec 2005) 2006-03-14 (14th Mar 2005) My SQL query needs to fetch all the records which are X number of months ahead of the current date. eg: I query it saying "get me all records where the date is 1 month from now.... I have used this (MONTH(date_review)-MONTH(CURDATE())) which returns 1 - so this is ok - as it's November, and december is one month away.... but when I ask for 3 months - it wont get the records... I know I somehow need to add the months on - but how do I do this with the year attached too?
View Replies !
View Related
Combining SELECT Statements Into One SELECT Statement.
I want to take the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN authuser ON name.empnumber = authuser.uname AND authuser.team = 'PHQ' ORDER BY name.lastname, name.firstname; and the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN crew_attendance_6QJ ON name.empnumber = crew_attendance_6QJ.empno ORDER BY name.lastname, name.firstname; And combine them into one query that outputs all of the results both queries would output. Then order those results. So far I have come up with:
View Replies !
View Related
Select Date Date Functions
i do very elementary sql and am having trouble with SELECT i have a database of the chinese years, they start at different days and months so what i did was create a table called year: year_id (int) year_name (var) ex:RAT year_starts (date) ex: 1917-01-23 year_ends (date) ex: 1918-02-10 so when i want to find out what year somebody was born ($birthdate), i wanted the database to do something like: "SELECT * FROM year WHERE year.year_starts >= $birthdate AND year.year_end <= $birthdate" I can get it to select from either year.year_starts >= $birthdate (alone) or year.year_end <= $birthdate
View Replies !
View Related
Select Statement!
I have a simple query that is similar to this one: SELECT distinct b.username from permissions a, users b, links c where a.user_id = b.id and c.information = 14; In this case, it selects the all users details from table 'user' that are permissioned to access a certain information with id = 14. The problem is that I need to select the users that are not permissioned but I have sql v3.23 and I canot use union. In other words, I need to do a select b.username from users b where b.username not in "the result" of the last select.
View Replies !
View Related
One Select Statement
I have a table with the fields id | titleid | catid Each title can have more than 1 catid so it's possible for the fields to look like this: id | titleid | catid 1 1 1 2 1 2 3 2 1 Now I want to select any rows where titleid matched more than one catid. In this example I would like to select titleid 1 because it is in catid 1 and catid 2 but I can't seem to figure out a statement that properly says "give me any titleid that has a catid of 1 AND a catid of 2"
View Replies !
View Related
SELECT Statement With OR
I need some help with this select statement: SELECT * FROM gallery WHERE type = 'skate' AND park = 'Both' OR park = 'Park 1' I just need it to look at one table and grab all the rows that have a type of 'skate' and have a park value of either 'both' or 'milwaukee'. I think the OR is throwing it off.
View Replies !
View Related
Regarding Select Statement
I am retrieving data from two tables using the UNION statement and i am thinking of having a field in my resultset(this field is not found in the database) to distinguish the data from two tables.Is it possible to set a fixed value for a particular field using the select statement?For example, a field value of 'buy' and 'sell' values are used to distinguish between the two tables.
View Replies !
View Related
Select And A Max In The One Statement
Can you combine a select and a max in the one select statement? Eg,. I want to select like select ids from table_name where ids > $idsvariable... order by ids.... but I also want to grab the highest ids number so I can put $idsvariable (max) into a table so next time it selects only the ids higher than the last time the select was run! So if ids were: 2 5 6 9 12 14 15 then I'd like to get 15 as the max as well... any examples.
View Replies !
View Related
Select Nav Statement
I have a nav table in my database that grabs information to put into the navi menu, this is dependant on user access etc... i have an item in my table that is in zone "members" and it is marked for permission Z. Although when i run the query it dont pick it up.... these are the queries that ive tried... SELECT * FROM acwe_nav WHERE privs LIKE '%A%B%C%D%E%F%X%Y%Z%' AND zone='members' ORDER BY weight; SELECT * FROM acwe_nav WHERE privs LIKE '%ABCDEFXYZ%' AND zone='members' ORDER BY weight;
View Replies !
View Related
Else If In Select Statement
i have a bunch of records that i want to pull on the basis of eg SELECT lat, lng, html, label, icontype FROM ade WHERE icontype='" . $_POST['name'] . "'"; but i'd like to make the first query and also another on an "if else" basis to cover various bases if i post a query where the 'name' isnt an icontype, i want it to look in the "section" field and pull those records matching a certain value ie all records that have a value of "5" is it possible to do something like this SELECT lat, lng, html, label, icontype FROM ade where icontype='" . $_POST['name'] . "' ELSE IF section='" . $_POST['name'] . "' ELSE IF catid='" . $_POST['name'] . "'";
View Replies !
View Related
Is There Something Like A SELECT ALL BUT Statement?
Usually the tables I have to handel have a high number of columns. Additionally, columns are deleted and added dynamically. Most calls made on these tables are select statements: Consider two tables t1 and t2. A natural join results in the columns a,b,c,d,e,f,g,h,z. A usual select statement is the following: SELECT a,b,c,d,e,f,g,SUM(z) FROM t1 NATURAL JOIN t2 GROUP BY a,b,c,d,e,f,g This way the column h could be "eliminated". As you can see the select and the group by clause can become fairly long. What I am looking for is a clever way to say "Group by everything but h and z". Is that possible?
View Replies !
View Related
Select Statement MIN()
SELECT * FROM Distance, LorryDetails WHERE cavan = (Select min (cavan) From Distance where cavan>0); here is the results i am getting source ,Cavan, Athlone, Cork, Galway ,LorryID, LorryReg, Position Athlone 90 0 180 80 3 02-CN-8001 Cork Athlone 90 0 180 80 7 00-CN-1000 Cavan Athlone 90 0 180 80 10 02-G-8001 Galway What i want to try and do is compare the Values with the Column names and get the shortest path. for example when you compare the position cort with the column cork it is 180, cavan is 90, and galway is 80. Galway is the nearest to athlone so i want to narrow down the results to just give me back the row where the position is equal to Galway. Can anyone help me with this.
View Replies !
View Related
Select Statement Within Another?
Tag_id::Img_id::Tag::counter 001::1::Tree::70 002::2::Tree::50 003::3::Pond::30 004::4::Flower::20 005::5::pink::60 lets say the above is my table. this is my current query: SELECT tag, max(counter) FROM tags GROUP BY tag DESC limit 3 this is the result I get: tree 70 pond 30 pink 60 here is can someone pls tell me a way to get the total of the counter of all the occurrences of a particular tag(than the max(counter) ). so that it'l be tree 120 pond 30 pink 60
View Replies !
View Related
SELECT With An AS Statement
I'm using a LEFT JOIN to mash two tables together. It works fine. I'm feeding this result into PHP's mysql_fetch_assoc. mysql_fetch_assoc doesn't handle columns with duplicate names very well, and these two tables do have duplicate names. So, I am using AS to assign an alias to these duplicate column names. But, these tables have about 20 fields each, and I can't figure out how to use my SELECT statement so that it selects all of the fields from tables (SELECT * FROM . . . ) but also assigns an alias to a specific field.
View Replies !
View Related
SELECT MIN, MAX And IF Statement
I have a few entries with a start timestamp, and an end timestamp. What I'd like to know is the earliest start date, and the latest end date, to define the complete time span of the selected entries. Example: id, date_start, date_end: 1, 0000-00-00 00:00:00, 2009-02-07 12:00:00 2, 2009-01-01 12:00:00, 2009-01-05 12:00:00 3, 0000-00-00 00:00:00, 0000-00-00 00:00:00 As you can see, it's possible that a start_date and/or end_date aren't inserted. If I now select the MIN(start_date) and the MAX(end_date) from the above entries: SELECT MIN(start_date) AS start, MAX(end_date) AS end FROM entries WHERE ... The result is: start = 0000-00-00 00:00:00 end = 2009-02-07 12:00:00 What I'd like to return, is the lowest possible date_start, in case a date_start has been entered. In the example above, this would be 2009-01-01 12:00:00, because that's the only date_start that isn't NULL. I tried this: SELECT IF(start_date>0, MIN(start_date), NULL), etc...
View Replies !
View Related
SELECT With IF Statement(?)
# Table structure for table `db_emails` # CREATE TABLE `db_emails` ( `id` int(10) NOT NULL auto_increment, `user_id` int(10) NOT NULL default '0', `email` varchar(255) NOT NULL default '', `main` int(1) NOT NULL default '0', `list` int(2) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; # # Dumping data for table `db_emails` # INSERT INTO `db_emails` VALUES (1, 1, 'a.a@a.se', 1, 1); INSERT INTO `db_emails` VALUES (2, 1, 'aa.aa@a.se', 0, 2); INSERT INTO `db_emails` VALUES (3, 2, 'b.b@b.se', 0, 1); INSERT INTO `db_emails` VALUES (4, 2, 'bb.bb@b.se', 0, 2) So, now to my question. How can i do a SELECT where i want all email adresses BUT only the main if main=1 and all if main=0. The result for this dataset should be: a.a@a.se b.b@b.se bb.bb@b.se
View Replies !
View Related
Select Statement
How can I do a select statement for this problem: If a in ('1', '6', '11', '16', '21', '26', '31', '36', '41', '46', '51', '56') then '1' as ‘stemp’ If a in ('2', '7', '12', '17', '22', '27', '32', '37', '42', '47', '52', '57') then '3' as ‘stemp’ If a in ('3', '8', '13', '18', '23', '28', '33', '38', '43', '48', '53', '58') then '5' as ‘stemp’ If a in ('4', '9', '14', '19', '14', '29', '34', '39', '44', '49', '54', '59') then '7' as ‘stemp’ If a in ('5', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55', '60') then '9' as ‘stemp’ I have this possibilities as a result.
View Replies !
View Related
Select Statement
I have a table containing names of people in the neighborhood. The table only contains first name and family names. How to get lists of first names grouped by the same family name ? This is similar to getting "sum" of salary grouped by a certain condition. Unfortunately, "sum" does not work for strings.
View Replies !
View Related
Need Help With SELECT Statement
I have 2 simple tables: emps and rprts. Table emps has: "ID", "name" (simple string), and some other irrelevant fields. Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean). I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest. For example: emps: id name ------------------ 1 jeff 2 wanda 3 brad rprts: id emp_id rprt type ------------------------------------------- 1 3 df 1 2 1 ie 1 3 2 ei 1 4 3 fd 0 5 2 tg 0 6 3 gf 1 For this data, the following should be selected: id name type ---------------------------- 1 jeff 1 2 wanda 0 3 brad 1 I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.
View Replies !
View Related
Help Me With This Select Statement :)
I have two tables 1- articles table which contains the articleid and article_description 2- articles_translation table which contains translation_id, article_id(foreign key from the articles table) and translation_description I would like to select all the articles which aren't translated yet... How can i do this in MySQL?
View Replies !
View Related
Select Statement In Normalized Db
can anyone share the best way to retrieve from a normalized db? My goal is to get the engines.eng_definition. Here's what I have: thehub: 1N --------- 1)item_no carinfo: 2n ---------- 1)item_no 2)eng_ID engines: 3n ---------- 1)eng_ID 2)eng_definition my query has to get the item number from theHub. Is there a way for me to JOIN these together? I understand how to do a JOIN on theHub and carinfo but can I join engines in the same select statement? right now this is what I have. Keep in mind there are other items being retrieved. Select x, y, z FROM thehub INNER JOIN carinfo ON (thehub.item_no = carinfo.item_no)
View Replies !
View Related
Logs Via Select Statement
I know then when I am using MySQL via the command interface, I can press the up arrow to scroll through previously typed statements. Is this information stored somewhere that can be accessed via log file or via a select statement? Is this available for all accounts?
View Replies !
View Related
Select Statement Needed
The sending page allows the user to choose which search criteria he wants, so this (and many other) pieces of the select statement may or may not be present. The statement always starts with SELECT * from logdata where driver = '$_COOKIE[username]' If the user has selected them and supplied the proper data for a search, other elements will be added to the query string. For example, it may look like SELECT * from logdata where driver = '$_COOKIE[username]' AND amount >= $_POST[txtLowAmount] AND amount <= $_POST[txtHighAmount] AND restaurant regexp??? I'm not even sure if that is where a regexp belongs or if that is how it should be used. Ultimately, I would prefer it be non case sensitive. Here are some examples of what I want: If the user string is "CHIL" Chili'strue Charlie'sfalse CHILtrue chfalse
View Replies !
View Related
Concat In Select Statement
Is it possible to conduct a test in the select statement to see if a value is not found in one table (t2) then get the value from another table (t3). In this case you would have 3 tables. if there is no related record in table2 t2, then get the value from table3 t3 If so what would the code look like? .....
View Replies !
View Related
SQL Statement To Select Only One Name From A Lot Of Same Names
I have SQL statement like "Select Fullnmae from abssubtest" and if I do so the output is the list of all data containing in the Fullname...including the data with same names is repeating...I dont want the repeated data...i.e. if the Fullname data has 1. Ulm 2. Prague 3. Frankfurt 4.Prague 5. Frankfurt I want the output as only 1.Ulm 2.Prague 3.Frankfurt but not the repeated city names...
View Replies !
View Related
IF Statement Within A Select Query?
If there a way to do an IF statement within a MySQL select query? I have this query that gets a vendor's code and name from the database and CONCAT it to put the code into "()" and add the vendor name to the end. Is there a way to write it so the "()" will not be included if the vendor does not have a code set.
View Replies !
View Related
Nested Select Statement
I am working on a feature for my website (MyRoladex.com) where i have a script run through the database once per hour, look to see who has events scheduled that are within the next hour, then look to see what their email/sms/text message preferences are, then email/text them a notification that it is close to time for their scheduled event. Under the Accounts table, i have 5 notable fields: uid, email, mobile, timezone, messagetype Under the DatebookEvents table, the important field is, eventdate (besides 'uid', obviously), which is is YYYY-MM-DD HH-mm-SS format. I did not want to calculate the time zone difference for each user, and then compare it to each of the event dates, and then to the system time, just to see if a notification should be sent, as this would be an immense waste of cpu and bandwidth. The next best thing that i though of was to STORE the event date PLUS or MINUS the time zone difference, whenever the user creates a new event, and then, whenever the user displays their events, it would ADD or SUBTRACT the time zone to the event date, so that it will display correctly for them, AND when the cron job runs once every hour, it will ONLY have to compare the eventdate field with the system time. This is better because the cron job will run once per hour, and have to work with, perhaps, thousands of records, whereas a user may only display his records once or twice per day, thus saving cpu and bandwidth overhead When i was writing the query for the displaying of event details, where i take the event date for the entry they are viewing and ADD or SUBTRACT their time zone to it so that it displays properly according to their time, i came across this problem: Select eid,(eventdate + hour(select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) from DatebookScheduler where uid='666' Returns: #1064 - 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 'select Accounts.timezone from Accounts where Accounts.uid='666')),left(note,40) ' at line 1
View Replies !
View Related
SELECT Statement For Area
I am trying to search a table with a statement like the following: "SELECT name FROM table WHERE state='$state' AND trigger=1 AND areaA='$area' OR areaB = '$area'"; The problem is I need it to find the 'area' value in one of two different columns while matching the 'state' and 'trigger'. My syntax seems to be incorrect and I dont know how to find matching for 2 values while the other 2 columns can be optional, but one must be found.
View Replies !
View Related
Optimize Select Statement
I have a big simple table with 3 fields and 700,000 records. Table: mytable Field1 Field2 Field3 name1 initial1 1 name2 initial2 0 name3 initial3 0 name4 initial4 0 ... ... ... i like to be able to do a fast search where it can return the first record that has 0 in field3. If i go through the whole table, it takes too long. Select * from mytable where field3 = 0; <---- it takes too long to do this i would like to get the record # 2 because this is the first record that field3=0
View Replies !
View Related
Using A Select Statement As A Wildcard
Is there a way to use a sub-query in a wildcard context? I need to reference a field in one table as a search query in another but where the content of the second could exists anywhere in the first. where t1.f1 like '%t2.f2%' I fully appreciate that the above does not work but is there a way that the reference can be enclosed in a wildcard type statement? An example of content: t1.f1 = '123456 ABC1234/09' and t2.f2 = 'ABC1234' If doing a sub query or join how can one acheive a match on the above?
View Replies !
View Related
Result Of SELECT Statement For IF/THEN?
What I am trying to find out if - if I do a SELECT FROM and there are NO results, is there a return code or variable to look at / compare in order to print out a message that the user does not exist? SQL Statement is: SELECT FROM subscribers WHERE (username='".$user1."' AND userpass='".$pass1."') Then I would like to have an IF statement that interrogates something that would be 0 or null if there were no records found on the select.
View Replies !
View Related
Trouble With SELECT Statement
I start to work with MySQL to store infos of my private group of dancers. There I have different tables (personen, addresses, emails, phones, urls) I like to display the infos for all members of this group on a website (php & mysql), but I have trouble with my MySQL statement : SELECT DISTINCT concat(firstname," ",name) AS Mitgliedsname, concat(area,"-",number) AS Telefonnummer, mail_address AS 'Email-Adresse', concat(street," ",houseno,", ",zipcode," ",city) AS Anschrift FROM personen LEFT JOIN phones ON phones.owner = personen.id LEFT JOIN emails ON emails.owner = phones.owner LEFT JOIN addresses ON addresses.owner = emails.owner WHERE personen.status="Active" ORDER BY Mitgliedsname; Some of the members have more than one emailaddress and more than one phonenumber. So when I use the sql statememt above I got multiple combination of some members. here an example of the sql result: Hans 8386884 hans@web.de Street 18, D-11111 Munich Hans 7927678 hans@web.com Street 18, D-11111 Munich Hans 751036 hans@web.net Street 18, D-11111 Munich Hans 7927678 hans@web.de Street 18, D-11111 Munich Hans 751036 hans@web.com Street 18, D-11111 Munich Hans 8386884 hans@web.net Street 18, D-11111 Munich Hans 751036 hans@web.de Street 18, D-11111 Munich Hans 8386884 hans@web.com Street 18, D-11111 Munich Hans 7927678 hans@web.net Street 18, D-11111 Munich Does anyone of you have an idea how I can solve this?
View Replies !
View Related
Select Statement Where Clause
Does MySQL support "==" in the where clause, or is an exact match the default behavior? I noticed the following select statement in existing code: *********************************** $q = "SELECT account_id, account_username, account_email, account_password, account_status FROM account i WHERE (upper(account_username) = $tcName)"; *********************************** I would have expected two records matching for the following situation: $tcName = "BECK", with a record having account_username = "BECK" and a record having account_username = "BECKER" It only returned a single record ("BECK"). The account_username is a varchar - does that make a difference? Are there any MySQL settings which control "exact on"?
View Replies !
View Related
|
TRACKER
