MYSQL :: Working Out The Number Of Minutes Between Two Dates

Working With Dates
After taking a break from this project for a bit I find myself confused about how to pull specific date periods from a database. The timestamps are stored in UNIX format. I'm creating a report builder to pull details about issues that my techs have entered (ISP support center). I can pull reports from calls entered today or yesterday, but getting them from say, mon-fri of last week is throwing me for a loop. Can anyone point me in the right direction? This is what I have thus far:

//echo "DateRange: ".$daterange."<br>";
// begin building our query
$query = "SELECT * FROM issues WHERE ";

// the value passed in date range is how we modify the date lookup in our SQL query
if ( $daterange == "today" ) {
$timestamp = strtotime("today");
$query .= "DATE_FORMAT(CURDATE(), '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "yesterday" ) {
$query .= "DATE_FORMAT(CURDATE() -1, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "thisweek" ) {
$query .= "DATE_FORMAT(CURDATE() -7, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "lastweek" ) {
$query .= "DATE_FORMAT(CURDATE() -14, '%Y%d%m') = FROM_UNIXTIME(issue_submitted_date, '%Y%d%m');";
}
else if ( $daterange == "thismonth" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 1 MONTH) >= issue_submitted_date;";
}
else if ( $daterange == "lastmonth" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 2 MONTH) >= issue_submitted_date;";
}
else if ( $daterange == "thisyear" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 1 YEAR) >= issue_submitted_date;";
}
else if ( $daterange == "lastyear" ) {
$query .= "DATE_SUB(CURDATE(),INTERVAL 2 YEAR) >= issue_submitted_date;";
}

Working With Dates...
How would I finish a query like this:

SELECT * FROM table WHERE datetime_field BETWEEN NOW() AND <tonight at midnight>

I'm not sure how to calculate "tonight at midnight" in a format that can be compared to the result of NOW(). Any ideas?

Working Out A Percentage Of A Number
Is there a way that I can calculate a percentage of an amount from a field in the database?

For eg If I had a field with the number 颼' in it could show me what 17.5% of that would be?

Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like:
2/2/2006 0:00:00, 12/20/2006 9:22:05

Any ideas how to format those into mysql friendly dates?

Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula?

===========================================

LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt'
INTO TABLE auto
(field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9)
SET id = MD5(UUID())
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'

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?

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.

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.

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?

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.

How To Select Records When No Activity For 20 Minutes
I have a problem, i have 31days, 24h loged activity (timestamp). If there was no recorded activity in the log for 20 minutes in this interval, i have to find when this happened (Record A, Record A+1).

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.

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.

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.

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

How To List All Missing Dates Between 2 Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Stroring Dates Perior To 1 AD, BC Dates
I need to store some dates that can be BC, However I received a problem indicating Incorrect Datetime.

I have read the documentation, and it says that Date&DateTime types can store from 1000-9999 AD, and Timestamp stores less year ranges. What other options do I have to store dates before 1000 AD !

List All Dates Between 2 Diff Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Dates, Dates, Dates - Syntax Help Please!
OK, I have a MySQL table which contains, among others, a field called date which stores dates in the format YYYY-MM-DD HH:MM:SS.

What I'd like to do is select a list of all the months present (without repetition), preferably in the format YYYY-mm

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.

!= Not Working?
SELECT

d.cache_diff,
d.cache_name,
d.cache_type,
d.cache_last_found

FROM geocaches d
LEFT JOIN logvisit ON d.cache_id=logvisit.cache_id
WHERE d.cache_status = 0 && logvisit.log_type != 'Found' && logvisit.log_owner_id = 64

This returns zero rows. Its true that records in logvisit.log_type for this user are only FOUND but I am trying to display cache information for all other caches (in geocaches table) that the user did not log FOUND in logvisit.

So, basically if the user logged against 3 caches and there are 10 caches available to log agains then I want to display only the remaining 7. Is JOIN the way to go on this one?

LIKE '[0-9]%' Not Working
Here is the SQL query:

Code:

SELECT * FROM member WHERE username LIKE '[0-9]%'

Returns 0 rows. I've tried this on 4.1.7-nt as well as 4.0.22-standard; neither work. And yes, there are records in the table that should match that pattern.

Not Working
I have 4.1.10a on my local machine and the following WHERE clause works fine:
Code:

WHERE wk1 IN (select max(wk1) from wkpts where teamid = w.teamid)

But when I upload it to my web site, I get an sytax error at 'select max(wk1) from...'
My web host has MySQL version 4.0.20-standard.
Can someone tell me why it would work locally, but not on my web host's server? Thank you.

LIKE '%$var%' Not Working
I am trying to create an sql statement to return values on either side of the variable.
Code:

$sql = "SELECT * FROM principals LIKE companyName = '%{$_POST['companyName']}%'";

The error was:

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 'LIKE companyName = '%Mike%'' at line 1

Select As Where Not Working!

