Data operation

[FraanXT]

Hello everybody,

 

I want to obtain from the database the rows that have been inserted in the last seven days.

Every time that I insert a row, I add a date with this format:

$today = date("Y-m-d H:i:s");

So it should be like:

$lastweek = date("Y-m-((d)-7) H:i:s");

SELECT * FROM pub WHERE data BETWEEN('$today' AND '$lastweek')

Is it possible? which is the method to do that?

 

Thank you all

[Jacques1]

The best method is to RTFM.

[Psycho]

First off, you shouldn't need to manually set the date when adding a record. Instead, you can use a timestamp field with a default value of NOW(). Then in your INSERT statements don't even include the datetime as part of the INSERT data. The database will automatically populate that field with the current datetime value.

 

Second, if you want all the records that were created in the last seven days, then there is no need to use BETWEEN. You just need a greater than comparison.

 

Third, the sample query you posted is looking for a comparison of the datetime value on a field called 'data'?

 

Fourth, do you want the time component to be included in this comparison? For example, if you run the query at 1:00PM, do you only want the records created from last Wednesday after 1PM? Or do you want ALL the records created from last Wednesday starting at 12:00AM? Or, maybe you really want today + the last six days inclusive, otherwise you would always be getting between 7 to 7.99999 days of data.

 

Lastly, whichever you want from above, you can dynamically do this int he query - no need to artificially create the start datetime in PHP.

Edited May 28, 2014 by Psycho

[Psycho]

OK, here are a couple examples since I don't know which one you really need:

 

If you want all the records for the last 6 days (inclusive) plus all from today, then you could use this

SELECT *
FROM table_name
WHERE date_field > DATE_SUB(CURDATE(), INTERVAL 6 DAY)

So, if you run this at 3PM on a friday it would pull all records starting at 12AM on the previous Saturday. I think this is probably what you want.

 

If you want all the records from seven days ago starting at the same time then you could use this format

SELECT *
FROM table_name
WHERE date_field > DATE_SUB(NOW, INTERVAL 7 DAY)

So, if you run it at 3PM on a Friday, it would pull all the records starting from 3PM on the previous Friday. I doubt you want this. If you run it at 1:))PM and then again at 1:05PM on the same day, you could get different results.

Edited May 28, 2014 by Psycho

[FraanXT]

Ow thank you so much Psycho!

 

I wanted the second one and it's exactly what I was looking for.

Edited May 28, 2014 by FraanXT