Date and Time problems

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Vapor
Forum Newbie
Posts: 3
Joined: Thu Dec 29, 2005 10:55 am

Date and Time problems

Post by Vapor »

Hello; this is my first post on here; so hell to you all =D. My current project is to create a site that will keep track of work hours; expected payouts; shifts, etc..etc... My current problem lies in when I attempt to do a 'montly summary' where I need to select all the shifts of that month only. In my MySQL database I have a 'Shift_Date' datatype 'DATE". I need to somehow parse the date out of this and select only the results in the current month. I'd prefer this to be built intp the SQL query instead of returning all the rows then filtering through them, but I cannot see any way in which to do this. If a filter is needed what would be the most dynamic way to filter the results to reduce stress on the server?
yum-jelly
Forum Commoner
Posts: 98
Joined: Sat Oct 29, 2005 9:16 pm

Post by yum-jelly »

You have many different options, it really depends on your database setup. Most databases a really smart, so you can do simple stuff like....

starting_date = 2005-10-01

ending_date = 2005-10-31

Code: Select all

SELECT column_one, column_two FROM some_table WHERE shift_date >= starting_date AND shift_date <= ending_date
Like I said you have other options to...

You can use INTERVAL and select from the cuurent date and move backward or forward...

(show the next 30 day)

Code: Select all

SELECT column_one, column_two FROM some_table WHERE shift_date >= CURDATE() AND shift_date <= ADDDATE(CURDATE(), INTERVAL 30 DAY);

If your using MySQL have a look at the time and date functions. There is many good examples that cover a lot!

pif
Vapor
Forum Newbie
Posts: 3
Joined: Thu Dec 29, 2005 10:55 am

Post by Vapor »

Thanks! I'm using your first suggestion; problems occuring on the <= statement but I should be able to get this. Thanks again!
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post by Bill H »

You can also use the MONTH() function:

Code: Select all

SELECT column_one, column_two FROM some_table WHERE MONTH(shift_date)=curr_month
Post Reply