SELECT CONCAT(RIGHT(dt,4),'-',MID(dt,4,2),'-',LEFT(dt,2)) AS dateavail FROM atable WHERE dateavail = &#55614;&#57158;-06-01'

(dt is a date in format dd/mm/yyyy - the concat bit works fine)

Microsoft OLE DB Provider for ODBC Drivers error ?e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.19-standard-log]Unknown column 'dateavail' in 'where clause'

Also, if I get it to work, how do I convert the 2 strings to date so I can look for < and > ?

Doing A JOIN Not Working
I have 2 tables, one contains columns that holds the ID to the other table so eg

Table 1 (images)
id | imagepath
--------------
1 dir/hair.png
2 dir/face.png

Table 2 (buildImages)
userid | hair | face
1 1 2

So i'd just like to retrieve a row like

dir/hair.png, dir/face.png

where userid = 1

does that make sense?
Oh, the title of this thread is kind of wrong, shoudl be how do I do this join :P

DATE_SUB() Is Not Working Right For Me.
My `date` column is in date/time format 2007-12-14 19:55:00
When I try to select only the records added in the last 5 hours, I get records from more than 24 hours ago. I know I am EST zone and the server is PST zone, but something else is wrong. This is my WHERE clause:

WHERE `date` > DATE_SUB(CURRENT_DATE, INTERVAL 5 HOUR)

Working With SET Type ...
I have this SET(Ƈ',ƈ',Ɖ',Ɗ') on a column. Suppose that the colunm has the data (1,2,4)
Now i want to select rows that contain either 1 or 2 or 4. What is the fastest way to do this ?

Left(str. Len) Not Working!
BTW: I am very new to PHP & MySQL learning from the site point book ("Build your own database driven website!").

My Question
If i use the left function from the command line, the expected result is returned ok, however when i apply the same query to my web page nothing is returned.

This is my query:
$sql = "SELECT date_added, short_desc, LEFT(long_desc, 10), news_ID, loc_id FROM location Inner Join news ON globalisation = loc_id WHERE loc_id = Ƈ' ORDER BY news_ID ASC";

$getRows = @mysql_query($sql);
if (!$getRows)
{
exit('Error: Loading SQL statements: '.mysql_error());
}
while($listedItems = mysql_fetch_array($getRows))
{
$id = htmlspecialchars($listedItems['news_ID']);
$news_short = htmlspecialchars($listedItems['short_desc']);
$news_long = htmlspecialchars($listedItems['long_desc']);
$date_added = htmlspecialchars($listedItems['date_added']);
echo '<div class="row_container">'
echo '<div class="col_1">' . htmlspecialchars($id) . '</div>'
echo '<div class="col_2">' . htmlspecialchars($news_short) . '</div>'
echo '<div class="col_3">' . htmlspecialchars($news_long) . '</div>'
echo '<div class="col_4">' . htmlspecialchars($date_added) . '</div>'
echo '<div class="col_5"><a href="edit_news.php?id=' . $id .'">Edit<a/></div></div>'
}

DISTINCT() Not Working
Does anyone have any idea why i can't get this to return distinct values?

PHP

SELECT DISTINCT(agi.userid), agi.imageid, agi.title, agi.dateline, agi.catid, agi.thumbname
    FROM a_gallerys_images agi
    WHERE agi.valid = 1 AND agi.open = 1 AND agi.catid = 2
    ORDER BY agi.dateline DESC
    LIMIT 4

MAX(mainid) Isn't Working For Me???
I am wanting to get the last inserted mainid number then insert back into a different row at a higher number. For example if the inserted mainid was 1 the next number should be 2. The code below isn't working for me. It just adds 0 to the mainid column. Can someone please tell me if there is a better way of doing this? Thanks in advance.

Here is my

