SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 33
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Date and the hour not exceeding one hour

    Hi everyone, I need your help.

    This is a table doTbl_A MySQL:

    Code:
    ID	District 	Date	 	Hour	 	Es 	Zn	 	Installation 	ID Lines 	Lines 		Description 
    1	VT 		17/10/2009 	12:02:28 	DM40 	DM4QU1 		VIT	 	DM4043806 	CAALE 		SCAT DEF D.TE 1 S 
    2	PE 		16/10/2009 	19:04:55 	DM70 	DM7OU2 		SCA 		DM7043005 	MANOPPELLO 	SCAT DEF D.TE 1 S 
    3	PE 		16/10/2009 	17:53:40 	DM70 	DM7OU2 		SCA 		DM7043005 	MANOPPELLO 	SCAT DEF D.TE 1 S 
    4	TE 		16/10/2009 	14:33:47 	DM70 	DM7PU2 		GIU	 	DM7057310 	COLO.SPIAG 	SCAT DEF MAX.I 3 S 
    5	TE 		16/10/2009 	14:11:02 	DM70 	DM7PU2 		GIU	 	DM7057310 	COLO.SPIAG 	SCAT DEF MAX.I 3 S 
    6	TE 		16/10/2009 	13:24:34 	DM70 	DM7PU2 		GIU	 	DM7057310 	COLO.SPIAG 	SCAT DEF D.TE 1 S 
    7	CH 		16/10/2009 	08:43:51 	DM70 	DM7MU1 		MIG	 	DM7048207 	ARTIGIANAL 	SCAT DEF MAX.I 1 S 
    8	VT 		16/10/2009 	05:08:43 	DM40 	DM4QU2 		BAS	 	DM4043601 	CAPRANICA 	SCAT DEF MAX.I 2 S 
    9	FR 		16/10/2009 	00:14:05 	DM20 	DM2M00 		CAS	 	DM2041405 	S.BIAGIO 	SCAT DEF MAX.I 2 S

    I need import in the doTbl_B only this rows:

    Code:
    ID	District 	Date	 	Hour	 	Es 	Zn	 	Installation 	ID Lines 	Lines 		Description 
    1	VT 		17/10/2009 	12:02:28 	DM40 	DM4QU1 		VIT	 	DM4043806 	CAALE 		SCAT DEF D.TE 1 S 
    2	PE 		16/10/2009 	19:04:55 	DM70 	DM7OU2 		SCA 		DM7043005 	MANOPPELLO 	SCAT DEF D.TE 1 S 
    3	PE 		16/10/2009 	17:53:40 	DM70 	DM7OU2 		SCA 		DM7043005 	MANOPPELLO 	SCAT DEF D.TE 1 S 
    6	TE 		16/10/2009 	13:24:34 	DM70 	DM7PU2 		GIU	 	DM7057310 	COLO.SPIAG 	SCAT DEF D.TE 1 S 
    7	CH 		16/10/2009 	08:43:51 	DM70 	DM7MU1 		MIG	 	DM7048207 	ARTIGIANAL 	SCAT DEF MAX.I 1 S 
    8	VT 		16/10/2009 	05:08:43 	DM40 	DM4QU2 		BAS	 	DM4043601 	CAPRANICA 	SCAT DEF MAX.I 2 S 
    9	FR 		16/10/2009 	00:14:05 	DM20 	DM2M00 		CAS	 	DM2041405 	S.BIAGIO 	SCAT DEF MAX.I 2 S
    I exclude of import the ID number 4 and number 5 (doTbl_A) because the date and the hour not exceeding one hour compared to ID number 6.

    I take always only the older record when there is included to an hour.

    Can you help me?

    Many thanks
    Mike

  2. #2
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    In view with a SELECT query for table doTbl_A ?
    Is not possible exclude the ID number 4 and number 5 (doTbl_A) ?

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I try this query:

    Code:
    SELECT a.* FROM doTbl_A a
    JOIN doTbl_A b ON a.ID=b.id-1
    WHERE 1 = 1 AND
    STR_TO_DATE(CONCAT(a.Date, ' ', a.hour)) >= STR_TO_DATE(CONCAT(b.Date, ' ', b.hour));
    But I have this 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 ') >= STR_TO_DATE(CONCAT(b.Date, ' ', b.hour))' at line 4
    Why?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    STR_TO_DATE requires a format field
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    STR_TO_DATE requires a format field
    thanks x your answer.

    1) field date = date YYYY-MM-DD
    2) field hour = time HH:MM:SS


    Code:
    SELECT a.* FROM doTbl_A a JOIN doTbl_A b 
    ON a.ID=b.id-1 
    WHERE 1 = 1 AND 
    STR_TO_DATECONCAT(a.Date, ' ', a.hour) >= STR_TO_DATECONCAT(b.Date, ' ', b.hour);
    Error:

    FUNCTION myDBMySQL.STR_TO_DATECONCAT does not exist


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    STR_TO_DATE(  CONCAT(a.Date,' ',a.hour) , 'format' )
    you should look up the format code in da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    STR_TO_DATE(  CONCAT(a.Date,' ',a.hour) , 'format' )
    you should look up the format code in da manual
    Ok, thanks x your help.

    Code:
    SELECT a.* FROM doTbl_A a 
    JOIN doTbl_A b ON a.ID=b.id-1 WHERE 1 = 1 
    AND STR_TO_DATE(CONCAT(a.Date, ' ', a.hour), '%Y-%m-%d %h:%i:%s' ) >= STR_TO_DATE(CONCAT(b.Date, ' ', b.hour), '%Y-%m-%d %h:%i:%s' );
    Error:

    Incorrect datetime value: '2009-10-17 12:02:28' for function str_to_time


  8. #8
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT a.* FROM doTbl_A a 
    JOIN doTbl_A b ON a.ID=b.id-1 
    WHERE 1 = 1 AND 
    STR_TO_DATE(CONCAT(a.Date, ' ', a.hour), '%Y-%m-%d %H:%i:%S' ) >= STR_TO_DATE(CONCAT(b.Date, ' ', b.hour), '%Y-%m-%d %H:%i:%S' );
    Not error but 0 rows fetched...

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    Not error but 0 rows fetched...
    okay, what do you get for this --
    Code:
    SELECT COUNT(*) 
    FROM doTbl_A a 
    JOIN doTbl_A b 
    ON a.ID=b.id-1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...

  11. #11
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, what do you get for this --
    Code:
    SELECT COUNT(*) 
    FROM doTbl_A a 
    JOIN doTbl_A b 
    ON a.ID=b.id-1
    Code:
    SELECT COUNT(*) 
    FROM doTbl_A a 
    JOIN doTbl_A b ON a.ID=b.ID-1 
    
    COUNT(*) = 8

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, what do you get for this --
    Code:
    SELECT a.Date, a.hour, b.Date, b.hour
    FROM doTbl_A a 
    JOIN doTbl_A b 
    ON a.ID=b.id-1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, what do you get for this --
    Code:
    SELECT a.Date, a.hour, b.Date, b.hour
    FROM doTbl_A a 
    JOIN doTbl_A b 
    ON a.ID=b.id-1
    Output:

    Code:
    Date		Hour		Date		Hour
    2009/10/16	00:14:05	2009/10/16	05:08:43
    2009/10/16	05:08:43	2009/10/16	08:43:51
    2009/10/16	08:43:51	2009/10/16	13:24:34
    2009/10/16	13:24:34	2009/10/16	14:11:02
    2009/10/16	14:11:02	2009/10/16	14:23:47
    2009/10/16	14:23:47	2009/10/16	17:53:40
    2009/10/16	17:53:40	2009/10/16	19:04:55
    2009/10/16	19:04:55	2009/10/17	12:02:28

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, clearly something is wrong with your data

    your "hour" column appears to contain both a date and a time

    also, it is pretty clear that your dates are ~not~ in '%Y-%m-%d' format

    no wonder STR_TO_DATE isn't working correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, clearly something is wrong with your data

    your "hour" column appears to contain both a date and a time

    also, it is pretty clear that your dates are ~not~ in '%Y-%m-%d' format

    no wonder STR_TO_DATE isn't working correctly
    OK.

    I changed :

    1) field date = date YYYY-MM-DD
    2) field hour = VARCHAR (10)

    And now I have this output:

    Code:
    Date		Hour		Date		Hour
    2009-10-16	00:14:05	2009-10-16	05:08:43
    2009-10-16	05:08:43	2009-10-16	08:43:51
    2009-10-16	08:43:51	2009-10-16	13:24:34
    2009-10-16	13:24:34	2009-10-16	14:11:02
    2009-10-16	14:11:02	2009-10-16	14:23:47
    2009-10-16	14:23:47	2009-10-16	17:53:40
    2009-10-16	17:53:40	2009-10-16	19:04:55
    2009-10-16	19:04:55	2009-10-17	12:02:28

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, now the data looks okay

    do you understand now why you got 0 results?

    none of the "a" datetimes is actually greater than the "b" datetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    OK.

    But my problem is excluded of the view the rows when the date and the hour not exceeding one hour compared to ID-1 ( for the same District ).

    I need this output:

    Code:
    Date		Hour		Date		Hour
    2009-10-16	00:14:05	2009-10-16	05:08:43
    2009-10-16	05:08:43	2009-10-16	08:43:51
    2009-10-16	08:43:51	2009-10-16	13:24:34
    2009-10-16	14:23:47	2009-10-16	17:53:40
    2009-10-16	17:53:40	2009-10-16	19:04:55
    2009-10-16	19:04:55	2009-10-17	12:02:28
    Because the rows:

    Code:
    2009-10-16	13:24:34	2009-10-16	14:11:02
    2009-10-16	14:11:02	2009-10-16	14:23:47
    Not exceeding one hour compared to ID-1:

    Code:
    2009-10-16	08:43:51	2009-10-16	13:24:34
    Code:
    Dist	Date		Hour		Dist	Date		Hour		VIEW	
    FR 	2009-10-16	00:14:05	VT 	2009-10-16	05:08:43	YES	
    VT 	2009-10-16	05:08:43	CH 	2009-10-16	08:43:51	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    CH 	2009-10-16	08:43:51	TE 	2009-10-16	13:24:34	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    TE 	2009-10-16	13:24:34	TE 	2009-10-16	14:11:02	NO	ID-1 > 1 HOUR AND SAME DISTRICT 'TE'
    TE 	2009-10-16	14:11:02	TE 	2009-10-16	14:23:47	NO	ID-1 > 1 HOUR AND SAME DISTRICT 'TE'
    TE 	2009-10-16	14:23:47	PE 	2009-10-16	17:53:40	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    PE 	2009-10-16	17:53:40	PE 	2009-10-16	19:04:55	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    PE 	2009-10-16	19:04:55	VT 	2009-10-17	12:02:28	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    Do you understand me ?

  18. #18
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Sorry:

    Code:
    Dist	Date		Hour		Dist	Date		Hour		VIEW	
    FR 	2009-10-16	00:14:05	VT 	2009-10-16	05:08:43	YES	
    VT 	2009-10-16	05:08:43	CH 	2009-10-16	08:43:51	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    CH 	2009-10-16	08:43:51	TE 	2009-10-16	13:24:34	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    TE 	2009-10-16	13:24:34	TE 	2009-10-16	14:11:02	NO	ID-1 < 1 HOUR AND SAME DISTRICT 'TE'
    TE 	2009-10-16	14:11:02	TE 	2009-10-16	14:23:47	NO	ID-1 < 1 HOUR AND SAME DISTRICT 'TE'
    TE 	2009-10-16	14:23:47	PE 	2009-10-16	17:53:40	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    PE 	2009-10-16	17:53:40	PE 	2009-10-16	19:04:55	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    PE 	2009-10-16	19:04:55	VT 	2009-10-17	12:02:28	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't understand your problem

    can't you just read all the rows into your application language (php?) in the correct sequence and do the "not exceeding one hour" logic there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, i don't understand your problem

    can't you just read all the rows into your application language (php?) in the correct sequence and do the "not exceeding one hour" logic there?
    Yes, but I use ASP not PHP ...

    Sorry for my english but I posted this output for explain my problem, do not understand?:

    Code:
    Dist	Date		Hour		Dist	Date		Hour		VIEW	
    FR 	2009-10-16	00:14:05	VT 	2009-10-16	05:08:43	YES	
    VT 	2009-10-16	05:08:43	CH 	2009-10-16	08:43:51	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    CH 	2009-10-16	08:43:51	TE 	2009-10-16	13:24:34	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    TE 	2009-10-16	13:24:34	TE 	2009-10-16	14:11:02	NO	ID-1 < 1 HOUR AND SAME DISTRICT 'TE'
    TE 	2009-10-16	14:11:02	TE 	2009-10-16	14:23:47	NO	ID-1 < 1 HOUR AND SAME DISTRICT 'TE'
    TE 	2009-10-16	14:23:47	PE 	2009-10-16	17:53:40	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    PE 	2009-10-16	17:53:40	PE 	2009-10-16	19:04:55	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT
    PE 	2009-10-16	19:04:55	VT 	2009-10-17	12:02:28	YES	ID-1 > 1 HOUR AND DIFFERENT DISTRICT

  21. #21
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I can not believe the tool power MySQL can not help...

  22. #22
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I try this:

    Code:
    SELECT ( SELECT a.* 
                           , b.* 
                           , DateDiff(n,a.HOUR,b.HOUR) AS strDiff 
    FROM doTbl_A a 
    JOIN doTbl_A b 
       ON a.DIST=b.DIST 
          AND a.DATE=b.DATE ORDER BY a.ID ) 
             FROM doTbl_A ORDER BY ID ASC
    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 'a.HOUR) AS strDiff FROM doTbl_A a JOIN doTbl_A b ON a.DIST=b.DIST AND a.DATE' at line 1

    Why?

  23. #23
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    datediff() takes two arguments, not three.

  24. #24
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    datediff() takes two arguments, not three.
    Ok two arguments, thanks.

    But:

    Code:
    SELECT * ( SELECT a.* 
    , b.* 
    , DATEDIFF(b.HOUR,a.HOUR) AS strDiff 
    FROM doTbl_A a 
    JOIN doTbl_A b 
    ON a.DIST=b.DIST 
    AND a.DATE=b.DATE ORDER BY a.ID ) 
    FROM doTbl_A ORDER BY ID ASC

    [mysqld-5.0.45-community-nt-log]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 '( SELECT a.* , b.* , DATEDIFF(b.HOUR,a.HOUR) AS strDiff FROM doTbl_A a JOI' at line 1

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you are allowed to put a subquery into the outer query's SELECT clause only if that subquery is guaranteed to return at most one row

    your subquery returns a result set
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •