Quick Date Subtraction Question
When I do the following, I get the number of days in difference between two dates however for the dates that have expired, I get the number of days back negated, ie -3 for example?
What I actually want is that if there has been an expiration, I want the result for a given row to be 0. Anyone got an idea how to?
// timestamp is datetime creation of record
// expires is date in future, ie 7, 14, 21 or 28 days in that increments
mysql> select day( expired ) - day( now() ) as days from polls;
Here is an example of the data returned,
+------+
| days |
+------+
| 7 |
| 7 |
| -3 |
| 7 |
| 7 |
| 21 |
| 7 |
| 7 |
| 14 |
| 21 |
| 7 |
| 21 |
| 7 |
| -3 |
| 21 |
| -3 |
| 7 |
| 7 |
| 7 |
| 14 |
| -3 |
+------+
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Subtraction Yields
If 'field1' is '0' and 'field2' is '1' and I perform the following query then I end up with some unexpected results: SELECT field1-field2 AS field_importance WHERE rec_id=12345 I would have expected 'field_importance' to simply be "-1" but instead it ends up being "18446744073709551615".
DATETIME Compare / Subtraction
I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds).
DATETIME Compare/subtraction
--0-930715088-1061086093=:22602 Content-Type: text/plain; charset=us-ascii I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds).
Quick Join
I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys. Now I want the querry result to look like this: Match ID | Winner Name | Loser Name. I did a join query: Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id How do I get the loser name?
Quick Fix 4 MySQL Pro
I will have a few instances like this and since I'm new to MySQL, perhaps a good example will help down the road. All I want to do is display a list of the first and last name of the names in my DB so they appear like so: John Smith Suzie Johnson Etc... first and last are two separate fields in the DB so I just need to join them together. The code I have now gives me this error: PHP Notice: Undefined index: first in e:etc... <?php // Request the names in db $result = mysql_query("SELECT artist.first, artist.last FROM artist"); if (!$result) { echo("No artists posted at this time"); exit(); code...
Quick Join
I have a quick SQL question: I have two tables Matches (m_id, m_winner_id, m_loser_id) and Users (u_id, u_name). m_winner and m_loser_id are foreign keys. Now I want the querry result to look like this: Match ID | Winner Name | Loser Name. I did a join query: Select matchs.id, users.name From matchs, users Where matchs.winner_id = users.id How do I get the loser name?
Quick PhpMyAdmin
I believe I got phpMyAdmin setup to work right, I used HTTP authentication, and I get a log in screen when I goto the url. How do I set up an account for that? GRANT ALL PRIVILEGES ON acct1_db.* TO 'Brian'@localhost IDENTIFIED BY 'password'; Is that right? I wish to host websites off this computer and have multiple users. Do I need to log in to the controluser account to do it? Or am I way off?
Quick Query
this one shoud be easy, but I just can't think of the best way (other than writing a php script to do it). I have a CMS and need to change one word in the description field for 64 rows. For example, where we now have 64 entries with "Bilbo" in the middle of the data, we need to change it to say "Frodo" instead. (Actually, I need to replace a misspelled javascript toLowerCase() call...darned non-programmers! They used toLowercase() - note the lower case C)
Quick Queries
Is it possible to use a regular expression or similar in a mySQL query, to make 'fuzzy' comparisons, or is LIKE the best we can hope for?
QUICK AND EASY ONE
im fairly new to php/mysql so please bear with me I am trying to upload a txt.file created from an excel spreadsheet using the following query: LOAD DATA INFILE 'path/file.txt' INTO TABLE my_table; im getting a syntax error! what could be wrong? are there any other attributes i should be adding to this query? i also tried uploading a csv version of the same file and get the same error.
Quick COUNT()
The MySQL documentation says: COUNT(DISTINCT expr,[expr...]) Returns a count of the number of different non-NULL values. How do i get it to INCLUDE null values? I am using COUNT with the DISTINCT bit as well, and i want the NULL rows to count as one as well.
Quick Tip: Switching Field On/off
Ever wanted to change a database field (typically TINYINT(1)) from 1 to 0 or from 0 to 1 depending on the current value? No need for two queries. I just thought of this 'clever' way to take care of it. Never saw that done this way before, and should have thought earlier about this. UPDATE articles SET published = ABS(published - 1) WHERE id = x
How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store data into it and, at the end, I dump all the date into 'production' table. Until now, I perform it following these steps: 1) I empty 'production' table 2) I make a SELECT of every rows of the 'wild card' table and an INSERT into 'production' table. 3) I empty 'wild card' table. But I've got problem: during SELECT+INSERT time, 'production' table is not full and there's a data lack.
How To Dump (quick) A Table Into Another One.
On MySQL, I've got a table I use as 'wild card'. I mean, I store data into it and, at the end, I dump all the date into 'production' table. Until now, I perform it following these steps: 1) I empty 'production' table 2) I make a SELECT of every rows of the 'wild card' table and an INSERT into 'production' table. 3) I empty 'wild card' table. But I've got problem: during SELECT+INSERT time, 'production' table is not full and there's a data lack.
Quick Ones To Speed Up Access
------=_NextPart_000_0032_01C35FC2.62884DE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am querying from a table containing more than 40,000 records. Earlier = when the records were 10,000 it was taking 9 sec and now after 1 year = and 40,000 records its taking 30 sec. Code is the same.=20 I am pretty sure that it has something to do with database only. No body = can think of spending 30 secs for retrieving values from tables unless = it's very huge in the sense of millions of records. =20 I don know whether I have to modify my database or do some sort of = restructuring or reindexing so as to make it fast enough. Is there some methods or optmization which can be applied to this = database which hasn't been touched since design to enhance the spped.=20 If there are some quick ones but valued alottttttt please let me know.
Dump (quick) A Table Into Another One
On MySQL, I've got a table I use as 'wild card'. I mean, I store data into it and, at the end, I dump all the date into 'production' table. Until now, I perform it following these steps: 1) I empty 'production' table 2) I make a SELECT of every rows of the 'wild card' table and an INSERT into 'production' table. 3) I empty 'wild card' table. But I've got problem: during SELECT+INSERT time, 'production' table is not full and there's a data lack. I'm looking for a faster way. Does anybody know it? Thank you very much. PS: I was told about 'mysqldump', but I do not know if it's faster than mine.
Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes. I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?
Quick Question - Id Mediumint(8) - Does The (8) Matter?
just a quick question really.. Just setting up 1 of my tables and just wondering if it matters if i set my id to mediumint(8) but i only ever get to 6, will this effect anything in anyway? Same goes for - address varchar(50) and say that no address ever reachs 50?
Quick & Easy MySQL Sync?
Does anyone know of any quick and easy way to sync a local development mysql server to a remote server and back. I'd like to be able to optionally sync either direction?
Quick Mysql Auto_increment Question
i noticed that after adding some test data into my table the autoincrement is working fine. but after i delete them, even though the table is empty again the new data i put in will have primary key thats in auto_increment as if the old data was there. for example i added in 2 rows. now i have an id 1 and id 2. i delete them and the table has no rows. but now i add another two rows after wards and they get assigned id 3 and id 4. is there anyway for me to make the counting start over??? thanks for any replies or thoughts on this.
Quick Question About Reseting Demo Database?
I would like to set up a cron job to reset my demo database on a regular basis. Is there a way to export an sql file via phpmyadmin, and run it as a whole via the PHP mysql commands? The only way I can see to do it, I have to split up the exported sql from phpmyadmin into individual queries. I'd like to just process the entire .sql file at once, as it would make maintenence much easier.
Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria? $query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";
Passing Date Via Form For Mysql Date Type
I've created a database for tracking our paper inventory. Basically when an individual takes paper, or envelopes the quantity is entered into the database, along with some other items. This all works great. I also have two fields that use the "Date" and "Time" types for holding the date and time of the initial transaction. I've created a seperate php script that we will use for our "end of month" reports. The script goes through and adds up the cost of each "purchase" between a specified time frame (1 may thru 31 may for example). This script works for me as long as I perform my query with my condition formated as such:
Output Multiple Records From Start Date To End Date?
Firstly I'm using MySQL 5 on Win2003. I have a DB table that has multple columns, but the 2 columns I am interested in are DATE_START and DATE_END. So I'll have many records in the DB and the values in these fields will be anything like: DATE_START=2006-10-23 DATE_END=2006-10-26 Or if its a 1 day thing it will be sumin like: DATE_START=2006-10-23 DATE_END=2006-10-23 Is there anyway I can output multiple records from the DB for that one record between those dates? For example, the first record above would output: TITLE | SUMMARY | 2006-10-23 TITLE | SUMMARY | 2006-10-24 TITLE | SUMMARY | 2006-10-25 TITLE | SUMMARY | 2006-10-26 So basically I get a loop of all dates between the start and end date? Before anyone asks, no I can't loop through this in the code! I have a calendar, and all the dates are created and I just need to populate these dates with the records... and I won't go into detail, but without querying every day, this is the best way by far to do it... just need to know if it can be done?
Latest Date Entry + Future Date
My requirement is data to be added to database depending on dates. Like If the user wants to add data for the next 10 days,the data should be added with a corresponding column containing the next 10 days' dates. Then if he logs in tomorrow and wishes to add data for another 100 days,it should find the last date added and then add data with the next 100 dates starting from that date. Also I would want to display these dates to the user.So can you suggest which type to be used for database field and also the date functions(mysql as well as php) to be achieved. Let me put it more clearly. "select the last date(which might be future dates) entered in the database"; If the date is Oct10 and the user wants to add entries for Oct 11,12 13 "insert into table values('data','oct11')" ...... ...... likewise for Oct12 and 13 The format I need is just year,month and day.No time is required.
Date/time Field Update Only Date
I have a field(tmMessage) in a table, it's a date/time struct. it looks like "2003-09-21 15:52:35" All I want to do is change the date in this field. I want the time to stay the same.
Calculate Days Between Current Date And Date
I need to calculate the days between the current day and the date stored in a date column. I saw the datediff() command, but that only works with mysql 4.1.1. I am running 3.23.58. How can I do this?
Date/time Field Update Only Date
I have a field(tmMessage) in a table, it's a date/time struct. it looks like "2003-09-21 15:52:35" All I want to do is change the date in this field. I want the time to stay the same.
Quick "WHERE"
Part of a WHERE criteria I'm trying to construct needs to return only those records where a date field in my adkey table contains a date older than a user-specified date (submitted as variable {sRemoveDate} from a web-page form). The following works: CODEWHERE (adkey.D_RemoveDate < '{sRemoveDate}' Or adkey.D_RemoveDate is null)
Updating A Date From Another Date In The Same Table.
I'm having trouble coming up with the correct SQL syntax to update a date field from another date field in the same table. Both fields are DATE types. I want to add 2 months to the date in the final_date field and update the keeplive_exp field with that total. So if final_date has 2007-05-04, keeplive_exp needs to update to 2007-07-04.
Date Range Without Date Column
Suppose I have a table that says on which days I should eat certain foods: +----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+ | Id | Name | Qty | Mon | Tue | Wed | Thu | Fri | Sat | Sun | +----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+ | 1 | Carrots | 3 | T | T | T | T | T | T | T | | 2 | Cauliflower | 1 | T | T | T | T | T | T | T | | 3 | Broccoli | 2 | T | T | T | T | T | T | T | | 4 | Peas | 10 | F | F | F | F | F | T | T | | 5 | Potatoes | 1 | T | T | T | F | F | F | F | +----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+ And suppose this is the calendar: August 2006 MonTueWedThuFriSatSun 123456 78910111213 14151617181920 21222324252627 28293031 For the days August 8 through August 10 inclusive, how can I figure out how much food I should eat with an SQL statement? The answer should be: 9 carrots 3 cauliflowers 6 broccoli 2 potatoes
Date Query (how Do I Get Everything With A Date In 7 Days?)
the goal is a reminder email to people who have a workshop coming up in 7 days. So, workshop date is 2006-05-25. The reminder email should go out on 2006-05-18 I guess mysql query returns all records where (Date column - 7 days) = current date. Right? But what does teh QUERY look like? And how does it accomodate if today is 2006-05-28 and the upcoming workshop is 2006-06-04 (so month changes over the 7 day period).
Convert Date String Into Date
I have created a database using data in a text file. Unfortunately the date column in the text file returns the date as day month date (e.g. Mon Nov 13). How do I convert these dates into the mysql format of YYYY_mm_dd ?
Date Range From A Single Date
I have a football fixtures table with a date of the fixture stored with the fixture. I want to display this fixture on my website for (say) 3 days before the actual fixture date and (say) 3 days after the actual fixture date. Is this possible from within a single query? I think this might have something to do with the DATEADD/DATESUB functions, but I can't work out how to use these in the WHERE clause of my query.
Date Range On Date Field
I have a table that has birthday dates stored as a DATE field. How would I get all the rows returned based on a min years old and a max years old...Let's say... My form has.... Display Rows by birthdate in years.... Show between (23 years old) AND (36 years old) from the current month/day/year
Select From Date To Date
i need a query that selects from date to date in the end the query will be weekly top 5 tutorials and i would like it every sunday to automaticly change the date in the query so i never have to do it.
Getting Date Using Date Format
PHP Code: SELECT *, DATE_FORMAT(date, '%w-%M-%Y') AS datevalue FROM members where username = '$_SESSION[username]' I have that code to select from my database, it all works fine and I can view all entries. But the date still prints in the format it was stored.
INSERT Non-MySQL Date Into MySQL Date Field
I have a non-MySQL database that has a date value that is an integer value (4 byte unsigned integer representing the number of days since 1904, ie 01/01/2000 = 35063) Do I need to re-format this to use in an SQL to INSERT it into a MySQL table? If I do need to reformat it, what is the best format to use.
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.
Max (date)
I'm new to SQL and have a problem withe a query. Table: empstatus (employee status): - id (foreign key to employee table): int, - efdate (effective date of employment status changes): date, - substatus (foreign key to empstatusdefs - employee status definitions): int empstatusdefs is a dictionary fo status definitions, eg: ACTIVE, SUSPENDED, etc. What I need to do, is to find all employees with current employee status and that status has to be ACTIVE. My first attempt was:
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?
Date
I have split the date field in three. Basically I have set the date as three int fields so that I will make it easier for the user to insert dates in the database. Because of this I have created another problem. I want to make a query in the database that is date related. I have used the following query SELECT * FROM main WHERE DATE (CONCAT(Year,Month,Day)) < '2007-10-10' However this is not returning any information back.
DATE()
I need help with a sql query where i have a DateStamp column with values like 5/10/2005, 4/11/2006 etc. Now since todays date is 09/19/2005, I want to pull only those values that have year as 2005. Once the year changes to 2006 I want to pull values for 2006 only. So basically depending on the day the report is being pulled i need the appropriate values. Here's the query: mysql="SELECT DateStamp FROM TimeTracking WHERE DateStamp LIKE '%Year()%'"
Date
Im having a Problem querying a date field in Mysql. I need to return all recs that are between the Current Date and the date field ,if the value date field is between 1 and seven days.
Sql Date
have the following tables book bookid slotid date carid 1 1 2006/06/02 1 1 2 2006/06/02... 3 mottimes id mott slotid 1 9:00 1 2 10:00 2 3 11:00 3 am making a query that lists All mot times in a table and puts the car that is having an mot in the correct slot. so far... motsql1 = "SELECT mot.mott, mot.motslot, car.reg, book.bookdate "_ & "FROM mottimes AS mot "_ & "LEFT JOIN book ON mot.motslot = book.slotid "_ & "LEFT JOIN car ON book.carid = car.carid "_ & "WHERE dayid =1 AND bayid =1 AND book.bookdate =" & [motdate]
|
TRACKER
