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 - 11: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 )

  • Moderators
  • 16,940 posts
  • LocationCheshire, UK

Posted 17 January 2013 - 12:05 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.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.







moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Wiranata

Wiranata

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 17 January 2013 - 10: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 )

  • Moderators
  • 16,940 posts
  • LocationCheshire, UK

Posted 17 January 2013 - 12:04 PM

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.







moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 Wiranata

Wiranata

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 17 January 2013 - 04:10 PM

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 )

  • Moderators
  • 16,940 posts
  • LocationCheshire, UK

Posted 17 January 2013 - 07: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);

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.







moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 Wiranata

Wiranata

    Newbie

  • New Members
  • Pip
  • 8 posts

Posted 17 January 2013 - 08: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 )

  • Moderators
  • 16,940 posts
  • LocationCheshire, UK

Posted 17 January 2013 - 10:17 PM

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.







moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users