0

So i am trying to use Between Timestamp as follows , i have date stored as Unix time stamp and i am 1st trying to convert it, and then get all users between the said time.

SELECT users.*, DATE_FORMAT( FROM_UNIXTIME(users.created), '%Y %b %e ' ) AS date_formatted FROM node LEFT JOIN users ON node.uid = users.uid WHERE node.uid != 0 AND (date_formatted BETWEEN '2017-06-30 00:00:00' AND '2018-06-30 00:00:00') GROUP BY uid

But i am getting this error

Unknown column 'date_formatted' in 'where clause'

Improve this question
0

1 Answer 1

Reset to default
1

the issue, as mentioned in the comments, is that "date_formatted" is an alias of a result. it is not in the tables itself and therefore you can't call it within the WHERE statement. You can call it at the end of a query using a HAVING statement, but that would mean that the query has to first pull up a whole lot more data than you need. MYSQL does a whole lot of implicit casting when it comes to dates, so you can probably just use:

users.created BETWEEN '2017-06-30 00:00:00' AND '2018-06-30 00:00:00'

but if you don't want to risk it, you can apply the formatting/casting within the WHERE statement.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.