Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2001
    South Africa

    Unanswered: declaring temp variables in selects ???

    Is there a way in mysql to do the following:

    declare @dt1 = select max(datetime_col) - interval 300 second from tbl
    declare @dt2 = select max(datetime_col) from tbl
    select * from table where datetime_col >= @dt1 and < @dt2

    ie: I want the last 5 minutes (according to the datetime_col) from the table ???

    Many thanks

  2. #2
    Join Date
    Dec 2002

    Sounds like you want subselects...

    From the looks of it you want to be able to use subselects, which I don't believe are supported in MySQL yet (I could be wrong as I haven't done mysql in a while). Is there a particular reason you are trying to do:
    select * from table where datetime_col >= @dt1 and < @dt2 , are you specifically trying to exclude the last item(s) (by doing < @dt2)?

    If so I don't think you need a total of 3 mysql queries I think you can break it down to 2. Just grabbing the highest date and then querying based on data that you derive from that.

    I'm not sure what language you're using for all this, but basically just grab the highest date, calculate and store what 5 minutes before that was and then

    SELECT * FROM tbl WHERE datetime_col > [var holding highest date - 5 minutes] AND datetime_col != [var holding highest date]

    Hope this helps
    (I'm only available at the email address provided in my profile on weekdays, if you have questions or advice, during off hours use AIM). Also any views I provide here or on my website are mine and not representative of any views of my work, family, friends and sometimes even myself.

Posting Permissions

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