SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL question: how to us an 'AS' field in the 'WHERE' clause

    Here's my query string:

    PHP Code:
    $sql "SELECT LogSessionID, LogTime, FROM_UNIXTIME
    (LogTime,'%d-%b-%y') AS LogDate 
    What I'd like to do is to extend that query to include (for example)...

    WHERE LogDate='18-Jul-02'

    ...but it appears that any column name that has been renamed using the 'AS' syntax is not available for use in the WHERE clause.

    Can anyone:

    a) confirm (or otherwise) my observation?
    b) suggest a way around this, because it would be real useful to me?

    Thanks in anticipation.
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  2. #2
    SitePoint Enthusiast hex's Avatar
    Join Date
    Jul 2002
    Location
    Cambs, UK
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It should be available for use.
    :: David Speake
    :: EvilWalrus.com Content Dev.
    :: david@evilwalrus.com
    :: david.bigbluebang.com

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    as a general solution, you can go right ahead and use a column alias in the WHERE clause, because that's standard sql

    but, as usual, mysql is a wee bit different:

    6.4.1 SELECT Syntax
    A SELECT expression may be given an alias using AS. The alias is used as the expression's column name and can be used with ORDER BY or HAVING clauses.... It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. See section A.5.4 Problems with alias
    as a general workaround, any expression that you can give an alias to in the SELECT list, you can also use the same expression in the WHERE clause

    so, it's a bit uglier, but this whould work:
    Code:
    select LogSessionID, LogTime
         , FROM_UNIXTIME(LogTime,'%d-%b-%y') AS LogDate
      from YourTable
     where FROM_UNIXTIME(LogTime,'%d-%b-%y') = '18-Jul-02'
    not what you wanted to hear, but there ya go...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Remember spaceman that if you have an index on LogTime it will not be used on your query since you are applying a function to a column in the WHERE clause.

    I would do it the other way, e.g.:
    Code:
    WHERE LogTime = TO_UNIXTIME( '18-Jul-02' )

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    good point, matt, about the indexability (or rather, lack thereof) of casting a date time value as a string

    the function you're thinking of is not TO_UNIXTIME, which would only have made sense (this is mysql, don't forget) but rather UNIX_TIMESTAMP()

    but your query won't work anyway, because casting '18-Jul-02' as a timestamp will more than likely get the time 00:00:00 appended to that date

    and that's not going to match very many actual timestamps in the table, is it...

    so, spaceman, you can also try this --
    Code:
    select LogSessionID, LogTime
         , FROM_UNIXTIME(LogTime,'%d-%b-%y') as LogDate
      from yourTable
     where LogTime between unix_timestamp('18-Jul-02')
                       and unix_timestamp('19-Jul-02')
    and let us know if it works faster (i.e. uses the index)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Silly MySQL breaking its own conventions.

    Yup the between would be a better solution!

  7. #7
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys - you've been a great help.

    Due to my actual SQL query being more involved than the simplified example I initially gave, the most convenient solution was to use the one in r937's original post.

    And of course it worked 100%. Thanks again - really helped me out there.
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise


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
  •