Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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 |
+----------------------------------------+--------------------------------------------+
share|improve this answer
    
The question is if you can reuse the calculation. The fact that you can repeat it is not much of an answer. –  Andomar Feb 16 '13 at 20:13
    
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." –  Michael - sqlbot Feb 16 '13 at 20:20
    
I might have to use this I think. Thanks! –  Hosh Sadiq Feb 17 '13 at 7:28
    
Also thanks for the CURDATE() suggestion! Am using that now :) –  Hosh Sadiq Feb 17 '13 at 8:28
add comment

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.

share|improve this answer
    
@Michael-sqlbot: Ok... how about another view? –  Andomar Feb 16 '13 at 19:05
    
Wouldn't that have a huge performance impact? –  Hosh Sadiq Feb 16 '13 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? –  Andomar Feb 16 '13 at 19:11
1  
week is a column in the original table. –  ypercube Feb 16 '13 at 21:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.