Date counting

reeksy

Hi.

I’ve got the following date stored in a database: 2007-12-30 (Obviously this date could be anything) I'll refer to this as the 'original date' throughout this post.

The ultimate goal is to display to the user the date 10 weeks before the original date (shown above).

Now, my guess would be to convert the original date back into a timestamp and then do some sort of calculation on that timestamp that counts back 10 weeks.

My first problem is; I’m unsure how to convert the original date back into a timestamp and secondly I’m unsure how I would go about counting back 10 weeks using the timestamp.

Any help/time would be great.

Jon

frikikip

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

You query should look something like this

SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 10 WEEK) <= date_col

Cheers,

Marvin

PS, the WEEK constant can only be used in MySQL 5, so you probably need a workaround for that.

bogu

For mysql, u can find all cover in here: Date and Time Functions

For php u may find all in here: Date and Time functions

reeksy

Ok thanks, I think there's some confusion though!

I'm not trying to select a date from the database. The 'original date' has already been selected. What I’m trying to do is do a calculation on the selected date. If that makes sense!

So say you had '2007-12-30' how would you count back 10 weeks from that date? Not using MySQL.

Thanks

Installer

MySQL:

SELECT my_date, SUBDATE(my_date, INTERVAL 10 WEEK) AS earlier_date FROM my_table;

or PHP:

$earlier_date = date('Y-m-d', strtotime('-10 weeks', $my_date));

Try "INTERVAL 70 DAY" for MySQL < 5.

reeksy

Using the following code where $my_date = 2006-12-30 i get a result of 1970-03-12

$earlier_date = date('Y-m-d', strtotime('-10 weeks', $my_date));

That can't be correct!

I looks like the code is miss counting somewhere as 12 has become the day as opposed to the month!

Any ideas!?

tsinka

Hi,

strtotime expects a unix timestamp as optional second parameter. Alternatively add the date to the first parameter.

So something like this should work:

$earlier_date = date('Y-m-d',strtotime($my_date.' -10 weeks'));

Thomas

Installer

Sorry, my error.

Anyway, it's all in the links provided earlier in the thread.