MySQL Reusing calculation in SELECT clause in a VIEW

MySQLselectview

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

SELECT *, @week_diff := TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (@week_diff % week = 0) AS do_current_week
FROM event_repeats

The table:

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.

Best Answer

You can only do this in a view by repeating the expression.

CREATE VIEW upcoming_events_view AS
SELECT *, 
       TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (TIMESTAMPDIFF(WEEK, `create_date`, NOW()) % week = 0) AS do_current_week
  FROM event_repeats

The NOW() function uses the time at the beginning of statement execution no matter how long the query takes to run, so you'd never get two different answers from NOW() in the same query. The TIMESTAMPDIFF() function's second invocation will probably not be optimized away by the optimizer, but this is a trivial calculation. It benchmarks at 0.5 microseconds on an AMD Opteron 1GHz processor, so a million calls to that function takes half a second.

Since your create_date is a DATE, not a DATETIME, the CURDATE() function might be a more technically accurate choice, since the TIMESTAMPDIFF() function first converts your dates to datetimes with an implicit 00:00:00 time element.

mysql> select TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23'), TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23');
+----------------------------------------+--------------------------------------------+
| TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23') | TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23') |
+----------------------------------------+--------------------------------------------+
|                                      0 |                                          1 |
+----------------------------------------+--------------------------------------------+
Related Question