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

[spaceman]

Here's my query string:

$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.

[hex]

It should be available for use.

[r937]

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:

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...

[MattR]

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.:

WHERE LogTime = TO_UNIXTIME( '18-Jul-02' )

[r937]

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 --

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)

[MattR]

Silly MySQL breaking its own conventions.

Yup the between would be a better solution!

[spaceman]

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.