PHP
require "config.php";
// more code here
$insert1 = "INSERT INTO `menu` (`mainid`)
SELECT max(mainid) + 1
FROM menu";
if (mysql_query ($insert1)) {
$query1 = mysql_query("SELECT LAST_INSERT_ID() as id FROM menu") or die ("Could not query because: ".mysql_error());
$row1 = mysql_fetch_assoc($query1);
$id1 = $row1['id'];
$update1 = "UPDATE menu SET menu = '$menu', pagetitleMenu = 'Menu', pagetitle2 = '$pagetitle', pagetitleSub = 'none', subid = Ɔ', submenu = '$submenuyesno' WHERE mainid = '$id1'";
// more code here
mysql_close();

Working Of Mysql
can any one explain me in details how mysql engine works

Queries Not Working
its been ages since I have worked with MySQL so I am very rusty and am just trying to patch together loads of old code at the moment.

$dbh=mysql_connect ("localhost", "*********", "************") or die ('I cannot connect to the database because: ' . mysql_error());

mysql_select_db ("*********");

$result = mysql_query("SELECT * FROM Update
WHERE number=Ƈ');

$row = mysql_fetch_array($result);

echo $row['title'];

mysql_close($dbh);
I keep getting this message: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dcaggorg/public_html/update/index.php on line 18

Fulltext Example Not Working
I'm trying fulltext searching for the first time [Ver 12.22 Distrib
4.0.17, for pc-linux (i686)], but I can't figure out why the example
in the mySQL manual is *not* working.

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
Empty set (0.00 sec)

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
-> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

Subquery Not Working
CODESELECT parexel_staff.name, parexel_skills.skill, parexel_activity.date
FROM parexel_staff, parexel_skills, parexel_activity
WHERE parexel_staff.name = parexel_activity.name
AND parexel_activity.skill = parexel_skills.skill
AND parexel_skills.skill = 'CGI'
AND CURRENT_DATE > (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
AND parexel_staff.name NOT IN
(
SELECT parexel_staff.name
FROM parexel_staff, parexel_skills, parexel_activity
WHERE parexel_staff.name = parexel_activity.name
AND parexel_activity.skill = parexel_skills.skill
AND parexel_skills.skill = 'CGI'
AND CURRENT_DATE < (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
);

Character Set Not Working
I am using 5.0.24 max on my RedHat 8.0. Having problems inserting persian character set into my database. After searching around and doing what was recommended, I still got no where. I am using a firefox -> PHP -> mysql to enter and read data. Reading data from the command line results in garbage. Here is some useful info:

mysql> show create database mytest;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------+
| mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_persian_ci */ |

mysql> show create table binfo3;
| binfo3 | CREATE TABLE `binfo3` (
`id` int(11) NOT NULL auto_increment,
`year` varchar(8) collate utf8_persian_ci default NULL,
`make` varchar(15) collate utf8_persian_ci default NULL,
`model` varchar(20) collate utf8_persian_ci default NULL,
`miles` int(11) default NULL,
`price` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100009 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci |

mysql> show variables like 'character_set%';
+--------------------------+--------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-max-5.0.24-linux-i686/share/mysql/charsets/ |

Here is how I connect the DB:
# cat opendb.php
<?php
// This is an example opendb.php
$conn = mysql_connect($dbhost, $dbuser, $dbpass ) or die ('Error connecting to mysql');
mysql_select_db($dbname);
mysql_query("SET CHARACTER SET UTF8");
mysql_query("SET NAMES utf8");
mysql_query("SET CHARACTER_SET_RESULTS=NULL");
?>

In my browser after entering the data I echo the data and it appears fine. I read it back from the DB and its garbage like:
ID:100008
Year:Û±&
Make:Ø¨Ù†&
Model:Û²Û²Û°
Kilometer:0
Price:0

The & is not suppose to be there. Only 1 char from Year is appearing and 2 chars from the Make appearing. But Model looks fine! Kilometer and price are not accepting any inputs!

SELECT Not Working
SELECT U.*, G.Name AS groupname, C.FirstName, C.LastName,
(Select count * from Account Acc where Acc.User_ID = U.User_ID) as Account_ID
FROM ((Users U LEFT JOIN Groups G ON U.Group_ID = G.Group_ID)
LEFT JOIN Customers C ON U.Customer_ID = C.Customer_ID)
WHERE 1 = 1

The above returns a 1064 Error on SUSE Linux MySQL 4.0.18. But on WinXP MySQL5.0 all runs fine. Can anyone give me advice on how to fix the above to work? If I remove the second select from the query all works fine.

PHP Not Working With MySQL
Just starting out and I have PHP, Mysql, and Apache installed. PHP and MYSQL dont seem to be working together. Everything seems to be running fine and I edited the httpd.conf in order to allow apache to process php requests.

This is what I am doing when I start mysql...
c:/net start mysql
c:/mysql/bin/mysql -u root -p
Then I log in and it accepts the log in.

When I create a sample php program like "Hello", it works.

But when I do a simple sql connect:

<?php
$conn = mysql_connect("localhost","root","tntboy");
echo $conn;
?>

I get nothing! Nothing at all comes up on the screen.
Any ideas? Why is it not connecting to the database?

UPDATE Not Working.
The page that sends information to the following page has a series of text inputs. The name of the input corresponds to the name of the column in the in the mysql table. There's also a hidden input, "id", that supplies the unique id number of the entry.

My script to enter data into the table and to view the table work fine. The update script runs without any errors, but doesn't alter any values. I also tried printing the value of $query and everything appears to be fine. Anyone know why this is happening? Code:

Foreign Key Not Working
I am using mysql server 5 version, and heidisql version 3.0.

i am adding a foreign key to an already existing table

the exact command is

ALTER TABLE mailer_contact_details ADD CONSTRAINT mcd_fk FOREIGN KEY (address_id) REFERENCES addresses(id) on delete set null

there is no error, and i am getting query ok, but the foreign key
functionality is not happening, i am able to enter the value in the
child table which is not present in the parent table, and still no error
message is getting displayed

i even created an index for address_id field, and then again giving
the above command, still foreign key functionality is not working..

where is the problem?