Experts Round Table Network

Databases => MySQL => Topic started by: klitscher on October 18, 2006, 08:31:53 PM

Title: querying on timestamp field for certain month or day
Post by: klitscher on October 18, 2006, 08:31:53 PM

I'm writing some software in php to organize news and events for a school committee and want to start an archive function. The News and events have a timestamp that marks when the event takes place or when the news was posted. If I want to return all of the events that happened in september of 2006, how would I construct a sql query on the timestamp field to select those events? I've tried googling and looking at the dev.mysql.com but just don't know where to start. Thanks,
Ken

Title: Re: querying on timestamp field for certain month or day
Post by: rdivilbiss on October 18, 2006, 08:44:58 PM

You didn't say what database or how the timestampt wass generated, but assuming MySQL using NOW to create the timestamp, then:

SELECT *
FROM `yourTable`
WHERE month( timeStampField ) = '9'
AND year( timeStampField ) = '2006'

should do the trick.

Title: Re: querying on timestamp field for certain month or day
Post by: klitscher on October 18, 2006, 08:57:42 PM

Sorry...thought the database was obvious, as it's in the MySQL forum...and the timestamps are either done using NOW() or inserted using the mysql datetime format. I'll give it a try...thanks!

Ken

Title: Re: querying on timestamp field for certain month or day
Post by: klitscher on October 18, 2006, 09:31:14 PM

worked like a charm...thanks so much

Title: Re: querying on timestamp field for certain month or day
Post by: VGR on October 18, 2006, 11:33:32 PM

too bad I was asleep ;-)

select * from yourtable where datetimestamp like '2006-09%' order by datetimestamp DESC LIMIT xxx;

may be faster