Jump to content

Recommended Posts

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!

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';

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.

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.

 

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'";

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';

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.