1

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.

Improve this question

2 Answers 2

Reset to default
4

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 |
+----------------------------------------+--------------------------------------------+
Improve this answer
3
  • The question is if you can reuse the calculation. The fact that you can repeat it is not much of an answer. Commented Feb 16, 2013 at 20:13
  • 1
    The answer to "how do you do this?" is "you don't," ... or "no, you cannot reuse the calculation in a view, you have to repeat the expression." Commented Feb 16, 2013 at 20:20
  • Also thanks for the CURDATE() suggestion! Am using that now :) Commented Feb 17, 2013 at 8:28
0

You can use another view to define the alias:

create view upcoming_events_view_base as
select  *
,       timestampdiff(week, `create_date`, now()) AS week_diff
from    event_repeats;

create view upcoming_events_view as
select  *
,       (week_diff % week = 0) AS do_current_week
from    upcoming_events_view_base;

Example at SQL Fiddle.

Improve this answer
4
  • @Michael-sqlbot: Ok... how about another view? Commented Feb 16, 2013 at 19:05
  • Wouldn't that have a huge performance impact? Commented Feb 16, 2013 at 19:07
  • Views are very fast, I wouldn't expect a measurable difference. Btw, where does the week in (@week_diff % week = 0) come from? Commented Feb 16, 2013 at 19:11
  • 1
    week is a column in the original table. Commented Feb 16, 2013 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.