Avatar of Daniel Wilson
Daniel Wilson
Flag for United States of America asked on

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

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!
MySQL ServerMicrosoft SQL Server

Avatar of undefined
Last Comment
Daniel Wilson

8/22/2022 - Mon
ralmada

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
Daniel Wilson

ASKER
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!
ralmada

for mysql

use

WHERE PA.EntryDate between last_day('11-10-04' - 1 interval month) + 1 and last_day('11-10-04')
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Daniel Wilson

ASKER
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?
Daniel Wilson

ASKER
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
ralmada

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ralmada

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

Daniel Wilson

ASKER
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() ?
ASKER CERTIFIED SOLUTION
ralmada

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Daniel Wilson

ASKER
Thanks!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23