Datetime within past week

[Greg Scharlemann]

I have a series of records in a database. When each record is stored,
the datetime is logged: $date = date("Y-m-d H:i:s");

Prior to adding a new record to the database, I want to run a query to
retrieve all of the records uploaded in the last 7 days. I thought it
would be easiest to:

Pseudo Code:
$newDate = $date - 7 days;
select * from TABLE where DATE $newDate;

Problem is I can't figure out how to subtract 7 days from $date and
convert that value to a valid datetime object.

Thanks for any help

[Chris Hope]

Re: Datetime within past week

Greg Scharlemann wrote:

I have a series of records in a database. When each record is stored,
the datetime is logged: $date = date("Y-m-d H:i:s");
>
Prior to adding a new record to the database, I want to run a query to
retrieve all of the records uploaded in the last 7 days. I thought it
would be easiest to:
>
Pseudo Code:
$newDate = $date - 7 days;
select * from TABLE where DATE $newDate;
>
Problem is I can't figure out how to subtract 7 days from $date and
convert that value to a valid datetime object.
>
Thanks for any help

You can do it in SQL. Assuming you're using MySQL look for SUBDATE() on
the following page:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

If you want to do it in PHP you can do eg:
date('Y-m-d H:i:s', time() - 86400 * 7);

time() returns the current timestamp. There are 86400 seconds in a day
so multiplying that by 7 gives you the seven days you're after. Then
use date() to format and put into your sql statement.

It's also possible to do it with mktime() eg:
$timestamp = mktime(0, 0, 0, date('m'), date('d')-7, date('Y'))
although there are more function calls doing it this way.
See http://www.php.net/mktime for more details

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

[Jerry Stuckle]

Re: Datetime within past week

Greg Scharlemann wrote:

I have a series of records in a database. When each record is stored,
the datetime is logged: $date = date("Y-m-d H:i:s");
>
Prior to adding a new record to the database, I want to run a query to
retrieve all of the records uploaded in the last 7 days. I thought it
would be easiest to:
>
Pseudo Code:
$newDate = $date - 7 days;
select * from TABLE where DATE $newDate;
>
Problem is I can't figure out how to subtract 7 days from $date and
convert that value to a valid datetime object.
>
Thanks for any help
>

If it's MySQL, you can do it all in SQL:

$result = mysql_query('SE LECT myColumn FROM myTable where datacol=
SUBDATE(NOW(), INTERVAL 7 DAY))';

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Greg Scharlemann]

Re: Datetime within past week


Chris Hope wrote:

>
If you want to do it in PHP you can do eg:
date('Y-m-d H:i:s', time() - 86400 * 7);
>

Exactly what I was looking for! Thanks!

[Peter Fox]

Re: Datetime within past week

Following on from Chris Hope's message. . .

>If you want to do it in PHP you can do eg:
>date('Y-m-d H:i:s', time() - 86400 * 7);

To be pedantic this isn't "last 7 days". It could be important if say a
customer makes a regular order. Suppose last Monday they managed to put
their order in at 11am but this Monday don't get round to it until
11:30am; now 'Last weeks' order won't appear in the list. To the
customer this could be confusing when last weeks order sometimes appears
and sometimes doesn't.

* It may not matter, so no need to be more complicated
* If it does then even an 'exact same time' or '(86400*7)+(60* 30) could
fail when switching to daylight saving time.
* It isn't difficult to establish what '00:00:01' is when you know the
page in the manual, but once again things can go wrong at daylight
saving and beware the simple coding error.

>It's also possible to do it with mktime() eg:
>$timestamp = mktime(0, 0, 0, date('m'), date('d')-7, date('Y'))
>although there are more function calls doing it this way.
>See http://www.php.net/mktime for more details
>

--
PETER FOX Not the same since the borehole business dried up
peterfox@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>