Comparing Datetime And Time
I need to compare two values : the first one is in DATETIME format and the second one in TIME format. If I compare the two values with an usual operator, will MySQL extract the "time" part of the DATETIME and compare it to the TIME value?
If not, what function can I use to extract the "time" value? I have noticed the existence of TIME(expr) function, that extracts the TIME value from 'expr'... but it works only if 'expr' is a string, not a DATETIME.
View Complete Forum Thread with Replies
Related Forum Messages:
Comparing DateTime Field To Now()
I am trying to have a select query which compares a Datetime field to the current date/time, Now(). Basically I want something to this effect: SELECT Name FROM Lists WHERE expiration > 'Now()' Expiration is a datetime field. The problem is It returns every name on the list except for ones with a value of 0000-00-00 00:00:00 whether or not the date has passed already. I have tried many other things including but not limited to SELECT Name FROM Lists WHERE datediff(expiration,'Now()') > 0 SELECT Name FROM Lists WHERE timediff(expiration,'Now()') > 0 SELECT Name FROM Lists WHERE datediff('expiration','Now()') > '0' and many other variations on those. The above 3 just returned a null recordset. How can I make this work?
View Replies !
Comparing A Datetime Column
I am trying to write a query that will delete all rows that are older than a specified date. The problem is that I am fairly new to mysql and that I am not quite sure about how I should compare something in the format yyyy-mm-dd hh:mm:ss. Would converting that date format using UNIX_TIMESTAMP(), subtracting that from the current unix timestamp and then comparing be a good way to approach this problem? Or are there other functions that may be easier to use? Also, on a curious note.. how portable are these built-in mysql functions to other SQL software packages?
View Replies !
Comparing DATETIME Field With Date...
I'd like to make query which compares two dates - one from database, ang second in php (date("Y-m-d H:i:s")). I'd like to make query which gives me back those rows where the date from db is more than three days older than current date.
View Replies !
Comparing Dates Y, M, D , Time In Sql Query
ok.. ive finally got my nice little calender setup to output my dates now how do i query my database for example ive got 2 variables $startdate = 2006-1-1 00:00:01 $enddate = 2006-1-1 23:59:59 these represent the 1st and last second of the first of january 2006 i have a cell in my table (table called challenges) ( cell called time) that contains a date in the same format for each entry how do i get all the entries between the start time and the end time.. can i use less than < and more than > as these are not really integar values the column type is datetime but there is also another column of type datetime
View Replies !
DateTime Without Time
I have a difficult case and need your advice: For managing events in my online application I need to be able to input the start and end. They can be inserted either as a date only or as a date and time both. And I save this data into the DATETIME format. The problem is, if only a date is inserted, MYSQL will put the missing time information automatically as '00:00:00' but I don't want this. Because it will display the time as '00:00:00' which is wrong. Is there no way to save into DATETIME format without the time?
View Replies !
Elapsed Time Between 2 DateTime Values
MySQL Version 4.0.20 on a Linux server. How does one get the elapsed time between (2) DateTime values? I need the answer to the nearest minute. Is upgrading to Ver 5 with its more robust date/time functions the only solution? You can directly subtract 2 DateTime values and a long integer results. What is that number?
View Replies !
Datetime Field Updateing Time Only
How would I update the time only on a datetime field? The filed is: "2008-09-14 12:06:00" and I want to change it to "2008-09-14 12:16:00". If I use  update field_name set field_name = TIMEDIFF('2008-09-14 12:06:00', '2008-09-14 12:16:00');Â
View Replies !
Update Time Ony In Datetime Field
How do I update the time portion in a datetime type field? There are several rows containing various times and I want to make them all the same: 11:30:00. I have tried doing the following w/o success: update field set field = "date(field) 02:02:00"
View Replies !
Update Just Time In Datetime Column
I have a datetime column called foo, it currently has dates in it with all times set to "00:00:00". I would like sql to update all the rows and set the time to "15:15:00" but leave the dates as they are today.
View Replies !
Datetime :: 12 And 24 Hours Time Format
I am using webservice to download data from mySQL to SQL Server. I noticed in sql server datetime format makes the hour 12 to 00. for example 2008-06-12 00:40:47.000 This is really 2008-06-12 12:40:47.000 My question is how does mySQL treats 24 hours format. with 12 or 0 for the hour "12".
View Replies !
Function To Convert From DateTime To Relative Time
I have a DATETIME column and I looking to write a function which does the following: Takes in the DATETIME and compares it against the current time, and returns a string in any of the forms below, depending on the value of the DATETIME: 'You updated your settings 4 seconds ago.' 'You updated your settings 2 minutes ago.' 'You updated your settings 6 hours ago.' 'You updated your settings 12 days ago.' But its smart and automatically scales from seconds, to minutes, to hours, to days, rounding either up or down.
View Replies !
Creating Datetime Data Object From Date And Time Objects
I have date and time objects that correspond to eachother. I want to add a certain amount of minutes and hours to them and get the resulting date and time. I was planning on creating a datetime datatype from the two and then using the date_add() function and then convert back; however, I do not know how to create a datetime object from two seperate date and time types and then back to seperate types, or whether it is even possible.
View Replies !
Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this: WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00")) Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like: Day Min Max 2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59 2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68 where at this point i`m getting min=40 and max=68
View Replies !
Compare Datetime Field With Todays Datetime
How do I compare a datetime field in the database with todays datetime... Is it: if rs("fieldname")<>getdate() then .... else (gotto set a cookie here) end if Somebody suggested I use cdate...what is tht for..is it to make sure they are both in the same format or something.. if so how do I use it?
View Replies !
Comparing Age
So the birthdays in my MySQL table are in a column called DOB and in the format of say: March 8 1985. Now i have my own PHP function that will convert that to a nice numerical value of age relative to today, which is perfect. it is called getAge(). However, i can't seem to be able to parse an SQL query with that function inside...is there any way to parse my own PHP functions inside an SQL query?
View Replies !
Comparing
I'm currently working with two tables; A scheduling table and a document table. Basically I'm trying to make a query that says this -- "give me everything from this month and day in the Document table that's not in the Schedule"
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 !
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
Comparing Versions Of Sql
I'm taking up a new position and have never used sql before although use and code in lots of other languages. When doing a bit of survey I find many many versions of commercial and public versions of sql. Can anyone tell me if the syntax is generally similar or are they completely different. If I invest time learning mysql on XP or Linux will that be useful if I end up using something like Oracle later on? The only one I would avoid is MS.like things that work occasionally.
View Replies !
Comparing Two Tables
I have these tables: table02 Id | tab01_field 1 | 00258 2 | 00041 Code: table01 Id | tab01_field 1 | 00258 2 | 00123 3 | 00825 4 | 00041 5 | 00005 table02 Id | tab01_field 1 | 00258 2 | 00041 When run a query to see the common records in both tables it returns a right result select table01.tab01_campo from table01,table02 where table01.tab01_field=table02.tab01_field Code: 00258 00041 But when I try to see the the uncommon records it returns select tab01_campo from table01,table02 where table01.tab01_field<>table02.tab01_field Code: 00258 00123 00123 00825 00825 00041 00005 00005 You can see, it returns the uncommon records twice, but also returs the common records. How could make a query tah returns only the uncommon records? Code: 00123 00825 00005
View Replies !
Comparing Times
I'm creating a voting system, and want to prevent people from cheating. Therefore I'm logging their IP, and the time they voted, and what I'm planning to do is have a script which will delete any records which have been in the database for say 24 hours. To get the length of time I was gonne use: HOUR(TIMEDIFF(NOW(),time))
View Replies !
Comparing Dates?
i have an 8 digit date string that i want to compare to a column of type 'date' in a MySQL db. the 8 digit string is in the format 'CCYYMMDD'. the question is: do i need to put it into the 'CCYY-MM-DD' format in order to get correct output from the datediff function, or can i simply leave it how it is?
View Replies !
Data Comparing
i'm writting a program that uses my sql to compare a date time feild to a date that i have to count call on one date the statment looks like this: SELECT Count(*) FROM wab_answers WHERE wabScreen=1 And DateDiff(answerTime,'" & curDate & "')=0; where curDate is string containing the date that i would like to compare. I'm not to sure on the correct way to do this. Just need a push in the right direction.
View Replies !
Comparing Differences
I want to find the fields that are in table A but not in table B. If I have: table A: id, name, scoreA table B: id, name, scoreB I want to find the scores that are in table A but not in B, by example: table a: 1,john,12|2,mike,14|3,neal,17 table b: 1,hellen,14|2,nolhan,12|3,vicky,10 After make the query: Score in A that are not in B: 17
View Replies !
Comparing Columns Using LIKE
I can write "column1 LIKE column2" to compare two columns in a fuzzy way, but what about "%" to indicate what should be compared? column2 contains a list (separated by spaces) of the single values that can be found in column1, so I need to use %value%. I've already validated the values, and they are always a single word, so there's no risk using LIKE per se. Any more elegant way to do this without having to resort to a PHP loop?
View Replies !
Comparing Two Dates?
I am trying to compare dates in MySQL, but one of my dates from the curdate()-1 method, returns a string that looks like this 20081210 curdate() method returns '2008-12-11' Both are not dates, so they will not compare. Here is my query: SELECT Order_Number FROM <TableName> WHERE date(Order_Date) BETWEEN curdate() AND curdate() -7;
View Replies !
Comparing Strings
I can't work out but i'll try to present it simply here. Assume the table "Names". FirstName, SecondName Robert Smith John Jones Richard Robertson Mike Richardson Ian Stevenson I need to write a query where by it selects all records where the First Name appears as part of any second name. the output should be Robert Smith Richard Robertson Now, I know I can do SELECT FirstName, LastName FROM Names WHERE LastName LIKE '%Richard%'; This will find all records where Richard appears as part of a last name. But I want it to work for any first name not just a specific one. i basicly need the last line to be something int he order of WHERE LastName LIKE '%FirstName%'; only a version that works.
View Replies !
Comparing Within A Column
i have a table 'packets' with the following structure : ip_src varchar(20),mac_source varchar(20) .now i want to list all those ip_src which would have more than one mac_source. how do i go about doing this?
View Replies !
Comparing String To Int
I would like to know what is the performance behavior in reading an writing string and int in several database tables. What i should use in terms of performance, int or string?
View Replies !
Comparing Dates
I have a problem with me. I am describing the whole situation. I have a table, the structure of the table is as follows: Table Name: Announcement FieldName Data Type(Size) Announcement Varchar(50) St_Hr Decimal(10,0) St_Min Decimal(10,0) St_Day Decimal(10,0) St_Mon Decimal(10,0) St_Year Decimal(10,0) End_Hr Decimal(10,0) End_Min Decimal(10,0) End_Day Decimal(10,0) End_Mon Decimal(10,0) End_Year Decimal(10,0) The data stored is: Test Announcement,10,20,6,10,2005,11,0,8,10,2005 Now, I have to write a query to retrive announcements after filtering them as per the mentioned Starting and Ending Date & Time. The condition should use the local date and time.
View Replies !
Comparing Two Fields With LIKE
Here's the sql: SELECT * FROM boxer.lpars a, boxer.libraries b WHERE b.lpars LIKE '%a.lpar%' AND a.deleted = 0 ORDER BY node, lpar b.lpars will commonly look like: "WIN1#WIN2#WIN3#WIN4#" a.lpar will commonly look like: "WIN1" When I run this query I get zero matches. What do I need to do to match fields using LIKE?
View Replies !
Comparing Two Table
I have two tables: CREATE TABLE `FootballerScore` ( `footballerID` mediumint(4) NOT NULL default '0', `GameWeekID` mediumint(4) NOT NULL default '0', `GameRuleID` mediumint(4) NOT NULL default '0', `Goals` mediumint(4) NOT NULL default '0', `Assists` mediumint(4) NOT NULL default '0', `CleanSheets` mediumint(4) NOT NULL default '0', `YellCards` mediumint(4) NOT NULL default '0', `RedCards` mediumint(4) NOT NULL default '0', `WeeklyScore` mediumint(4) NOT NULL default '0', `AccScore` mediumint(6) NOT NULL default '0', PRIMARY KEY (`footballerID`,`GameWeekID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `footballers` ( `footballerID` mediumint(15) NOT NULL default '0', `fname` varchar(15) collate latin1_general_ci NOT NULL default '', `sname` varchar(15) collate latin1_general_ci NOT NULL default '', `value` decimal(15,1) NOT NULL default '0.0', `PremTeamID` mediumint(15) NOT NULL default '0', `position` varchar(15) collate latin1_general_ci NOT NULL default '', `Importance` varchar(4) collate latin1_general_ci NOT NULL default '', PRIMARY KEY (`footballerID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; The footballers table basically has a list of all the players in the premiership and the footballerscore table has a list of the player who played in a given week. For any one week I need to find out who DIDNT play. I can do this by comparing all the footballerIDs in both tables and the difference is the group I need. However I'm not sure how to put this into an SQL statement.
View Replies !
Comparing A Csv To A Db Table
How would I go about comparing values in a CSV file to a table in the database, and then removing any rows where that value (an email address) matches? Is this something I can do in phpMySQL or will I need to create a separate PHP script or something? I maintain a mailing list using something called ListMessenger. It's a great web application, but the one major drawback is that it doesn't have a way to handle bounces. Over the course of 5-6 years, I'm now up to around 800-900 bounced emails every time I send out a newsletter. I'd really like to clean up the list and I was able to extract all the bounced address from my email client. I'm just not sure how to do it. I actually have an idea of what needs to be done... it's more the actual code to make it happen that I'm not sure of. I'm thinking it would be something like this: 1. Open csv file and read values into an array 2. Run a SQL statement that removes any rows from the "users" table if the "email_address" field matches any of the addresses in the array. Any help would be greatly appreciated. I can get into more specifics as far as the table/column names and stuff,
View Replies !
Comparing Tables
I have a query that produces a list of names of people who have completed a certain course. I want to be able to run a report which can show who has not completed this course. The code I have attached below just returns the same result as those who have sat the course....
View Replies !
Comparing Timestamps
My table keeps track of the last time a program was run and how often that program is supposed to be run. I want to retrieve all the rows where the program is scheduled to run again: ... WHERE NOW() > (last_runtime + run_frequency) last_runtime and run_frequency are both TIMESTAMP field types Is there a way do this, or do I need to convert these fields into something other?
View Replies !
Comparing Hours In One Single Day
I have to write a program using PHP5 and mysql 5.0.51b thats found on the WAMP 2.2 The dilemma is the following: I have 5 meeting rooms each one named c1,c2,c3 etc How do make a table where I can reserve a room at a certain date and certain time? This is the table i have so far: ....
View Replies !
Comparing A Field With A String
I would like to run a select statement against a table, with a where condition that a field (less all it's non-alphanumeric chars) = a string. Ordering is important. For example the following should all evalute true, '1 2 3' = '123' '1"2£3' = '123' '1 23' = '123' '(abcde)' = 'abcde' where as these should evaluate false, '123' != '321' '1a23' != '123' 'abc' != abcd' I have a sinking feeling I need regex for this, which is unfortunate as I know none.
View Replies !
Comparing Data From 3 Tables
I am trying to wrap my head around the sql statement needed to get the data I need by comparing three tables. I have three tables: authors, submissions, and ratings. I need to see all the submissions that a specific author has not rated. In other words, I compare the submissions table to the ratings table and find instances where there is a submission but not a rating from a particular author (signified by the row rater_id) I've tried different combinations of INNER and OUTER joins, unions, subqueries... and nothing is working for me.
View Replies !
Comparing Data In 2 Tables
I have a table of users which is updated once a week by the client sending me a spreadsheet, and me uploading the CSV to my scripts which then import the data into the user table. This all works fine. However instead of sending me incremental data that can be just imported, the client has now taken to sending me a complete data list. The problem with this is that I cannot just empty the user table and re-populate with the new data as I will lose the primary key values which I use elsewhere. My solution to this was to create a new table for importing with the same structure as my user table, import the csv to there, then somehow compare the tables and add the rows missing from the import table into the user table. My question then, is this possible using a SQL query. Can I compare 2 tables, and add missing data from 1 into the other as new records? I could code a software solution, but with around 30k records in the user table I think a SQL query would be the best approach.
View Replies !
Comparing Partial Strings
Suppose I have a varchar field "A" in my table that has a value '120 g' I have a variable $weight=120; Now I want to run a query which compares $weight to 120 of field A Can I do that? I think I will have to extract the number from 120 G and then do the comparsion.or something like that/
View Replies !
Comparing Tables In Same Database
I have two tables on the same database: hr_harassment (everybody who has already entered into a training class) fields: lname, fname, class hr_employee_list (everybody who needs to enter into a training class) fields: Lname, Fname I need to compare the two tables to figure out who has not yet signed up for the class. What would be the easiest way to do this? I am using PHP to program the page. I looked online and came up with this: SELECT * FROM hr_employee_list AND NOT EXISTS (SELECT * FROM hr_harrassment AND hr_employee_list.Lname = hr_harrassment.lname AND hr_employee_list.Fname = hr_harrassment.fname) But my browser tells me this: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in monitor.php on line 270
View Replies !
Comparing Tables With Mysql
I have 2 tables with the same format (same columns). Table 'a' is a subset of table' b'. I.e. in an ideal situation all entries in table 'a' should exist in table 'b'. I can uniquely identify an entry using 3 fields (no SQL key is setup) I need to write an SQL query (or a few queries) that will compare the 2 tables and output: Records that are in A but not in B Records that exists on both (i.e. key match) but are different (any other value but the key is different).
View Replies !
Comparing Multiple ID Fields
I have an advert table which contains three category ID fields (catid, catid2, catid3). These catid fields match up with the catid field in the category table. I want to run a query that returns any row that contains any mention of a specific category. SELECT * FROM advert, category WHERE advert.catid = category.catid AND advert.catid = '3' The above query works fine for the first advert.catid field, but once I introduce...: OR advert.catid2 = '3' ... the results start getting strange. The same row is returned 9 times.
View Replies !
Comparing Dates And Choosing
if( date_add(cur_date(), interval 30 day)>='$thisDate','$thisDate',date_add(cur_date(),interval 30 day) So: if the current date plus 30 days is larger then thisDate, use thisDate else use the current date plus 30 days.
View Replies !
Comparing Entire Row And Inserting New Ones
I have a transaction table (T1) that is refreshed with transactions every day where I have bunch of information but the key fields are; identifier, cost, retail, date I have a limitation on T1 that I cannot have identifier as primary key since there are many transaction with same identifier, it is being sold multiple times. I have a second table (T2) where I keep price updates. T2 has; identifier(primary key), cost, retail, date I am trying to find out if cost or retail is changing (my distributors have flexibility to change both in certain limits). I want to update the T2 by inserting a new line when either cost or retail changes with timestamp so I can see trends on both.
View Replies !
Comparing Successive Rows
I'm having a table with the following (simplified) columns: id (AUTO_INCREMENT), computer_id(INT), time (INT). Every few seconds different computers are inserting a row with time being the time when this happened. Now I would like to generate a statistic that shows per computer when it happened that two insert operations were more than x seconds apart. This way I can e.g. see when a computer has had a little too much lag time. If I only have one computer in my table this is quite straight forward: SELECT t2.time-t1.time, FROM mytable t1 INNER JOIN mytable t2 ON t1.id=t2.id-1 AND t1.time<t2.time-x; This quiery is based on the assumption that the IDs of two following entries from a computer are in order and e.g. 2 and 3 or 9 and 10. If however I have a few computers in the table the IDs for one computer aren't following each other anymore and could be 9, 17, 22, 23, 31 ... I read something about have a sort of "on-the-fly" incremental id per computer using a variable: SET @counter=0;SET @counter2=0; SELECT t2.time-t1.time FROM (SELECT @counter:=@counter+1 as counter, time FROM mytable WHERE computer_id=1) AS t1 INNER JOIN (SELECT @counter2:=@counter2+1 as counter, time from mytable WHERE computer_id=1) AS t2 ON t1.counter=t2.counter-1 and t1.time<t2.time-x; The sad news is that this query never returns, at least I didn't see it return. Generally I would say that having two sub-queries in there isn't the best thing to do, but I didn't know how to do it. I'm sure there is a way to do this without having a single table for each computer, but I just can't figure out how to do this. Anybody with an idea?
View Replies !
Comparing String As Date
A client is using mysql 3.23 as database. He wants me to make a report where the dates are store in varchar columns. How do i convert string columns to date so i can made the ">" and "<" right??
View Replies !
|
TRACKER
