Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Need help with a query

    Hello i'am new here. i am looking for help!

    i have a table "Contact" with the following fields

    ID - Number - Timestamp.

    I have the following query

    Select Number from Contact Where DATE_FORMAT('Timestamp','%Y-%m-%d') = '2013-03-13'

    The query returns als numbers in the database with a timestamp of 2013-03-13

    for al the numbers returned by that query i want to look if that number is in the database on the following data's 2013-03-11, 2013-03-12, 2013-03-14 or 2013-03-15.

    When the number is also present on one of the dates there must be a value + 1 of the number is not present on one of these dates another value must be +1.

    IS this all posible in one query. i know have it with 3 query's but it is very slow.

    I hope u can help me.

    THX

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    isn't timestamp a MySQL reserved word
    what data type is it.?

    Assuming you have used the datetime datatype it will be quicker if you could index the column 'timestamp'. Im not to sure if the SQL engine can use an index if you are converting a datetime value into a string.

    another good reason to use the date time functions is that you can use those functions to specify a range

    https://dev.mysql.com/doc/refman/5.5...functions.html

    use the BETWEEN sub clause in the WHERE
    Google

    the only wrinkle is the first value after the BETWEEN MUST be lower than the second value

    so thats going to be something like:_
    SELECT my, comma, separated, list, of, columns FROM mytable
    WHERE mydatecolumn BETWEEN DATE_ADD('2013-03-13', INTERVAL DAY -2) AND DATE_ADD('2013-03-13', INTERVAL DAY +2)
    if you have stored a time element in the column then you may need to tweak the the upper limit as adding 2 days to, say 13 March 2013 will include all rows whose date is less than or equal to 13 March 2013 BUT WILL EXCLUDE all rows which occured on 13 March 2013 after midnight ie 13 March 2013 00:00:00
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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