Jump to content


Photo

find time different 2 table mysql?

time

  • Please log in to reply
7 replies to this topic

#1 Wiranata

Wiranata

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 16 January 2013 - 06:54 PM

hi any body know about find different from 2 table.

example
table 1
start time
12:23:21

table 2
start time
01:12:12

can any body help me..please

#2 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,212 posts
  • LocationCheshire, UK

Posted 16 January 2013 - 07:05 PM

Yes, I can confirm that the time in table Table1 is different from the time in Table2.

Is there anything else? We'll need more information than that if there is.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#3 Wiranata

Wiranata

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 17 January 2013 - 05:36 AM

Yes, I can confirm that the time in table Table1 is different from the time in Table2.

Is there anything else? We'll need more information than that if there is.


yeah... i want make form to find different time where time from 2 different table,
i have

table 1
start time
12:23:21

table 2
start time
01:12:12


then how can i show the difference in HH:MM:SS format?
please help me..i tired about it...i can't sleep in 2 week because this problem
any u need more information again about its?

#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,212 posts
  • LocationCheshire, UK

Posted 17 January 2013 - 07:04 AM

This will find the difference between the two times. As there is no date, the same date is assumed. Hope this is what you meant

CREATE TABLE table1 (
id INT NOT NULL PRIMARY KEY,
starttime TIME
);

CREATE TABLE table2 (
id INT NOT NULL PRIMARY KEY,
starttime TIME
);

INSERT INTO table1 VALUES (1, '12:23:21');
INSERT INTO table2 VALUES (1, '01:12:12');

SELECT t1.starttime as time1, t2.starttime as time2,
  SEC_TO_TIME(TIME_TO_SEC(t1.starttime) - TIME_TO_SEC(t2.starttime)) as diff
FROM table1 t1
INNER JOIN table2 t2 USING (id);

The result from the query was

time1 | time2 | diff
12:23:21 | 01:12:12 | 11:11:09

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 Wiranata

Wiranata

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 17 January 2013 - 11:10 AM

This will find the difference between the two times. As there is no date, the same date is assumed. Hope this is what you meant

CREATE TABLE table1 (
id INT NOT NULL PRIMARY KEY,
starttime TIME
);

CREATE TABLE table2 (
id INT NOT NULL PRIMARY KEY,
starttime TIME
);

INSERT INTO table1 VALUES (1, '12:23:21');
INSERT INTO table2 VALUES (1, '01:12:12');

SELECT t1.starttime as time1, t2.starttime as time2,
SEC_TO_TIME(TIME_TO_SEC(t1.starttime) - TIME_TO_SEC(t2.starttime)) as diff
FROM table1 t1
INNER JOIN table2 t2 USING (id);

The result from the query was

time1 | time2 | diff
12:23:21 | 01:12:12 | 11:11:09


yeah....maybe i should reload my question.
i have 2 form upload file with time. and 2 table to save time.
1 form upload to upload task and 1 form upload to upload answer task
and i have 2 table upload file
CREATE TABLE uploadyask(
id INT NOT NULL PRIMARY KEY,
timeupload TIMESTAMP
);

CREATE TABLE task(
id INT NOT NULL PRIMARY KEY,
time TIMESTAMP
);

i follow your code in my view archives
but nothing happen

#6 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,212 posts
  • LocationCheshire, UK

Posted 17 January 2013 - 02:36 PM

if you are using timestamps (which have a date as well as a time, by the way, unlike your data examples) then

CREATE TABLE table1 (
id INT NOT NULL PRIMARY KEY,
starttime TIMESTAMP
);

CREATE TABLE table2 (
id INT NOT NULL PRIMARY KEY,
starttime TIMESTAMP
);

INSERT INTO table1 VALUES (1, '2012-01-17 12:23:21');
INSERT INTO table2 VALUES (1, '2012-01-17 01:12:12');

SELECT t1.starttime, t2.starttime,
  SEC_TO_TIME(UNIX_TIMESTAMP(t1.starttime) - UNIX_TIMESTAMP(t2.starttime)) as diff
FROM table1 t1
INNER JOIN table2 t2 USING (id);

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#7 Wiranata

Wiranata

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 17 January 2013 - 03:32 PM

if you are using timestamps (which have a date as well as a time, by the way, unlike your data examples) then

CREATE TABLE table1 (
id INT NOT NULL PRIMARY KEY,
starttime TIMESTAMP
);

CREATE TABLE table2 (
id INT NOT NULL PRIMARY KEY,
starttime TIMESTAMP
);

INSERT INTO table1 VALUES (1, '2012-01-17 12:23:21');
INSERT INTO table2 VALUES (1, '2012-01-17 01:12:12');

SELECT t1.starttime, t2.starttime,
SEC_TO_TIME(UNIX_TIMESTAMP(t1.starttime) - UNIX_TIMESTAMP(t2.starttime)) as diff
FROM table1 t1
INNER JOIN table2 t2 USING (id);


thanks for replay..but i've try your code but when i run that code nothing happen
your code is succesfully in mysql not my form to find different time

#8 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,212 posts
  • LocationCheshire, UK

Posted 17 January 2013 - 05:17 PM

Sorry about that but my lack of psychic abilty prevents me from doing any more for you.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com