I have an SQL query that has a simple date difference between
NOW() and the creation date (is in the table). I need to reuse this again within the
SELECT clause in a
VIEW. Is this possible at all?
The query (note substitutions have been made from the original):
CREATE VIEW upcoming_events_view AS SELECT *, TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff, (week_diff % week = 0) AS do_current_week FROM event_repeats
Needless to say, this didn't work due to the
week_diff being reused in the
SELECT clause. As a normal
SELECT query, I found variables worked; however, I can't use that in a
SELECT *, @week_diff := TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff, (@week_diff % week = 0) AS do_current_week FROM event_repeats
CREATE TABLE IF NOT EXISTS `event_repeats` ( `event_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `event_name` varchar(150) NOT NULL, `start_date` date NOT NULL, `week` int(11) NOT NULL, PRIMARY KEY (`event_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=51 ;
What's the best way to do this (if any)?
Thanks in advance.