Querying DB for particular month in DATETIME

[Hutchie]

Hi all,

I am currently storing dates and times for certain events in the following format:

 

$now= date('Y-m-d H:i:s');

 

This variable is then stored in a MySQL field of type DATETIME. I am looking for a way to query the database to return all results in a certain month, or within a certain time range, or a certain year etc.

 

How would this be achieved?

 

e.g. If I wanted to select everything for the year 2009, would it be:

SELECT * FROM table WHERE date BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 11:59:59' ?

 

Thanks!

[jcbones]

Try using the DATE(), MONTH(), YEAR() functions in Mysql.

 

Ex.

SELECT MONTH(`date`) FROM table;
SELECT * FROM table WHERE MONTH(`date`) = MONTH('2009-01-01 00:00:00') AND YEAR(`date`) = YEAR('2009-01-01 00:00:00');
SELECT * FROM table WHERE MONTH(`date`) = '12' AND YEAR(`date`) = '2009';

[Hutchie]

Thanks jc, but I think you misunderstood my question.

 

It seems like you are trying to get pull a month or year out from a stored entry. Rather, I am trying to query the database, and get a list of results of all rows that have occurred at a particular time, or during a particular time range.

 

Let me give a more specific example.

 

I have a table of users, and in a field called createdon I stored the date and time of the account creation as described above in a DATETIME type field. I now want to return a list of all users created during 2009.

[samshel]

Hi all,

I am currently storing dates and times for certain events in the following format:

 

$now= date('Y-m-d H:i:s');

 

This variable is then stored in a MySQL field of type DATETIME. I am looking for a way to query the database to return all results in a certain month, or within a certain time range, or a certain year etc.

 

How would this be achieved?

 

e.g. If I wanted to select everything for the year 2009, would it be:

SELECT * FROM table WHERE date BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 11:59:59' ?

 

Thanks!

 

Yes..

 

SELECT * FROM table WHERE date BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 11:59:59'

 

would work.

 

[litebearer]

or...

/* all records for given month */
$c_mo = date("m");
$query_b = "SELECT * FROM table WHERE month(date_field)='$c_mo'";
/* all records for agiven year */
$c_yr = date("Y");
$query_b = "SELECT * FROM table WHERE year(date_field)='$c_yr'";

[jcbones]

Thanks jc, but I think you misunderstood my question.

 

It seems like you are trying to get pull a month or year out from a stored entry. Rather, I am trying to query the database, and get a list of results of all rows that have occurred at a particular time, or during a particular time range.

 

Let me give a more specific example.

 

I have a table of users, and in a field called createdon I stored the date and time of the account creation as described above in a DATETIME type field. I now want to return a list of all users created during 2009.

 

So, you would use part of my 3rd example.

SELECT * FROM table WHERE YEAR(`date`) = '2009';