Professional Web Applications Themes

Pulling data using NOW() and BETWEEN - MySQL

I need more coffee..... I have a 'DATE' column [NOW()] so my records are timestamped. Can't quite figure out the correct query for pulling records between now and a given period of time (24 hours, ten minutes, whatever...) What am I missing for that last bit? SELECT * FROM `my_table` WHERE `my_date` BETWEEN NOW() AND ??? TIA. ../JLK...

  1. #1

    Default Pulling data using NOW() and BETWEEN

    I need more coffee.....

    I have a 'DATE' column [NOW()] so my records are timestamped. Can't
    quite figure out the correct query for pulling records between now and
    a given period of time (24 hours, ten minutes, whatever...) What am I
    missing for that last bit?


    SELECT * FROM `my_table` WHERE `my_date` BETWEEN NOW() AND ???

    TIA.
    ../JLK

    Akhenaten Guest

  2. #2

    Default Re: Pulling data using NOW() and BETWEEN

    On 24 May, 11:58, Akhenaten <com> wrote: 

    It's not what you're missing from the last bit, it's the first bit
    that's the problem.

    A DATE column will only hold a date. To work with intervals of
    minutes, you need a DATETIME or TIMESTAMP column. If you have a DATE
    column, your records are date stamped, not timestamped. Once you have
    a timestamp you can use the TIMESTAMPADD() function to get the
    interval.

    You will find details of this hidden away in the manual. The hide it
    really well as they put it in the section called "Date and Time
    Functions". Go figure. Could they have made it more difficult to find?

    Captain Guest

  3. #3

    Default Re: Pulling data using NOW() and BETWEEN

    On May 24, 6:58 am, Akhenaten <com> wrote: 

    One of the ways to achieve this is to use the DATE_SUB function.

    http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-sub

    Nb.

    select * from `my_table` where `my_date` > DATE_SUB(NOW(), INTERVAL 1
    DAY)
    and `my_date` <
    DATE_SUB(NOW(), INTERVAL 1 HOUR) ;


    Regards,
    Bill

    Bill Guest

  4. #4

    Default Re: Pulling data using NOW() and BETWEEN

     


    Correction - the column is in a timestamp format, just unsure as how
    to reference my column as the date. Those nice little date/time
    functions (ref'd here: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html)
    are severely lacking in both explanation and example (IMHO).

    Akhenaten Guest

  5. #5

    Default Re: Pulling data using NOW() and BETWEEN

    On May 24, 6:13 am, Bill Turczyn <com> wrote: 


    >[/ref]
     

    Thank you Bill...just the example I needed.

    Akhenaten Guest

Similar Threads

  1. Error pulling local data.
    By Gene in forum Macromedia Flex General Discussion
    Replies: 3
    Last Post: June 25th, 06:31 PM
  2. Pulling in data from system ODBC
    By Rusty in forum MySQL
    Replies: 2
    Last Post: May 18th, 01:54 PM
  3. Pulling data across domains
    By CreativeStride in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: September 23rd, 04:50 AM
  4. PHP/Oracle - Pulling data into array
    By Philip in forum PHP Development
    Replies: 9
    Last Post: February 18th, 06:15 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139