Select Rows From Date Not Time In Where Clause
I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working
View Complete Forum Thread with Replies
Related Forum Messages:
Applying A WHERE Clause To Several Rows On A Joined Table To Select A Single Row
I have one table which is users for example id | username and another table called ratings for example user_id | title | rating and I want to select users who have rated more than one thing... I can do this easily if I want to select users who have rated just one title using the following query SQL Code: SELECT * FROM users INNER JOIN ratings ON users.id = ratings.user_id WHERE ratings.title="blah" GROUP BY users.id but cannot think of a way to do this for more than one rating. Doing SQL Code: SELECT * FROM users INNER JOIN ratings ON users.id = ratings.user_id WHERE ratings.title="blah" AND ratings.title="blahblah" GROUP BY users.id
View Replies !
Select Between Date And Time
I have field date and time respectively. I want to select date between (date1 and time1 > 17) and (date2 < 17) I tried two ways but doesn't work, please see code below. SELECT * from purchases_suppliers WHERE (date_invoice >= '2009/02/25' and time_invoice > 17) AND (date_invoice <= '2009/02/27' AND time_invoice < 17) SELECT * from purchases_suppliers WHERE date_invoice between ('2009/02/25' ANd time_invoice > 17) AND ('2009/02/27' AND time_invoice < 17 )
View Replies !
Select Date And Time In Same Column
I have table called mytable and there I have column named time example: time 2006-07-10 10:28:06 2006-08-18 20:48:22 2006-09-15 12:11:41 2006-10-12 23:06:02 is there any possibles that I can make query example: SELECT *FROM mytable WHERE date BETWEEN 2006-07-10 AND 2006-09-15 AND time BETWEEN 10:28:06 AND 12:11:41 My point is can I make query where I first find between date and after that I make query where I find between time when information is in the same column?
View Replies !
Select Date Ignoring Time
Im using a datetime fields eg. "2006-08-07 16:01:09" and I want to select dates... eg. "select * from mytable where mydate="2006-08-07" ignoring the time.
View Replies !
Trying To Select Rows With Max Date
This is my query: SELECT i_dest,i_rate, price_1, effective_from FROM Rates r ORDER BY i_dest; I have multiple entries for each "i_dest". Every entry does have a unique i_rate (the younger the entry the higher the i_rate since it's the tables primary key with auto-inc). Also every entry does have a unique effective_from datetime value. I would like to select all distinct i_dest definitions having either the highest i_rate value or the highest/youngest datetime in effective_from. I have tried something like this: SELECT i_dest,i_rate, price_1, effective_from FROM Rates r GROUP BY i_dest HAVING MAX(i_rate); Unfortunately this does not work, i.e. it does not result in only the rows with the highest i_rate. If I go like this: SELECT i_dest,max(i_rate), price_1, effective_from FROM Rates r GROUP BY i_dest; then I do see the highest i_rate BUT the price_1 and effective_from entries are those from other rows with a lower i_rate (and same i_dest obviously).
View Replies !
Select Rows On MAX(date)
My application inserts rows with a run_date - often all the other data will remain the same. I need to count only the rows with the latest run_date (and other WHERE clauses defined by the user). Table looks like: |rundate |vendor|users|vertical| |2009-02-01|blah |100 |chemical| |2009-02-02|blah |100 |chemical| So my query needs to return '1' as only the latest run_date should be included. I've played with MAX(run_date) but I don't know how to use it as part of a WHERE clause. e.g. SELECT vendor, COUNT(*) FROM results WHERE vendor = 'blah' AND MAX(rundate);
View Replies !
Select Rows With Latest Date For Each Species
I've got a table of species sightings with date and location. There may be several rows containing sightings of the same species (on different dates). Each row has an auto-increment id. need a query to make a list with one row per species, the row with the latest date for that species. The result must include the id for the row with the latest date per species. If there are multiple rows for the same species and latest date, it's ok to return all the rows but I'd prefer it return any one. The closest I have come is the following: SELECT id, species_name, max(date) from sightings group by species_name; This returns the right number of rows and the max date is correct, however the id is not the id of the rows containing the max date.
View Replies !
Selecting Rows While Using Values From These Rows In The Where Clause
I have a table which includes 3 columns: id: just an id name: a name parent: the id of the row which this row is a child of. In my example I want to select the rows with the id 5, it's parent, and the parent's parent. I found a syntax that workes in this case: SELECT `name`,`id` FROM `locations` WHERE `id`='5' or `id`=(SELECT `parent` FROM `locations` WHERE `id`='5') or `id`=(SELECT `parent` FROM `locations` WHERE `id`=(SELECT `parent` FROM `locations` WHERE `id`='5')) However my question now is if this syntax could be shortened while still working, and also if there is a way to allow for rows to be selected that do not have a parent( value 0 in my table) or that only have a parent but no grandparent.
View Replies !
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
Date-time Overlaps Another Time
had a time question in mysql... i have a start and end time for a meeting table and i am comparing whether a new meeting conflicts with another meeting in same room on the same date. i was wondering if i could check whether the 'date-time duration' in anyway overlaps another meetings date-time duration. visualized below... ............|____compareToThisMeeting__| .....|_________meetingNewOverlaps__________| i was doing something dumb before i realized i needed soemthing more complex (checking whether the start time of new meeting was 'BETWEEN' the start and end times of a meeting and whether the end time time is as well but i forgot the duration/middle value hehe). (using asp) and just returning booleans with a mySQL SELECT statement is what i am looking for like... //within a loop in asp for meetings on same date/room "SELECT '"+ year+"-"+month +"-"+day +" " + startHourString +":" + startMinuteString +":00''"+ " BETWEEN '"+ thisMeetingStart +"' AND '"+ thisMeetingEnd +"'"; (this doesnt cover the 'middle' overlap areas though)
View Replies !
Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row. I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.
View Replies !
Date Format And WHERE Clause
I have a question about a fiedl that I have in my table that has a date/time datatype. DOS is the date/time field I am trying to use this sql in Coldfusuion: SELECT * FROM Shows WHERE Shows.DOS = '#Form.GigDate#' ORDER BY DOS ASC I keep getting a datatype mismatch error. My date field has a Short Date format and I wondered what I am doing wrong in my sql and i believe it is in the WHERE clause. Do I need something around the DOS part of that sql?
View Replies !
Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The thing is, in my system, I have a set of users who have a 'quota' limiting their usage. They can create n number of tables and put m number of rows in it, but at the end, their 'usage' can not exceed, lets say, x MB. My problem is, how to I evaluate the space being used, given the fact that I can pull all data for a user by including a user ID in the where clause. In other words, I need to find size of the result set containing all user's rows. I am using mysql jdbc driver and not sure if there is any API that deals with the physical size of the resultset. resultset.getFetchSize() talks in terms of rows v/s the actual space they take.
View Replies !
Delete Rows From Multiple Tables (AND & WHERE Clause)
I have a database containing the folllowing tables; PUPIL id ... ROA pupil_id ... MESSAGE id pupil_id ... REPLY message_id ... How can I delete all the rows from all tables containing the same pupil id and all replies to messages from the same pupil? I managed it using AND clauses in WHERE but if there is no data in say the MESSAGE table then it doesn't work.
View Replies !
What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this: SELECT COUNT(*) AS exists FROM mytable WHERE ... Sometimes I just select the first row and check later in php how many rows have been returned: SELECT some_col FROM mytable WHERE ... LIMIT 1 But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example: SELECT id, name, (SELECT COUNT(*) FROM mytable WHERE ...) AS exists FROM othertable WHERE surname='xxx' Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.
View Replies !
Pagination By Date With Multiple Rows For Each Date
I'm not exactly sure what is the best method to use when trying to paginate my results. Currently I have 2 tables: entries ------- entryDate entryText images ------- entryDate imageName This is the query that I have been using to display all entries and their images on one page: Code: SELECT e.entryDate , e.entryText , i.imageName FROM entries AS e INNER JOIN images AS i ON e.entryDate = i.entryDate ORDER BY e.entryDate.........................
View Replies !
Select Clause
We have a recordset with the following structure id (int), mls (varchar) What we need to do is within a single sql call we need to show all records where the mls = '' and all unique records where mls!='' In other words, there will be multiple records with duplicate mls entries. We do not want to show duplicate enties for where the mls has been set, only where the mls has not been set. Any suggestions?
View Replies !
Select & Using In Clause
Is there a way to select all records where the record id isn't in another record's assignment field? Pertinent fields in the table are ID, Name, Country, Assignment. There are a few rows where the Assignment must occur manually (record 19 has an assignment of record 16, for instance), but otherwise the assignments are done randomly. Is there a way I can do a select and get all the record except the ones that have their record ID in another record's Assignment field? Something like Select all from CDLSS where ID not in Assignment?
View Replies !
SELECT Using IN Clause
for example, i'll do something like: select first_name FROM users WHERE user_ID IN (2,1,3) and i want the first name for user_ID 2 to come first, then 1, then 3 but instead, understandably so, it searchs 1, then 2, then 3, and i was severely hoping there was a way to preserve the order.
View Replies !
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 !
After Date And After Time
I would like to make a memo script that once you have entered the date and time and note in a memo intto the database the memo is displayed once both date and time have passed. I dont want the memo to disappear until it is told to. I have an idea how to make it disappear, but I dont know what the syntax is for the sql for AFTER the date has passsed and AFTER the time has passed.
View Replies !
Time AND Date
i want to store the time in my field typ time. i DON`T want to store date and time in one field. in the field i see the time with an admin tool, but when i want to put out the value in an ASP page i get time AND date .how can i configure the field typ to store ONLY the time ?
View Replies !
About Date And Time
i have created a table using the following code: ------------------------------------------------------------------------------ create table tt(name varchar(10), enddatetime timestamp, startdatetime timestamp, difference timestamps); ------------------------------------------------------------------------------ after i have created, i insert the following into it: ------------------------------------------------------------------------------ insert into tt values('bob',null,'2006-10-1 12:12:12',null); ------------------------------------------------------------------------------ and i want to find the difference between the enddatetime and the startdatetime. so i tried the following: ----------------------------------------------------------------------------- select enddatetime,startdatetime, sec_to_time(unix_timestamp(enddatetime)-unix_timestamp(startdatetime)) as difference from tt; ------------------------------------------------------------------------------ but the value came out from the difference column was xxxxx:xx:xx *(where x is an integer). as you can see, the year,month,date and the hour are all mix up together(xxxxx). so i was wondering whether my code or my variable was wrong?? and by the way, how can i show the difference in terms of seconds?? like converting the enddatetime and the startdatetime into seconds and substract them together?
View Replies !
Date Time
what I want to do is very simple however it seems I'm having trouble figuring out how :D I got a date_time field in a table. I want to retreive all the records that match yesteday. Something like: select * from table where datetimefield = now() - interval 1 day HOWEVER because it's a date_time field (and I can't split it in 2 fields for other reasons), the above query won't return anything. Seems I could do a select * from table where datetimefield between yesterday at 00:00:01 and yesterday at 23:59:59 but I can't find the functions for that.
View Replies !
Date & Time
i want to ast if i insert into table the value of now() in a datetime formated column does tha value it inserts if from my pc or from the server?
View Replies !
UTC Date/time
A data file I am trying to import has the date/time field in UTC format: YYYYMMDD HHMMSS+0. When I define my column field as datetime and import I only see zeros in all positions. Can MySQL import data that are in the UTC format and if so what data type or command should I use?
View Replies !
SQL Date Time
I've been having trouble trying to figure out the best way to select only the rows with timestamps within the last X amount of hours, days, weeks, months. So I guess my SQL statement in plain english would be: Give me all the rows that have a timestamp between NOW and X days/months ago and count how many times a row was submitted per hour/day/week/month within those rows. I'm pretty sure this is possible but I can't seem to wrap my brain around it.
View Replies !
IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS.. I'm trying to do this: insert into table2 (field1) select field1 from table1 where field1 not in (select field1 from table2) delete from table1 where field1 in (select field1 from table2) 1. Insert field1 from table1 into table2 if it doesn't already exist there... 2. Once moved, delete from table1.
View Replies !
Where Clause In Select Query
I have this query atm: SELECT postid, body, posttime, commentcount FROM nz_posts WHERE sections = ':7:' ORDER BY 'posttime' DESC LIMIT 0, 5 this works, but "sections" can have something like ":7: :8:", ":4: :7:", etc... all the numbers are between : and there can be one or many numbers (all diferent). I want to select all the rows that has :7: in the sections field.
View Replies !
Subquery In The SELECT Clause
I am having the problem in the sql statement SELECT name, (SELECT Sum(xxx) FROM myTable GROUP BY fld) as mySum , (mySum + 1)/2 as myVal FROM myTable1 This resulted in an error!! How can i use the 'mySum' in this query
View Replies !
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 !
SELECT DISTINCT With WHERE Clause
I'm using a SELECT DISTINCT to grab values from mysql, my question is, is it possible to get other field data from the statement, besides the DISTINCT data, example sql = "SELECT DISTINCT Value1, Value2 FROM SomeTable WHERE id="&whatever. can I only get data from Value1,Value2, or is there a way to add in other field names to get data from without them being part of the DISTINCT directive?
View Replies !
Sum Of Time In Multiple Rows
I have a table Code: TASKS{ started => time column, ended => time column } Now, for every task I do I make a new record (e.g.) saying I started at 11:00 and ended in 11:15 Now, I can find how long I worked PER row (e.g. in past example the following would return 15 min) Code: select subtime(ended, started) as time from tasks How do I sum all the rows, not just one?
View Replies !
Time And Date Fields
I have a form which insert data into a MYSQL table. In this MYSQL table I have a field called "DateEntered". Is it possible to have this field automatically updated with the current date when the form is submitted. What do I need to do in Mysql or on the html form?
View Replies !
Mysql Date Time
This is probably an easy one but i cannot seem to solve it, what I have is a database with a lastlogin datetime field in the form: 2005-01-17 00:14:30 I need to do a select on each entry to find the number of rows later than the date of the current row datetime, as I have users that log log in at different times. I tried something like in a loop but this does not work select count(*) as total from user_table where (NOW()>DATE_ADD(LastLogin, INTERVAL 0 DAY)) and iUserId != '$iUserId'
View Replies !
ORDER BY Date And Time
I am using php to extract variables from a MYSQL database that get loaded into a calendar application. Most everything works great. The only problem occurs when more than one event occurs on the same day. The event date and time are stored as separate variables in the database, thereby making it difficult to ORDER BY date and time. The result is that all of my events appear on the proper day, but they do not appear in chronological order. Is there a way to order by date and time? I currently have the following: Code: $SQL_Query = "SELECT eventID, eventDate, startTime, endTime, title, description, linkName, hyperlink FROM events ORDER BY eventDate";
View Replies !
Server Date Time
I have mysql server running in one machine. I have a query in my program 'SELECT CURRENT_TIME', it will retreive the system time. I installed my program in the client machine and it is connecting to the mysql server. If i execute the SELECT CURRENT_TIME, it is giving the client machine date and time. How can i retreive the server machine means on which mysql running, its date and time from the client machines.
View Replies !
Subtract Date And Time
is there a way that I can subtract date/time in MySQL? For example I have this dates and I want to know the number of hours between this dates: I want to perform 2006-12-05 22:00 - 2006-12-07 08:00. Can this be possible in a mysql query? $date1= '2006-12-05 22:00'; $date2= '2006-12-07 08:00';
View Replies !
Sort Date And Time
I have a simple question about sorting records based on seperate date and time fields. Take the following example query: SELECT some_id FROM datatable WHERE date >= '2007-01-01' and time >= '05:00:00' This returns all of the records with a date greater than 2007-01-01 and then reduces the records to only those with a time greater than 5:00. I want all records after 2007-01-01 5:00.
View Replies !
DATE Format :: How To Put Time?
I am using PHP myadmin to setup my mySQL database. The format I choose to store my date text field was DATETIME. How can I choose a different format for the DATE? All I want is a: 12-7-2007 format and don't want to put in a time but it's expecting the time as well.
View Replies !
Need To Include Time With The Date
I have a table called 'date_accessed' ... When I query that table for the date, how do I include the time along with it? Here is my query: SELECT user_agent, ipaddress, date_accessed FROM access_tracker;
View Replies !
Date/Time Format From CSV
I have a rather large csv file that includes many dates that I would like to import into MySQL. The problem that I am having is that the dates are DD/MM/YYYY HH:MM:SS, and MySQL does not accept them in its YYYY-MM-DD HH:MM:SS format. Any ideas how to convert this properly?
View Replies !
Date/Time To The 6th Microsecond
I am trying to import a field in a table on a server to which I am connected via ODBC. The field is in the following format 2008-08-11-00.00.00.000000 I would like for the format of the field locally to be the same but when I import it only goes to the seconds part of the format. Even when I try to import it as a text with 50 characters it doesn't help. Is there a way for the imported file to be in the exact same date/time format?
View Replies !
|
TRACKER
