兩個table的時間該如何比對?

edited 十一月 -1 in 資料庫
hi

請問一下,以下這種情形該如何比對:

Table A ,有欄位a1 ,格式為DateTime
Table B ,有欄位b1(StartTime),b2(EndTime),格式為DateTime

若要以A.a1為基準,誤差5分鐘,去搜尋在B.b1與B.b2之間的資料,該如何作呢?

謝謝


原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=4829

評論

  • edited 一月 2010
    1. join 條件?
    2. 誤差5分鐘定義?
    3. 給測試數值
  • edited 一月 2010
    誤差5分如A.a1時間為2010/01/10 13:00:00 ,須找出B.b1與B.b2之間有2010/01/10 12:55:00~2010/01/10 13:05:00的資料

    p.s用mysql

    謝謝
  • edited 一月 2010
    SELECT * FROM A
    INNER JOIN B ON A.X = B.Y
    WHERE TIMESTAMPDIFF(MINUTE, A.a1, B.b1) <= 5
    OR TIMESTAMPDIFF(MINUTE, A.a1, B.b2) <= 5
  • edited 一月 2010
    謝謝回答,但目前是使用mysql4.1沒有support TIMESTAMPDIFF
    如果是用mysql4.1,再配合php該怎麼做呢?
  • edited 一月 2010
    你可以分成兩次查詢
  • edited 一月 2010
    對不起,不懂您的意思, 可否指點一下??
  • edited 一月 2010
    sorry,如果我是要以A.a1為基準,找落於B.b1與B.b2的那筆資料

    SELECT * FROM A
    INNER JOIN B ON A.X = B.Y
    WHERE TIMESTAMPDIFF(MINUTE, A.a1, B.b1) <= 5
    OR TIMESTAMPDIFF(MINUTE, A.a1, B.b2) <= 5

    這個語法是否也適用?

    ex: 以A.a1為基準,誤差一分的條件的話,則需找到B.SN為2與3兩筆資料
    A.a1為2010-01-01 00:00:00
    B 的結構SN,b1,b2
    1,2010-01-01 01:03:00,2010-01-01 01:05:00
    2,2009-12-31 23:59:00,2009-12-31 23:59:59
    3,2010-01-01 01:01:00,2010-01-01 01:01:59


    這樣該怎麼做呢?
  • edited 一月 2010
    看不出來兩個問題的差異。

    分兩次查詢意思是先在 PHP 取得 A.a1 ,再透過這個數值查詢 B.b1與B.b2之間的資料,邏輯上應該更單純
  • edited 一月 2010
    請容許小的好奇一下,enrouge你要的功能是只有A表單第一筆資料比對B表單第一筆..A第二筆對B第二筆...
    還是A資料表每一筆資料都要跟B表單全部的b1.b2比對?能否將資料表的時間格式改用INT(10)?

    想不到有TIMESTAMPDIFF這個可以用!5.0版真的比4.0好很大Q_Q
  • edited 一月 2010
    是A資料表每一筆資料都要跟B表單全部的b1.b2比對?找出A.a1落於b1.b2中最接近的資料.但A資料表的時又不會跟B資料表的時間同步,所以必需以A的時間會基準做誤差比對
  • edited 一月 2010
    如果不能用TIMESTAMPDIFF函式的話,
    看能不能利用strtotime()將DATETIME的資料轉成10位數的時間格式(單位秒)
    例如:2009-02-14 07:02:30 就是 1234566150,
    然後去比對資料條件符合
    SELECT B.b1,A.a1,B.b2
    FROM B,A
    WHERE (({strtotime(A.a1)}-{strtotime(B.b1)}) <= 30) AND (({strtotime(B.b2)}-{strtotime(A.a1)}) <= 30)
    的結果,
    其中30就是時間差(單位秒),你可以自己設定或改成變數,
    ....說來抱歉,其實不是100%完全理解你想要的功能,以上參考看看
  • edited 十月 2013
    1.這個問題有點像是攝影記錄,基本上 Table A 與 Table B 沒有關聯性,無法使用 join
    2.mysql4.1 有支援 INTERVAL function
    3.假設 Table k ,有欄位k1 ,格式為DateTime
    Table u ,有欄位u1(StartTime),u2(EndTime),格式為DateTime

    那麼 sql 應該是

    SET @k1='2010-01-10 13:10:00';
    select *
    from u
    where not (((select (@k1+ INTERVAL 5 MINUTE))<u1) or ((select (@k1- INTERVAL 5 MINUTE))>u2))


    假設是用 php 的話 ,
    那麼應該用 while 或 for 迴圈,把上面的 sql 包在裡面,才能顯示關係
    select * from k
    // 跑每一筆 k1 ,while 與法自己查一下吧
    while {
    // run 出每筆 k1

    SET @k1='2010-01-10 13:10:00';
    select *
    from u
    where not (((select (@k1+ INTERVAL 5 MINUTE))<u1) or ((select (@k1- INTERVAL 5 MINUTE))>u2))

    while {
    // run 出 u1,u2

    }

    }

    //再用 php 把 k1,u1,u2 組合變成
    2010-01-10 13:10:00 2010-01-10 12:55:00 2010-01-10 13:05:00
    2010-01-10 13:10:00 2010-01-10 13:04:00 2010-01-10 13:06:00
    2010-01-10 13:10:00 2010-01-10 13:06:00 2010-01-10 13:07:00
    2010-01-10 13:10:00 2010-01-10 12:54:00 2010-01-10 13:06:00
    ==========================================
    2010-01-10 13:13:00 ...
    2010-01-10 13:13:00 ...
    2010-01-10 13:13:00 ...
    ==========================================
    2010-01-10 13:20:00 ...
    2010-01-10 13:20:00 ...

    這樣就可以知道 k1 內的每一筆記錄會對映到 u 內的那幾筆

    大概是這樣做吧,有點複雜;可能沒人看得懂我在說甚麼吧 :(
  • edited 十月 2013
    更正 sql :

    SET @k1='2010-01-10 13:10:00';
    select *
    from u
    where not (((select (@k1+ INTERVAL 5 MINUTE))<u1) or ((select (@k1- INTERVAL 5 MINUTE))>u2))
  • Sorry ,這個系統有點問題;
    正確的 sql 語法被系統吃掉了
  • 版主已修正 bug,上面的 sql 是正確的
Sign In or Register to comment.