Datetime within past week

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

Re: Datetime within past week

Greg Scharlemann wrote:

Quoted text here. Click to load it

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

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 for more details

Chris Hope | |

Re: Datetime within past week

Chris Hope wrote:
Quoted text here. Click to load it

Exactly what I was looking for!  Thanks!

Re: Datetime within past week

Following on from Chris Hope's message. . .
Quoted text here. Click to load it

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.

Quoted text here. Click to load it

PETER FOX Not the same since the borehole business dried up
2 Tees Close, Witham, Essex.
Gravity beer in Essex  <

Re: Datetime within past week

Greg Scharlemann wrote:
Quoted text here. Click to load it

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

  $result = mysql_query('SELECT myColumn FROM myTable where datacol=  

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline