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'

noobie-php
  • 6,817
  • 15
  • 54
  • 101

1 Answers1

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.

Bobert1234
  • 130
  • 12