Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2014
    Posts
    8

    Unanswered: Difficult date,time, and miliseconds query

    I have this table:

    Time_Stamp ,Time_Stamp_ms
    '2014-04-11 13:33:24', '879', '5555', '4444', '3333', '2222', '1111', '123', '1', 'text'
    '2014-04-11 13:33:24', '899', '5555', '4444', '3333', '2222', '1111', '123', '2', 'text'
    '2014-04-11 13:33:24', '919', '5555', '4444', '3333', '2222', '1111', '123', '3', 'text'
    '2014-04-11 13:33:24', '939', '5555', '4444', '3333', '2222', '1111', '123', '4', 'text'
    '2014-04-11 13:33:24', '959', '5555', '4444', '3333', '2222', '1111', '123', '5', 'text'
    '2014-04-11 13:33:24', '979', '5555', '4444', '3333', '2222', '1111', '123', '6', 'text'
    '2014-04-11 13:33:24', '999', '5555', '4444', '3333', '2222', '1111', '123', '7', 'text'
    '2014-04-11 13:33:25', '19', '5555', '4444', '3333', '2222', '1111', '123', '8', 'text'
    '2014-04-11 13:33:25', '39', '5555', '4444', '3333', '2222', '1111', '123', '9', 'text'
    '2014-04-11 13:33:25', '59', '5555', '4444', '3333', '2222', '1111', '123', '10', 'text'
    '2014-04-11 13:33:25', '79', '5555', '4444', '3333', '2222', '1111', '123', '11', 'text'
    '2014-04-11 13:33:25', '99', '5555', '4444', '3333', '2222', '1111', '123', '12', 'text'

    I want to get rows from one date, time and ms to another.

    Now this would work great:

    SELECT * FROM objects WHERE (date_field BETWEEN '2014-04-11 13:33:24' AND '2014-04-11 13:33:25')

    But I need it to be more filtered out by ms....How could I do this? I tried between with the ms but it doesnt work.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE  date_field >= '2014-04-11 13:33:24'
    AND    date_field <  '2014-04-11 13:33:25'
    ?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because MySQL discards any fractional seconds for storage, you need to explicitly code for them when needed.
    Code:
    SELECT * 
       FROM objects 
       WHERE  (  '2014-04-11 13:33:24' < date_field
             OR ('2014-04-11 13:33:24' = date_field AND '500' <= ms_column))
          AND (  '2014-04-11 13:33:25' < date_field
             OR ('2014-04-11 13:33:25' = date_field AND ms_column < '500'))
    Note that storing the milliseconds as strings is a recipe for problems. String comparisons are emphatically NOT integer/date comparisons!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2014
    Posts
    8
    This seems to work:

    Code:
    SELECT * FROM table WHERE (concat(Time_Stamp,'.',Time_Stamp_ms) BETWEEN '2014-04-11 13:33:24.958' AND '2014-04-11 13:33:25.219');

  5. #5
    Join Date
    Apr 2014
    Posts
    8
    This
    Code:
    SELECT * FROM table WHERE (concat(Time_Stamp,'.',Time_Stamp_ms) BETWEEN '2014-04-11 13:33:24.879' AND '2014-04-11 13:33:25.79');
    Almost works; Seems to have issues between "790", "079" and "79"

    BTW, the "Time_Stamp_ms" is a integer, not a string.

  6. #6
    Join Date
    Apr 2014
    Posts
    8
    [code]
    SELECT *
    FROM table
    WHERE ( '2014-04-11 13:33:24' < Time_Stamp
    OR ('2014-04-11 13:33:24' = Time_Stamp AND '879' <= Time_Stamp_ms))
    AND ( '2014-04-11 13:33:25' < Time_Stamp
    OR ('2014-04-11 13:33:25' = Time_Stamp AND Time_Stamp_ms < '79'))
    [/quote]

    Doesn't work either. Gives very strange, not normal results.

    Im thinking about a subquery being needed?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by riahc3 View Post
    This
    Code:
    SELECT *
       FROM table
       WHERE (concat(Time_Stamp,'.',Time_Stamp_ms)
          BETWEEN '2014-04-11 13:33:24.879' AND '2014-04-11 13:33:25.79');
    Almost works; Seems to have issues between "790", "079" and "79"

    BTW, the "Time_Stamp_ms" is a integer, not a string.
    You are creating a string expression using Concat(), and comparing that string expression to two other strings. Methinks that ain't gonna work well, but your mileage may vary!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As long as the milliseconds are stored as integers, try:
    Code:
    SELECT * 
       FROM table
       WHERE ( '2014-04-11 13:33:24' < Time_Stamp
             OR ('2014-04-11 13:33:24' = Time_Stamp AND 879 <= Time_Stamp_ms))
          AND ( '2014-04-11 13:33:25' < Time_Stamp
             OR ('2014-04-11 13:33:25' = Time_Stamp AND Time_Stamp_ms < 79))
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Apr 2014
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    As long as the milliseconds are stored as integers, try:
    Code:
    SELECT * 
       FROM table
       WHERE ( '2014-04-11 13:33:24' < Time_Stamp
             OR ('2014-04-11 13:33:24' = Time_Stamp AND 879 <= Time_Stamp_ms))
          AND ( '2014-04-11 13:33:25' < Time_Stamp
             OR ('2014-04-11 13:33:25' = Time_Stamp AND Time_Stamp_ms < 79))
    -PatP
    Yup, they are stored as integers.

    That query gives me:

    Code:
    Time_Stamp, Time_Stamp_ms
    '2014-04-11 13:33:25', '19', '5555', '4444', '3333', '2222', '1111', '123', '8', 'text'
    '2014-04-11 13:33:25', '39', '5555', '4444', '3333', '2222', '1111', '123', '9', 'text'
    '2014-04-11 13:33:25', '59', '5555', '4444', '3333', '2222', '1111', '123', '10', 'text'
    '2014-04-11 13:33:26', '19', '5555', '4444', '3333', '2222', '1111', '123', '58', 'text'
    '2014-04-11 13:33:26', '39', '5555', '4444', '3333', '2222', '1111', '123', '59', 'text'
    '2014-04-11 13:33:26', '59', '5555', '4444', '3333', '2222', '1111', '123', '60', 'text'
    '2014-04-11 13:33:26', '79', '5555', '4444', '3333', '2222', '1111', '123', '61', 'text'
    '2014-04-11 13:33:26', '99', '5555', '4444', '3333', '2222', '1111', '123', '62', 'text'
    '2014-04-11 13:33:26', '119', '5555', '4444', '3333', '2222', '1111', '123', '63', 'text'
    '2014-04-11 13:33:26', '139', '5555', '4444', '3333', '2222', '1111', '123', '64', 'text'
    '2014-04-11 13:33:26', '159', '5555', '4444', '3333', '2222', '1111', '123', '65', 'text'
    '2014-04-11 13:33:26', '179', '5555', '4444', '3333', '2222', '1111', '123', '66', 'text'
    '2014-04-11 13:33:26', '199', '5555', '4444', '3333', '2222', '1111', '123', '67', 'text'
    '2014-04-11 13:33:26', '219', '5555', '4444', '3333', '2222', '1111', '123', '68', 'text'
    '2014-04-11 13:33:26', '239', '5555', '4444', '3333', '2222', '1111', '123', '69', 'text'
    '2014-04-11 13:33:26', '259', '5555', '4444', '3333', '2222', '1111', '123', '70', 'text'
    '2014-04-11 13:33:26', '279', '5555', '4444', '3333', '2222', '1111', '123', '71', 'text'
    '2014-04-11 13:33:26', '299', '5555', '4444', '3333', '2222', '1111', '123', '72', 'text'
    '2014-04-11 13:33:26', '319', '5555', '4444', '3333', '2222', '1111', '123', '73', 'text'
    '2014-04-11 13:33:26', '339', '5555', '4444', '3333', '2222', '1111', '123', '74', 'text'
    '2014-04-11 13:33:26', '359', '5555', '4444', '3333', '2222', '1111', '123', '75', 'text'
    '2014-04-11 13:33:26', '379', '5555', '4444', '3333', '2222', '1111', '123', '76', 'text'
    '2014-04-11 13:33:26', '399', '5555', '4444', '3333', '2222', '1111', '123', '77', 'text'
    '2014-04-11 13:33:26', '419', '5555', '4444', '3333', '2222', '1111', '123', '78', 'text'
    '2014-04-11 13:33:26', '439', '5555', '4444', '3333', '2222', '1111', '123', '79', 'text'
    '2014-04-11 13:33:26', '459', '5555', '4444', '3333', '2222', '1111', '123', '80', 'text'
    '2014-04-11 13:33:26', '479', '5555', '4444', '3333', '2222', '1111', '123', '81', 'text'
    '2014-04-11 13:33:26', '499', '5555', '4444', '3333', '2222', '1111', '123', '82', 'text'
    '2014-04-11 13:33:26', '519', '5555', '4444', '3333', '2222', '1111', '123', '83', 'text'
    '2014-04-11 13:33:26', '539', '5555', '4444', '3333', '2222', '1111', '123', '84', 'text'
    '2014-04-11 13:33:26', '559', '5555', '4444', '3333', '2222', '1111', '123', '85', 'text'
    '2014-04-11 13:33:26', '579', '5555', '4444', '3333', '2222', '1111', '123', '86', 'text'
    '2014-04-11 13:33:26', '599', '5555', '4444', '3333', '2222', '1111', '123', '87', 'text'
    '2014-04-11 13:33:26', '619', '5555', '4444', '3333', '2222', '1111', '123', '88', 'text'
    '2014-04-11 13:33:26', '639', '5555', '4444', '3333', '2222', '1111', '123', '89', 'text'
    '2014-04-11 13:33:26', '659', '5555', '4444', '3333', '2222', '1111', '123', '90', 'text'
    '2014-04-11 13:33:26', '679', '5555', '4444', '3333', '2222', '1111', '123', '91', 'text'
    '2014-04-11 13:33:26', '699', '5555', '4444', '3333', '2222', '1111', '123', '92', 'text'
    '2014-04-11 13:33:26', '719', '5555', '4444', '3333', '2222', '1111', '123', '93', 'text'
    '2014-04-11 13:33:26', '739', '5555', '4444', '3333', '2222', '1111', '123', '94', 'text'
    '2014-04-11 13:33:26', '759', '5555', '4444', '3333', '2222', '1111', '123', '95', 'text'
    '2014-04-11 13:33:26', '779', '5555', '4444', '3333', '2222', '1111', '123', '96', 'text'
    '2014-04-11 13:33:26', '799', '5555', '4444', '3333', '2222', '1111', '123', '97', 'text'
    '2014-04-11 13:33:26', '819', '5555', '4444', '3333', '2222', '1111', '123', '98', 'text'
    '2014-04-11 13:33:26', '839', '5555', '4444', '3333', '2222', '1111', '123', '99', 'text'
    '2014-04-11 13:33:26', '859', '5555', '4444', '3333', '2222', '1111', '123', '100', 'text'
    '2014-04-11 13:33:26', '879', '5555', '4444', '3333', '2222', '1111', '123', '101', 'text'
    '2014-04-11 13:33:26', '899', '5555', '4444', '3333', '2222', '1111', '123', '102', 'text'
    '2014-04-11 13:33:26', '919', '5555', '4444', '3333', '2222', '1111', '123', '103', 'text'
    '2014-04-11 13:33:26', '939', '5555', '4444', '3333', '2222', '1111', '123', '104', 'text'
    '2014-04-11 13:33:26', '959', '5555', '4444', '3333', '2222', '1111', '123', '105', 'text'
    '2014-04-11 13:33:26', '979', '5555', '4444', '3333', '2222', '1111', '123', '106', 'text'
    '2014-04-11 13:33:26', '999', '5555', '4444', '3333', '2222', '1111', '123', '107', 'text'
    '2014-04-11 13:33:27', '19', '5555', '4444', '3333', '2222', '1111', '123', '108', 'text'
    etc.

    Just in case, the query should give me all results that happened between 2014-04-11 13:33:24.879 and 2014-04-11 13:33:25.79 and as you saw in the first post, I have data for it....

    Both Time_Stamp and Time_Stamp_ms are my primary keys.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please investigate "<" or ">".

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Argh! Tonkuma caught a formatting error in my code which caused the incorrect results!
    Code:
    SELECT * 
       FROM table
       WHERE (   '2014-04-11 13:33:24' < Time_Stamp
             OR ('2014-04-11 13:33:24' = Time_Stamp AND 879 <= Time_Stamp_ms))
          AND (  Time_Stamp < '2014-04-11 13:33:25'
             OR ('2014-04-11 13:33:25' = Time_Stamp AND Time_Stamp_ms < 79))
    Thanks Tonkuma!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Apr 2014
    Posts
    8
    Quote Originally Posted by tonkuma View Post
    Please investigate "<" or ">".
    I think you resolved my problem somewhere else :P

    Thank you

Posting Permissions

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