Extracting datetime

[Donovan]

I built a question and answer system for students and faculty at the university I work at.  Students can submit anonymous questions to their professors for each course they are in.  Each question submitted is inserted into a comment_queue table with a timestamp now().

 

The record is tagged with a date/time, such as 2008-01-17 13:02:07.

 

Currently all questions are displayed for all records for that course regardless of time/date.  I want to change this to only display the current "academic" years questions. Sounds easy enough but the academic year is different than calendar year.  This academic year we are in is 0708.  Next academic year will be 0809.  The academic year started in August like normal and will last till the following June.

 

I had a field in a comment_config table for academic_year (value = 0708) that could be inserted for all questions posted for that year, but was wondering if I could use the timestamp value I already have.

 

I also want to implement a way for students to be able to look at the previous years questions that were submitted at the start of that calendar year by the use of a list box for 2007, 2008...etc.

 

I'm getting confused trying to find a use for the timestamp value I already capture and if it can be used for the purposes I need.

 

How could I extract the date from my timestamp value and "transpose" that to compare it to an academic_year value such as 0708?

 

I'm thinking I may need another table just for this purpose.

 

2006 = 0607

2007 = 0708

2008 = 0809

2009 = 0910 

[irvieto]

hi.

 

Let me see..., academic year is a composition of the last 2 digits of the actual year and the next year. E.g.( 2008-2009 = 08-09 ).

So, use mysql date_format function to retrieve only the last 2 year digits from the timestamp and you will be able to retrieve academic year using concat()

 

Hope this helps.

 

[laffin]

but that wud also overlay. say 0607 0708

both wud contain all info from 07.

Extracting isnt the issue here, it's the calculation of year to year.

 

so u wud need to convert 0607 to start and end timestamps.

 

function getacyear($acyear)
{
     $mil=(substr($acyear,0,1)=='0'?"20":"19")
     $date['start']=$mil . substr($acyear,0,2). "-09-01";
     $date['end']=$mil . substr($acyear,2,2) . "-06-01";
     return $date;
}

 

the start/end date are designated as "YYYY-MM-DD" so change accordingly

now when searching in mysql, you can get and use these codes with

$sedate=getacyear($acyear);
$query = "SELECT * FROM tbl_name WHERE date >= $sedate[start] AND date <= $sedate[end];"

 

or however yer query goes

 

good lick