MYSQL :: Select All Records Less Than 10 Minutes Old?

HELP: SELECT Only Last Last Few Records That Were Created Within Last 5 Minutes
I've got a transactions table with a tstamp field (datetime type) and
I need help creating a SELECT query to retrieve only last transactions
that occured within the last 5 minutes. The datetime type writes the
tstamp as YYYY-MM-DD HH-MM-SS and I am using mySQL 4.1.

How To Get 15 Minutes Old Records
Scenario :
My system enables a record on zero minute of every hour [through cron job].

Lets suppose right now time is 10:10 AM.

Query:
My query is that ->
1- How can I get all records which are enabled before (1 hour and 15 minutes) of the current time?

Selecting Records From Database Entered Less Then 30 Minutes Ago?
I have tryed everything to select records from the database that have been entered less then 30 minutes ago. I have the time/date stored as a TIMESTAMP, so i need to find a way of returning the records.

Select Every 50 Records
when will be released the ultimate mysql 4.1 (no alfa, beta, gamma release

How To Select Records?
I select a list of records eg: from table1.

select names as temp from table1;

next i want to use the names selected from table1 to select other records again from table 2. How do i write the combine statement?

Select All Records
I have a structure like this:

A table (authors) that consists of authors names, ages, and an authorid
A table (books) that consists of books names, year published, and a bookid
A table (aTob) that consists of authorid and bookid, to match book to author

I want to write a select statement that selects all authors and all books they've written, if any.

So my best guess is:

SELECT authors.name, authors.age, books.name, books.year from authors, books, aTob.

But this doesn't work...sems to draw SQL into an infinite operation. The result I'm looking for is a row for each author and book...so some authors will be listed several times if they've written several books...BUT, I also want authors who haven't written any books...The end goal is to have a view in which I can do some statistical things, such as find the average year of publication for each author.

Select Records
I am needing to select records where a field has the current date. I figured that this would work:

SELECT * FROM table WHERE DATE(datetimefield) = DATE(NOW())

But that doesnt seem to work. In PHP i receive the following error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(datetimefield) = DATE(NOW()) ORDER BY datetimefield' at line 1,I have tried with a conjuction of MONTH and DAY, but it always seems to hang on the DAY part of it. Whats up with it?

Select Records
i'm quite a newbie in using MySQL so this question should be an easy-answer one.
I've got a db with about 20 tables in it containing various data about architecture projects like name, description, dimensions, year, etc.. Each table contains a specific kind of project but every table has the same structure. I want to select the latest project inserted considering every table (for example latest 10 project between all the tables). I tried with this but doesnt work:
SELECT * FROM pro_pianificazione, pro_master, pro_grandi, pro_residenza, pro_terziario, pro_attivita, pro_allestimenti, pro_arti ORDER BY 'id' DESC LIMIT $primo, $per_page
Of course the vairables of LIMIT instance are prevoiusly set in the code.

SELECT Records
I have two MySQL tables as follows:A 'wine' table that contains about 50 wines & has fields such as wine_id, name, origin, vintage etcA 'wine_venue' table that lists venues and the wines that each venue does NOT offer. It has 2 fields venue_id and wine_id
I want to select all the wines that a specific venue DOES offer. (BTW most venues offer all the wines in the wine table)
I have tried the following select statement, but it does not give the result I want!Code:

SELECT *
FROM wine, wine_venue
WHERE wine.wine_id != wine_venue.wine_id
AND wine_venue.venue_id = 23

Select Records
I need to know if it is possible select records from a database table on a remote server and if so what is the command syntax?
I'm picturing something along the lines of

SELECT * FROM <server address>:<database>.<table> but that does not work.

How To Add X Minutes, Myql Way
i using LOCALTIME() in my mysql codings...
how can i add in mysql X minutes to this time?

mySQLde="UPDATE DTIMES SET TIME=NULL WHERE TIME <" &DATE_ADD(LOCALTIME(),INTERVAL -30 MINUTE)
Microsoft VBScript compilation error '800a03ee'

Expected ')'

mySQLde="UPDATE DTIMES SET TIME=NULL WHERE TIME <" &DATE_ADD(LOCALTIME(),INTERVAL -30 MINUTE)
---------------------------------------------------------------------------------------------^

DATETIME Was How Many Minutes Ago?
I have a DATETIME stamp in a mysql database and wish to find out how many minutes ago it was... looked across mysql.com for this but no luck.

Select Surrounding Records
I am trying to find a way to select 2 rows before and two rows after a known row.

For example, I have a table with an id and a datetime field. Given row with an id of 70, how can I select the two rows that would preceed it, and the two that would follow it, when the table is sorted on the datetime field? I have been looking around, but can't seem to find a way to this.

HOW TO: Select Only The Last 2 Records In A Table.
I'm not very good in mySQL but am trying to modify a script.
It has to do with a log table.
I want to retrieve the last 2 log records (if any) and if the last log
entries are the same as my current (to be) entry, simply update the time in
the last entry. But if not, simply insert the new entry.

My question is how to select the last two entries from the log table?

CREATE TABLE broadcast_log (
id mediumint(8) unsigned NOT NULL auto_increment,
time varchar(20) NOT NULL,
entry varchar(255) NOT NULL,
PRIMARY KEY (id)
);

Select Distinct Records
I am trying to compare the date part of a datetime value field with today's date....

Here's the sql:

mySQL = "Select * from Test WHERE TheDate LIKE'"&date()"' ORDER BY TheDate"
Set rs= Con.Execute( mySQL )

That return nothing even though Test has records for today..

Select Last 5 Records Entries?
I have an extremely basic MySQL table with 2 fields, "id" and "name". I want to be able to select the last 5 "name"s that have been entered into the database.

Cant Select More Than 1000 Records
Command "select * from table" whith 3000 records give me only 1000 rows. I can't figure out what is wrong. I think that something wrong is with my my.cnf but can't find which varaible I have to change.....

Select Records Without Relation.....
I've 2 tables and the relation between them is the id. I want only select the records from table A which are not present in table B!

Select Records That Are A Group
I have a table like this:

ITEMID CODE
1 1
1 2
1 3
2 1
2 3
2 4
3 1
3 2
4 3

where every item is a set of codes. For example,
ItemID 1 = (1,2,3)
ItemID 2 = (1,3,4)
ItemID 3 = (1,2)
ItemID 4 = (3)

How can I know if an item exists? For example, if (1,2,3) exists or if (1,2) exists?

I'm working with a MySQL Database, and VB.Net application.

Select Records By Date
I got a form where the date is being plugged into the database on items that are marked as sold.

I want all records to show up until 5 days after being posted as sold.

So for example:

If I posted a item as sold on Oct 5th, after Oct 10th the record wouldnt be shown again.

Select Doesn't Show Some Records
This problem is driving me crazy. I've got a table with about 200 records in it. It contains a list of domains.

When I do a select * from table it works shows all records. But when I do a select * from table where url="http://www.xxxxxx.com/blahblah" it shows most records but it doesn't show certain ones. At something like record 150 to 160 select * from table where url="http://www.xxxxxx.com/blahblah" doesn't work.

So on about 10 records the select doesn't work it just returns 0 records. This is really confusing me because it's strange i've made certain the characters are exactly the same as the record i'm searching for(copy & paste as well) but it just shows 0 records.

Running INSERT 1 Or 2 Minutes???
I've been searching the web for code that would INSERT the date and time every 1 or 2 minutes? Is this possible?

MySQL Restarting Every 8:30 Minutes
My MySQL service seems to be restarting itself in intervals of 8 minutes and 30 seconds. There is, at most, a variation of a second or two. I can't seem to find out what is causing it. There are no cron jobs that I can see, the error log is only showing the stopping / starting messages, and the mysqld log doesn't seem to be showing anything peculiar around the time of the restarts.

How To Do An Action In Mysql Every 15 Minutes
i want to get the values from a web and add it to my database. but i don't know how to call a process every 15 minutes and i don't know how to get the data from the web. The web data is stored on a table, i thin i can do something with it.

Best Way To Select Records WITHOUT Relationship In Another Table?
I have a stock table and a stock_category_r table which is a relationship table between stock items (products) and stock categories (M:M relationship)

I need to select stock that is uncategorised. I.e Items that have not been related to ANY categories.

I was using this query with a subquery:

SELECT s.ID, s.code, s.name
FROM `stock` s WHERE s.ID NOT IN
(SELECT stockID FROM stock_category_r)
Just wondering if this was the most effecient way to find stock records that have no corresponding relationship in stock_catgegory_r ?
Do sub queries effect performance much?

My tables for this app have very few rows, but I'm interested in the best-practice theory

The table structure:

CREATE TABLE `stock` (
`ID` smallint(5) unsigned NOT NULL auto_increment,
`code` varchar(16) NOT NULL default '',
`name` varchar(40) NOT NULL default '',
`description` mediumtext NOT NULL,
`image` varchar(35) NOT NULL default '',
PRIMARY KEY (`ID`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `stock_category_r` (
`stockID` smallint(5) unsigned NOT NULL default Ɔ',
`categoryID` tinyint(3) unsigned NOT NULL default Ɔ',
PRIMARY KEY (`stockID`,`categoryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Select Last N Records Matching A Query.
I am trying to select the last n records in a database which conform to criteria selected by a user. I gather that there is no "BOTTOM" equivalent to the "SELECT TOP" command. I've tried "SELECT... ORDER BY... LIMIT X,Y" but MySQL doesn't seem to take a second parameter for the LIMIT statement. Is there any way round this?

Select Records In A Range Of Values
I have a figure that a user inputs into a php script for how many people an apartment sleeps. What I want to do is select all properties in a database where this is so. Ok, easy enough for now. What I also need to do is to select apartments that may sleep 1 or 2 people more as well.

So, for example, if a person asks for apartments that sleep 3 people, it should return all apertments that sleep 3 and 4 people (already got this working), but if the person asks for an aprtment that sleeps 6 people, it should give all apartments that sleep 6,7 and 8 people and if they ask for an apartment that sleeps 10 people then it should return all apartments that have more than 10 in teh sleeps field.

SELECT Date Records Between Two Given Dates.
Let's say i have a job posting site and a user wants to see the jobs posted between dates.... how would i do that?

here's how a table would look like(sample):

table: Jobs
fields:
JobID
JobStatus
DatePosted
DateToDeactivate

and when i build the query i would make it something like this:

"Select * from Jobs Where JobID = '123' and ( DatePosted BETWEEN 2003-05-08 12:14:37 AND 2004-06-08 12:14:37)"

i know that this does not work! how can i do this.

Select Unique Records From A Join?
I have two tables, both tables contain a matching JobID. Some records in the job table are not present in the joblocation table. I need to Select only those records. I thought I could just do a left join on the two tables with a not equal to where clause?

SELECT
job.JobLocation1,
job.City,
job.Province,
job.SiteName1,
job.Code,
job.JobID,
joblocation.JobID
FROM soileng.joblocation joblocation
LEFT OUTER JOIN soileng.job job ON joblocation.JobID = job.JobID
WHERE job.JobID != joblocation.JobID limit 100

Any ideas on how to get this to work?

Select Records That Only Occur One Time
SELECT records that only occur one time.

I have a table with an ID field. I want to SELECT the records where the ID field only has one occurrence. If it appears in two or more rows I do not want to SELECT it.

Working Out The Number Of Minutes Between Two Dates
Using MySql 3 (deliberately using an old version) how can I work out the difference between a date in a database field (stored as a unix time) and now. I'm actually using PostNuke and want to identify how long open items have been in my phpbb forum.

I've tried various forms and the last effort was :
select to_days(now())-to_days(from_unixtime(p.post_time)) from nuke_phpbb_posts p

but that gives my a lowest number of 176 (I know people have been using it today).

Retieve Only The Difference In Minutes Between Two Dates
i am just lookin for a func or way about to retrieve the
difference in minutes between 2 dates which are of DATETIME data type
for induvidual records help in this regard is highly solicited.

Limit On Select Produces Records Out Of Order
I have a Java applet that displays descriptions (from an sql table) in a text area. The latest description is shown on load-up with max(time) ok, and there is a button to step through them in reverse order from latest to earliest.

I have a problem with the following query:

SELECT idescription FROM descriptions where unit = 'A' and iname = 'plate' order by 'time' limit "+nextrecord+",1

This query is executed on a java button press. The variable 'nextrecord' has already been set to 1 (to get the second latest, i.e. 'very useful item') and then 1 is added to it each time the button is pressed to get the next previous one. But the resulting order is squiffy, e.g. when applyed to the following table Code:

Select / Count Not Showing Records ... Permissions?
I imported 600,000 records from SQL Server 2005 to mySQL.

I know they are in there -- I just can't see them!

When I do a SELECT, it processes for a while, and then returns nothing.

Same with a count, it sits there processing, and then returns a '0'

However when I do the same on a table I know has no rows, it definitely
comes back right away.

I'm logged in as root. Do I need to assign myself some permissions to
see these records? And the count numbers?

Select Where Multiple Joined Records Match
I'm trying to figure out how to select all the records in one table which have multiple specified records in a second table. Here's a simplified version of my problem. I have two tables, resources and goals.

resources table:

ID TITLE
1 civil war women
2 bunnies on the plain
3 North Carolina and WWII
4 geodesic domes

goals table:

ID RESOURCE_ID GRADE SUBJECT
1 1 1 English
2 1 1 Soc
3 1 2 English
4 2 1 English
5 2 3 Soc
6 3 2 English
7 4 1 English

Now, how do I select all the resources which have 1st and 2nd grade
English goals? If I just do:

Select * from resources, goals where ((resources.ID =
goals.RESOURCE_ID) and (SUBJECT="English") and ((GRADE="1") and
(GRADE="2")));

I'll get no results, since no record of the joined set will have more
than one grade. I can't just put 'or' between the Grade
conditions; that would give resources 1, 2, 3, and 4, when only 1
really should match.

My real problem is slightly more complex, as the 'goals' table also
contains an additional field which might be searched on.

I'm thinking it's time for me to go into the deep end of SQL (MySQL,
actually), and my old O'Reilly MySQL & mSQL book isn't doing the
trick.

Join 2 Tables And Select Only Most Recent Records
I have been wrestling with a way to select only the most recent records for a group of clients without using a subselect. I have two tables: cdp_clients and cdp_level_records related by 'admitid'. For each cdp_client there are multiple cdp_level_records, each with a different 'level_date'. I want to select only the most recent cdp_level_record for each client in cdp_clients.

How Do I SELECT Records From A Database In Alphabetical Order
how to SELECT records from a database in alphabetical order,

Build An Automated PHP Gallery System In Minutes
Kia Ora From New Zealand

I get the following warning on both these tables

PRIMARY and INDEX keys should not both be set for column `category_id`

also I have built a script to add a new catorgory to the gallery but the only thing that happens is the Category_id increments is this becouse PRIMARY and INDEX keys should not both be set for column `category_id`

CREATE TABLE gallery_category (
category_id bigint(20) unsigned NOT NULL auto_increment,
category_name varchar(50) NOT NULL default Ɔ',
PRIMARY KEY (category_id),
KEY category_id (category_id)
) TYPE=MyISAM;

CREATE TABLE gallery_photos (
photo_id bigint(20) unsigned NOT NULL auto_increment,
photo_filename varchar(25),
photo_caption text,
photo_category bigint(20) unsigned NOT NULL default Ɔ',
PRIMARY KEY (photo_id),
KEY photo_id (photo_id)
) TYPE=MyISAM;

Calculating Time Difference In Minutes Between 2 Times
I have two columns in a table start_time and end_time (stored as datetime). I wish to work out the time difference in minutes between these two values. If the difference is 1hr 30 minutes I wish to get 90 (mins)

I have tried:

SELECT TIME_FORMAT(TIMEDIFF(start_time, end_time), '%i') FROM table ... This returns justs the minutes (e.g. 30)

and

SELECT TIME_FORMAT(start_time - end_time, '%i') FROM table .... no such luck.

Find Days, Hours And Minutes Between 2 Dates
I have a datetime field in a mysql database and i want to output how many days, hours and minutes are left between the current date and the future date.

So far i just convert the datettime to a timestamp like so: $start = strtotime($began);

Where $began is the current time, basically i just need to know how to convert a timestamp to days, hours ect If i was to minus the current timestamp from the future timestamp.

How To Substract Minutes From A Time Field In Mysql Query?
I have a problem, i want to substract minutes from a time field in a mysql query, i'm trying with the next query:

UPDATE TblOrders SET TmHours = TmHours - '00:09:00';

In the query I want to substract 9 minutes from all time fields named TmHours in the table, the datatype for the field TmHours is time.

Function To Extract Difference In Minutes From DateTime Variables
I need to compare a datetime field in my database with the current time
and return only the records that are 5 minutes old or older. Is there
any way to do it? Any function to it?

I use to_days() when I am doing the same thing but with days instead of
minutes.

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)).

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

Count Related Records, But Show Records With NO Related Records Also
cl_items
========
it_id (pk)
it_ownerid
it_name

cl_offers
========
off_id (pk)
off_itid (fk) -> to it_id
off_whoid

My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers)

I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also.

Is there any way to do this with mysql only?

All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`

Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

Select All Records From One Table That Does Not Appear In Another Table
I need to select all records from tableA that does not appear in tableB. I am using the following query that does work but is very very slow. Is there anything i can do to speed up the query?

select tableA.ID, tableA.Name, tableA.Surname
from tableA where tableA.ID != ALL (select tableB.ID FROM tableB where tableB.status = 'inserted' or tableB.status= 'edited' or tableB.status = 'deleted' );

I have as example 6000 records in tableA and 2000 records in tableB. tableB is used to track which records have been inserted, edited or deleted from another system so the query should return all records that do not exist in the other system yet.

This query runs for about 3mins and just gets slower the more data there is.
Can anyone suggest anything that could make this a sub second response?

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.

Select All Records Less Than 10 Minutes Old?

I'm wondering if anyone can tell me how to write an SQL statement that will select all records that are less than 10 minutes old, using a datetime column?