2

Good evening.

How can I calculate periods of 6 months (semesters or a two terms years)?

Explaining: There is a spreadsheet, which I will convert to a MySQL DB, that has the following, relevant, columns Course, Begin and Duration. Course is a string field that combined with the info from another table returns the Duration. The Begin field is year-semester (like 2010-2 is two thousand and ten secound semester) that the course was started. Duration is the number of years.

The format

table1

**Course**       **Begin**
Graduation 1     2010-1

table2

 **Course**      **Duration**
 Graduation 1    4,5

2010-1 means 2010 first semester and 4,5 (actually 4.5 years - four dot five years) means four and a half years, that gives a final date like 2014-1. The fields format, unfortunately, come from another database which I don't have access to change, I just can import the data.

This is probably simple or extremely simple or not.

[Edit] I hope now is correct. [Edit] This will be imported form a first DB, caculated and imported to another DB.

msmafra
  • 1,704
  • 3
  • 21
  • 34
  • I have a feeling that *semester* means something slightly different to you than it does to me. (Or to most of the people in the USA.) To me, your "4,5" doesn't make sense. To me, 4 semesters is two years, 5 semesters is 2 1/2 years, and (assuming "," is your decimal point) 4.5 semesters is, well, I don't know what half a semester is. Also, adding 2 1/2 years to 2010-1 seems like it should give me something besides 2012-1, which seems only two years difference. Tell us a little more about semesters and how that arithmetic is supposed to work. – Mike Sherrill 'Cat Recall' Aug 30 '12 at 09:53
  • Ooops! I am very sorry. In Brazil we use comma as decimal separator. so 4,5 is 4.5. Is not for thousand and a half :). Semester as in "A semester system divides the academic year into two terms, roughly 16–18 weeks each." [Wikipedia](http://en.wikipedia.org/wiki/Academic_term). The 5 semesters are actually 4 years and a half. Sorry again, I was almost sleeping over my keyboard. – msmafra Aug 30 '12 at 15:36
  • 1
    Shouldn't `2010-1 + 4.5yr` be `2014-2` ? – Ja͢ck Aug 31 '12 at 06:16
  • No, because 2010 counts. With the sum of 2010-1, 2010-2, 2011-1, 2011-2, 2012-1, 2012-2, 2013-1 and 2013-2 we have 4yr plus the 2014-1 we get to 4.5yr – msmafra Aug 31 '12 at 23:14

1 Answers1

1

MySQL doesn't have standard support for semesters, so you'd have to convert to months first (quarters are also supported, but working with months is easier regarding date).

This means that you need to replace "2010-1" by "2010-01-01" and "2010-2" by "2010-07-01". This could be done with REPLACE($begin, '-1', '-01-01') and REPLACE($begin, '-2', '-07-01').

For each semester you could add 6 months: DATE_ADD('2010-01-01', INTERVAL 6 MONTH) will return "2010-07-01". Multiply the "duration" by 12, which will give the amount of months you need to shift the "Begin".

wkoot
  • 139
  • 1
  • 10