Selecting Based On Time Range
I'm writing a series of queries on a transactions table. I'd like to find all transactions from:
Today
Yesterday
Last Week
Last Month
This Year
The table contains a datetime field called txn_timestamp. What is the appropriate code to filter the table for each time window?
SELECT transaction_id FROM transactions WHERE <?>
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting A Range Across A Range Of Fields
I am trying to gather stats from various categories. Each "item" can fit in as many as 8 categories and I have 25 categories to get stats on, which each have a unique numeric ID. What I am using now is VERY processor intensive, so I am wondering if there is some way I can put everything into the SQL statement instead of running it through a loop as shown below: $category_stats = array(); foreach ($categories as $k=>$v) { $query_result = mysql_query('SELECT SUM(stats) FROM items_table WHERE '.$v.' IN(catid0,catid1,catid2,catid3,catid4,catid5,catid6,catid7)'); $result = array(); $result = mysql_fetch_row($query_result); mysql_free_result($query_result); $category_stats[$k]['stats'] = $result[0]; } The $categories variable is just an array of 25 numbers, each corresponding to a category's unique ID. The stats field is an integer that is incremented on each stat increase.
View Replies !
Calculating A % Based On Date Range
I have a table called TransResult and I have 2 data fields in it. I want to get the percentages for the 2 possiable values in TransResult and I want this done by date, so I can go backwards to compare what was done in the past months to what is done in current month. This is what i have come up with so far, however my _total is giving me ALL records in the table not this months records, so my percentages are off, aside from the _total the values it is calculating are correct. SELECT TransResult, COUNT(*) AS HowMany, (COUNT(*) / _total ) * 100 AS Percent FROM tbltranslog, (SELECT COUNT(*) AS _total FROM tbltranslog) AS myTotal WHERE MONTH(Date) = MONTH(NOW()) +0 GROUP BY transresult
View Replies !
Showing Results Based On A Date Range
I have a calendar application, which stores dates as characters in its own column, ( was designed externally and using a now defunct openforge project) So y = year m = month and d = day. So the result would be ....
View Replies !
Selecting Range Of Numbers.
how to select a table of natural numbers in a given range. Is is possible to do something like this: select list(0, 3); to obtain: +------------+ | list(0, 3) | +------------+ | 0 | | 1 | | 2 | +------------+ or something similar?
View Replies !
Selecting A Range Of Columns
is there a way of selecting a range of columns e.g. telling the query to select col1, then every column between col10 and col100, (thus ignoring 2-9) without having to write each colum into the select statement?
View Replies !
Selecting A Date Range Using CURDATE()
When a user selects a page with the recordset I want it to list dates that fall into a certain range, related to CURDATE(). The range fields are start_date and end_date, which are both DATETIME column types. Using BETWEEN won't work because it won't list a date that starts BEFORE CURDATE() e.g. If CURDATE() is 2005-08-10 Then an event that has a start_date of 2005-08-06 and an end_date of 2005-08-26 won't show in the list, even though the date range does fall into the CURDATE() of 2005-08-10.
View Replies !
Selecting A Entry Within A Date Range
I have a MySQL database with 5 columns called Name, Model, Colour, Date_From, Date_To. Basically I want the user to specify the name and a date and want it to return the corresponding entry whose matches the name and within Date_From and Date_To. I wanted to use the date format dd-mm-yy so ive used VARCHAR. This is what ive done of which the date range doesnt work. SELECT Name, Model, Colour, Date, Date_From, Date_To FROM Cars WHERE Name='$Name' AND '$Date BETWEEN Date_To AND Date_From'
View Replies !
Selecting Another Column Based On Max()
I've got a funky query I'm trying to set up. I've got this poll answers database, and each row has a qid that matches to a question. So I can group answers by the question they correspond to. Now I'm trying to select the total votes and the answer with the most votes for each question. The total votes part is working fine, but I'm having trouble with the winning answer part. I've got a MAX(votes) that works well, and finds the answer with the most votes for every question, and returns its votes. What I want to do is select the `answer` column (the one that houses the actual text answer) of the answer with the greatest votes (the winner). Code:
View Replies !
Selecting From One Table Based On Another?
I have a table for products and another table for the type of product it is like this: I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc. but how can I then select a product based on criteria from the types table say I wanted to select all products that are type = "Band" and type != "Fancy" ?
View Replies !
Add Time :: Out Of Range Condition
I've got a time field (HH:MM:SS) that I want to add 1 hour to....... I've tried many combinations.... I assumed that I could simply use select time + interval 1 hour but this gives me an "out of range condition".
View Replies !
Selecting Based On Matched Column?
Lets say I have table with two columns zip_work and zip_home. I'd like to construct a query looking something like this: SELECT {here is the problem} FROM myTable WHERE zip_home=99999 OR zip_work=99999 GROUP BY zip_home, zip_work LIMIT 100; In response I'd like get two column table with zipcode as the first column and COUNT() of matched users. The idea is to know how many users are working or living in 99999 zipcode.
View Replies !
Selecting Values Based On Column
I couldn'y find solution to this on forum so here it is : sample table : id | name | value 1 | x | y 1 | z | v 2 | x | y i would like to select id where x=y and z=v. so in this case result should be : 1 (2 is not in the result coz it doesnt match x=y). How to create such a query?
View Replies !
Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me: Table 1 - portrait field 1 - id field 2 - type (will be the id from a row in business or personal) Table 2 - business field 1 - id field 2 - bizname Table 3 - personal field 1 - id field 2 - firstname field 3 - lastname His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account. If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
View Replies !
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
View Replies !
Selecting Based On Relevancy Of Number Ranges
I'm trying to figure out the best (any) way to do a relevancy query based on how closely a group of four numbers in a row match a supplied set of numbers. Basically, in each player's row, I have four game rankings that make up 100 points total. They may, for example, be given 40 for score A, 25 for score B, 20 for C, and 15 for D And, I want to be able to return a result set thats weighted based on the someone who's got a certain set of scores In one scenario, a C of 45 and B of 20 In another scenario, a A of 50, a D of 20, and a B of 5 etc I want to weight the results based on who is closest to the first selection, then the second, then the third, etc... Is this even doable? :0
View Replies !
Selecting Values For This Record Based On Other Records
Is it possible in a select to reference the contents of a column in a different record to the one currently being assessed? Kind of like :- Select id,prev_id,title,flag FROM my_table WHERE flag != "S" AND flag(prev_id)="S"; What I'd like it to do is select records which have flag set "S", but the record referred to in prev_id do not have flag set "S". If it matter, I'm stuck on 4.0.
View Replies !
Selecting Events Based On Various Date Criterias
I'm a little stuck at how to tackle this one efficiently. I'll try to explain concisely... I have a form to search for events. Form fields include Postcode Event name Venue name When (radios) -> today -> tomorrow -> this weekend OR instead of 'When' they can choose a specific date. In the events table there are a various columns including startdate (of type datetime). There is also a column for each day of the week that the event is repeated. For example, if the event is repeated on a monday then the column 'repeat_mo will' have a value of 1. My difficulty lies in creating a query to retrieve all the relevant results. For example, a search could include the following variables from the form: postcode -> YO14 7LX when -> tomorrow So I'd need to go to the events table and retrieve all rows that have a similar postcode, a start date that is tomorrow OR has been flagged as being repeated every wednesday. The postcode bit is fine and getting tomorrow date (with php) is fine. But how do I put it all together to also take into account the repeat day flags?
View Replies !
Returning Clock Hours In Time Range
I have a table such as below: BreakIDStartTimeEndTime 108:00:0010:00:00 208:00:0010:30:00 308:00:0011:00:00 408:30:0011:00:00 What I need to find are the whole clock hours that are wholly within each time range. So, in this example: 108:00:00 109:00:00 208:00:00 209:00:00 308:00:00 309:00:00 310:00:00 409:00:00 410:00:00 Just to be clear, when I say clock hour I mean a 60 minute period that starts at one of the hours universally recognized for designating 1/24 segments of the day. E.g. 8:00 AM, not 8:01 or 8:30. I am sure there is some technical terminology for this, but it escapes me at the moment.
View Replies !
How To Save Accounts Based On Variable Amounts Of Time
I'm currently building a website where users can signup for various accounts. An account may last 30 days, 12 months or 24 months. If a user's account is about to expire, an email notification is send, via a CRON Job. I want to save the different account types in the database, but I'm not sure how to save the duration of an account. If it was all months, I could simply save ཈' or པ' as an INT, but it also has an account for 30 days, which is more flexible. Edit: It would also be nice if I could ORDER BY duration..
View Replies !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page * The code selects all the bookings for a range of time. * It then counts how many concurrent bookings there are for each hour. * If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table. We also notify people if there has been a cancellation This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding! We've ended up with 5 sessions booked In the course of 2 page requests this seems to happen: User1 - submits their booking site selects to see if there's availability ( there is!) User2 - submits their booking site selects to see if there's availability ( there still is!) site inserts User1's booking site inserts User2's booking
View Replies !
Selecting Rank Over Time
I have 4 colums in my table.. they are id(int), usrid (int), datestamp (date) and credits(float). here's what i'd like to do.. select the numeric rank of a given userid over the past 30 days based on the most credits... ideally i'd like it all to be in one query but that may not be possible.. any ideas are welcome here, i'm stumped on this one.. so far all i've been able to do is select all the entries from the last 30 days.. SELECT * FROM daycredit WHERE TO_DAYS( NOW( ) ) - TO_DAYS( datestamp ) <= 30 and usrid = $usrid LIMIT 0, 30
View Replies !
Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using: *********** SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC *********** I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?
View Replies !
Selecting Date Time Field, Difference In Minutes
I wonder if is there any way to select from a table all the records which has a Date Field that is at least five minutes old? In other words, I have a table with a date field and I need to select all the records that are older than five minutes, has their date field updated before five minutes. Usually I do it with days by using the to_days() function and comparing the date to now: (to_dayss(now()) - to_days(somedate)).
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 !
Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables, Table A- has the field "id" which is the primary key Table B- has the field "id" which is the primary key Select * from tableA where Table A.id is not in tableB.id How can i form a statement that will accomplish the above.
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 !
Minutes + TIME = TIME Function
I need some way to convert minutes (e.g. 80 mins) to SQL TIME (e.g. 01:20:00) in order to add it to another TIME var. Is there any way to do this? I really dont want to convert my whole db. Basically, I want to do: e.g. 80 + 10:00:00 = 11:20:00.
View Replies !
How To Retrieve Mean Time Between Time Stamps
I have a table that stores failure information of external application. The time stamp of every failure event is stored. Now I'd like to make some system performance & reliability statistics calculations based on time stamps in certain recordset; mean time between failure MTBF, down time DT, etc. Example: 2007-09-27 12:23:52 2007-09-27 08:50:23 2007-09-26 23:31:34 2007-09-26 21:45:33 Here the times between are (hours:mins): 3:33, 9:18, 1:46 and MTBF=4:52 Any effective possibility to make this example within a single query - or a procedure is needed?
View Replies !
Time Format Military Time
I know the time format it HH:MM:SS in mysql but can I input military time? reason I can is that I want to later extract that information for calculations and military time is easier to work with.
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 !
Range
I'm trying to write a script that reports the number of users in each country. I have a users table that contains an ip address field and another table that contains the ip-ranges of each country. It has 3 significant columns, (1) name of the country (2) ip range start (3) ip range end. Is it possible to write one SQL statement that will have this result: 1. First column is the name of the country 2. Second column is the number of users within that country's ip-range 3. All countries with no user records will be disregarded I'm thinking about using GROUP BY but I'm not sure if it's possible to use it with ranges instead of just one column match.
View Replies !
Only Get Certain Age Range
im making a user search system for my website, an im trying to get it to display only users whos ages are within to criteria... e.g to display all users between 18 and 25... Does anyone have any ideas how to do this? the table is: hq_user an the dob cell is user_birthday whis is in 'date' layout.
View Replies !
Particular Range
I have a query, which selects and sorts some data. I need to get a particular range of these results, rather than all of them (e.g. the first twenty, or from the twenty first to the fortieth). Is there any way I can modify my query, to achieve that?
View Replies !
Age Range
I am working on my first web application and I am having some difficulties selecting an age range from Mysql. Let's say the user filled out that he is looking for a woman between 18 and 25 years old. This is the SQL I use to get it: SELECT t1.custnr, t1.name, t2.gender FROM klantnaw AS t1, klantspecs as t2 WHERE gender = 'woman' AND DATE_SUB(DATE_ADD(CURDATE(), INTERVAL -18 YEAR), INTERVAL -7 YEAR) <= t2.birthDate AND t1.custnr = t2.custnr The 7 is the difference between the first and the second age. This doesn't work. It gives some results, but wrong results. Could you tell me how to select an age range from a table that contains birthDates?
View Replies !
Between Price Range
I swear this is ment to be faily simple but basically I'm having a problem with searching between 2 price ranges. What I thought it would be is: SELECT * FROM ta_property WHERE property_price BETWEEN 200 AND 300 AND property_active = 0 Also say if I wanted to get all properties listed over 300?
View Replies !
Column Out Of Range
When I try to perform the following update on a table with a column of type "decimal(3,2)" I get the following error: Code: #1264 - Out of range value adjusted for column 'score' at row 1 The value that should be filling 'score' is "10.00". The SQL statment is from an exported MySQL 4.0.25 database that I am trying to execute with a MySQL 5.0.15 database. I am aware of the changes made to the decimal datatype as of 5.0.3 but I can't figure why (3,2) wouldn't be enough to handle two digits to the left and two to the right of the decimal. What am I missing?
View Replies !
Date Range Within A Date Range
I have tryed sub queries and betweens, and cant get it right. I have a list of hotel rooms types than links to a hotel table I also have a costs table with cost periods - like low season and hi season. so I might have 01/05/2009 | 30/05/2009 Low 01/06/2009 | 30/06/2009 Hi if somebody supplies dates to check availability say of the 29/05 - 6/06 the requirment is supplied by two rows of the table.
View Replies !
Maximum Range
maximum database in a server. maximum tables in a database. maximum rows in a table. I have known about there is 60000 tables and 50 millions record in a server. I use window XP sp_2 and mysql server 5.0.45. Can i store image and file, and process them with my web application.
View Replies !
Number Range
I have weight ranges of 100,300,500,1000. The user will enter the package weight in the form and the query will return the price per pound. How can I select the correct weight in the query?? $weight=400; $getprice="SELECT * from priceMatrix WHERE VENDOR='$vendorArray[$i]' AND SERVICE=$serviceArray[$s] AND WEIGHT<=$weight";
View Replies !
Out Of Range Value Error
I get this error when I input 364.38 into a column that is data type decimal (4,2) Code: ERROR 1264 (22003): Out of range value for column 'amount' at row 1
View Replies !
Column Range
I have this simple question, let's say you select data from 2 different tables and those two tables both have a field named 'ID'. Is there a way to get all the field of the two tables exept the second 'ID' field but using wildcard, for exemple to illustrate the concept: SELECT table1.*, (table2.* AFTER table2.ID) WHERE year > '2004'; I know this command doesnt work but is there any way to do this?
View Replies !
|
TRACKER
