I want to find the difference in times between the date/times of two
tasks in the same table.
selfJoin table
task_ID task date_time
1 measure board 2004-11-28 15:53:55
2 cut board 2004-11-28 16:08:03
Here is the best SQL statement I can come up with to find how much
time has passed between those two events:
$sql = "SELECT DIFF(first.date _time, second.date_tim e) FROM selfJoin
AS first, selfJoin AS second";
What is the correct way to do this?
(Never mind that there will in fact be many tasks in each table. And
I will also want to know the total time difference between the first
task and the last task.)
Thanks,
-Andrew K 2 2263
Andrew wrote: I want to find the difference in times between the date/times of two tasks in the same table.
selfJoin table task_ID task date_time 1 measure board 2004-11-28 15:53:55 2 cut board 2004-11-28 16:08:03
Here is the best SQL statement I can come up with to find how much time has passed between those two events:
$sql = "SELECT DIFF(first.date _time, second.date_tim e) FROM selfJoin AS first, selfJoin AS second";
DIFF() is not a function in MySQL as far as I know.
Read the docs on date/time functions: http://dev.mysql.com/doc/mysql/en/Da...functions.html
I would suggest this:
SELECT UNIX_TIMESTAMP( second.date_tim e)
- UNIX_TIMESTAMP( first.date_time )
FROM selfJoin AS first, selfJoin AS second
WHERE first.task_ID <> second.task_ID
This returns the difference in seconds between those two date/times,
assuming both are between 1970 and 2037. :-)
I'm assuming you left out any join condition because you want all rows
to be compared to all other rows. It's often handy to add a condition
as I have above, so you don't get each row compared to itself.
Regards,
Bill K.
"Andrew" <ak****@web4000 .com> wrote in message
news:42******** *************** ***@posting.goo gle.com... I want to find the difference in times between the date/times of two tasks in the same table.
selfJoin table task_ID task date_time 1 measure board 2004-11-28 15:53:55 2 cut board 2004-11-28 16:08:03
Here is the best SQL statement I can come up with to find how much time has passed between those two events:
$sql = "SELECT DIFF(first.date _time, second.date_tim e) FROM selfJoin AS first, selfJoin AS second";
What is the correct way to do this?
MySql offers several date/time functions
DATEDIFF and TIMEDIFF.
DATEDIFF Only returns the difference in days, while TIMEDIFF returns the
difference in the following format:
hours:minutes:s econds:millisec onds
But only from MySql 4.1.1
Check http://dev.mysql.com/doc/mysql/en/Da...functions.html This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tech |
last post by:
I have a table tblEmails where
the columns are id,list_id,address_id. I have many lists. I need to
find out
if a couple of lists (list_ids - 1000,1001,1002) have same
address_ids in common or not.
Snaphot
1,1000,1234
|
by: Andrew Mayo |
last post by:
Here's a really weird one for any SQL Server gurus out there...
We have observed (SQL Server 2000) scenarios where a stored procedure
which
(a) begins a transaction
(b) inserts some rows into a table
(c) re-queries another table using a subquery which references the
inserted table (correlated or not)
|
by: jbm05 |
last post by:
Hi,
I'm curious about the computational complexity of a query I have. The
query contains multiple nested self left joins, starting with a simple
select, then doing a self left join with the results, then doing a self
left join with those results, etc. What puzzles me is that the time
required for the query seems to grow exponentially as I add additional
left joins, which I didn't expect. I expected the inner select to
return about 25...
|
by: Shahzad |
last post by:
dear respected gurus,
I would like to knew how to apply append,insert query for a self table
where no primary keys issues.
i do have problem say there are 5 rows of single record, this is data
of motor under one policy. say policy is john2004, so john is owner of
5 cars. now on next year the card has got renew with same
conditions(same amounts all), so i need to insert new recocrd via
query in new policy is john2005, with same...
|
by: davegb |
last post by:
I'm trying to create a self-join table to show the relationship between
employee and supervisor. In another thread, I was advised to create a
SupervisorID in the employee table, a separate Supervisor table, and
join the Supervisor table to the Employee table and a copy of the
Supervisor table to create the self-join. I can't figure out how to do
this from reading Viescas or from researching it here. I can start a
query and create 2 copies...
| |
by: Ev |
last post by:
I have a database table in SQL Server that has a self join. In C# I have a
DataTable with a self-join. I have defined a foreign key constraint on the
DataTable for the self join. The AcceptRejectRule is set to Cascade
Table Structure:
OrderID (Identity field)
OrderDesc
OrderParent (this is the self-join - it is a foreign key to OrderID)
In my code I add several rows to the table, createing a hierarchy of orders
|
by: Jim Andersen |
last post by:
I have a problem with this standard employee-supervisor scenario
For pictures:
http://www.databasedev.co.uk/self-join_query.html
I want to show all employees "belonging" to a specific supervisor.
E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a supervisor
SUP2. EMP4 has supervisor SUP3.
|
by: Russell Warren |
last post by:
Is there any better way to get a list of the public callables of self
other than this?
myCallables =
classDir = dir(self)
for s in classDir:
attr = self.__getattribute__(s)
if callable(attr) and (not s.startswith("_")):
myCallables.append(s) #collect the names (not funcs)
|
by: sks |
last post by:
I have a table that contains keywords (Varchars) each one mapped to a
product. so the database schema is just an id column, product column and
keyword column.
I want to select the products that contain x,y,z keywords. Now if this query
involves many keywords I end up with a massive amount of self joins on the
keywords table, is there a better way to do this than self joins? What would
work perhaps is multiple unions where I could discard...
|
by: braver |
last post by:
Is there any trick to get rid of having to type the annoying,
character-eating "self." prefix everywhere in a class? Sometimes I
avoid OO just not to deal with its verbosity. In fact, I try to use
Ruby anywhere speed is not crucial especially for @ prefix is better-
looking than self.
But things grow -- is there any metaprogramming tricks or whatnot we
can throw on the self?
Cheers,
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |