?
Solved

MySql - can I create a Calculated Field and index it?

Posted on 2011-10-05
10
Medium Priority
?
400 Views
Last Modified: 2012-06-22
I have a WHERE clause that looks like:
WHERE Month(PA.EntryDate) = Month('11-10-04') and Year(PA.EntryDate) = Year('11-10-04')

Open in new window


Something like that shows up a lot in my DB.

I would like to add a column
EntryMonth as Month(EntryDate) -- or something to that effect.  In MS SQL Server, that would allow me to index the EntryMonth so that an index could be used in the WHERE clause.

Can I do something similar in MySql?  Every Google search I try on Calculated Fields is coming up with fields updated by a trigger ... not a true calculated field.  Is that the best MySql 5 can offer currently?

Thanks!
0
Comment
Question by:Daniel Wilson
  • 5
  • 5
10 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 36917923
why don't you just index EntryDate and then change your WHERE clause like this?

WHERE PA.EntryDate between dateadd(m, datediff(m, 0, '11-10-04'), 0) and dateadd(m, datediff(m, 0, '11-10-04'), 1)-1
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 36917961
select * from wt_PaymentAgreement PA WHERE PA.EntryDate between dateadd(m, datediff(m, 0, '11-10-04'), 0) and dateadd(m, datediff(m, 0, '11-10-04'), 1)-1;

Open in new window

gives me this:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '11-10-04'), 0) and dateadd(m, datediff(m, 0, '11-10-04'), 1)-1' at line 1

Open in new window


And ... what is the dateadd (datediff () ) stuff doing?  

This may be a good solution, but I do need to understand it!

Thanks!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 36917988
for mysql

use

WHERE PA.EntryDate between last_day('11-10-04' - 1 interval month) + 1 and last_day('11-10-04')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 32

Author Comment

by:Daniel Wilson
ID: 36918000
The DateDiff call is throwing it:
select datediff(m, 0, '2011-10-04');

gives the same error.

I think the syntax you gave me (once we fix whatever the error is) is saying "any date from the first of this month through 1 day less than the first of next month".

right?
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 36918013
With the last_day syntax:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval month) + 1 and last_day('11-10-04')' at line 1
0
 
LVL 41

Expert Comment

by:ralmada
ID: 36918037
problem is the the original suggestion was for MS SQL Server. for MySQL try http:#a36917988

>>I think the syntax you gave me (once we fix whatever the error is) is saying "any date from the first of this month through 1 day less than the first of next month".<<

correct :) basically is a way to obtain the first and last day of the month and then use those to compare. Now if you have time portion involved as well then you might want to just compare to the first day of the next month

WHERE PA.EntryDate >= last_day('11-10-04' - 1 interval month) + 1 and PA.EntryDate < last_day('11-10-04') + 1

0
 
LVL 41

Expert Comment

by:ralmada
ID: 36918054
>>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'interval month) + 1 and last_day('11-10-04')' at line 1
<<

oops, I've inverted things there:


WHERE PA.EntryDate >= last_day('11-10-04' - interval 1 month) + 1 and PA.EntryDate < last_day('11-10-04') + 1

0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 36918071
I do have the time portion as well ... so ... the +1 makes sense, except it's not working right either.
mysql> select last_day('11-10-04');
+----------------------+
| last_day('11-10-04') |
+----------------------+
| 2011-10-31           |
+----------------------+
1 row in set (0.00 sec)

mysql> select last_day('11-10-04')+1;
+------------------------+
| last_day('11-10-04')+1 |
+------------------------+
|               20111032 |
+------------------------+
1 row in set (0.00 sec)

Open in new window


But the biggest problem in here is the - 1 interval month  That is not being accepted at all.

should I use date_sub() ?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 36918100
1 interval month  should be interval 1 month  

WHERE PA.EntryDate >= last_day('11-10-04' - interval 1 month) + INTERVAL 1 DAY and PA.EntryDate < last_day('11-10-04') + INTERVAL 1 DAY

0
 
LVL 32

Author Closing Comment

by:Daniel Wilson
ID: 36918131
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question