Posted by Marcus Bointon on 07/29/05 11:04

On 29 Jul 2005, at 08:37, <virtualsoftware@gmail.com>
<virtualsoftware@gmail.com> wrote:

> I have the date when users registered stored in the database using
> time() function format.

Hm. I hope this means you're storing it as a TimeStamp format, or
better, DateTime, and not as a simple integer field.

> I have to search for users that are registered this week, this
> month and this year. How can i do that? I mean how can i found the
> beginning of this week? Or the beginning of this month?

If you are storing it as a DateTime field, MySQL has loads of date
related functions for doing this, and it's more efficient to do it
using MySQL's functions that the equivalent in PHP. Take a look here:
http://dev.mysql.com/doc/mysql/en/date-and-time-types.html and http://
dev.mysql.com/doc/mysql/en/date-and-time-functions.html

You can do the calculations in PHP. For example, to find the first
day of this month as a timestamp:

$first = strtotime(date('Y-m-01'));

strtotime has some wonderful functionality - you can ask for dates
like 'next week' 'last year' 'yesterday -1 week'.

To format a timestamp into a MySQL-compatible DateTime string, use:

$datetime = date('Y-m-d h:i:s', $timestamp);

Marcus
--
Marcus Bointon
Synchromedia Limited: Putting you in the picture
marcus@synchromedia.co.uk | http://www.synchromedia.co.uk

[Back to original message]