spaceman — 2002-07-18T06:10:23-04:00 — #1
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)...
...but it appears that any column name that has been renamed using the 'AS' syntax is not available for use in the WHERE clause.
a) confirm (or otherwise) my observation?
b) suggest a way around this, because it would be real useful to me?
Thanks in anticipation.
hex — 2002-07-18T08:21:09-04:00 — #2
It should be available for use.
r937 — 2002-07-18T09:14:32-04:00 — #3
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
where FROM_UNIXTIME(LogTime,'%d-%b-%y') = '18-Jul-02'
not what you wanted to hear, but there ya go...
mattr — 2002-07-18T09:38:32-04:00 — #4
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 — 2002-07-18T16:03:50-04:00 — #5
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
where LogTime between unix_timestamp('18-Jul-02')
and let us know if it works faster (i.e. uses the index)
mattr — 2002-07-18T16:36:16-04:00 — #6
Silly MySQL breaking its own conventions.
Yup the between would be a better solution!
spaceman — 2002-07-18T22:49:33-04:00 — #7
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.