Date functions in PH & MySQL

[Mong]

Me again

I need to display records of up to a week old, so what I was doing was:

$today = (date("Ymd") - 7);
...
$query = "SELECT DISTINCT foo1, foo2, foo3 FROM `table` WHERE foo2 > '$today' ORDER BY foo2 ASC";

But then of course, if today was 20021207, then -7 would give 20021200 - which is obviously no help! And of course, today is 20021216, so I was to do - 17 (for example), I'd get 20021199 !

Does anybody have experience of this?

M.

[Mong]

I read a sneaky part of the manual...

Do this:

WHERE TO_DAYS(NOW()) - TO_DAYS(foo2) <= 7 ORDER BY foo2 ASC"

M.

[Gayle29uk]

Subtracting 604800 from the current timestamp should do it

[Mong]

Originally posted by Gayle28uk
Subtracting 604800 from the current timestamp should do it

Consider yourself *fwapped*

M.

[Gayle29uk]

Originally posted by Mong
Consider yourself *fwapped*

M.

???

I hate sounding ignorant but what on Earth is a fwap?

[MT]

http://www.mysql.com/doc/en/Date_and...s.html#IDX1271

in particular you are lookinf for something like

SELECT x,y,z from table WHERE table.date_field > DATE_SUB( CURDATE(), INTERVAL 15 DAY);

[Mong]

Cheers MT... but... read the full thread before replying... the answer was already here

M.

[MT]

Originally posted by Mong
Cheers MT... but... read the full thread before replying... the answer was already here

M.

I did read the full thread, but

a) it didnt show how to get to that answer

and

b) I didnt feel that answer was necessarily the best answer.

[Mong]

MT: You prefer the "interval" method to the "to_days" method? I guess when dealing with days, there's no difference?

Gayle: To "fwap" is to hit hard, but in a friendly manner

M.

[Gayle29uk]

Originally posted by Mong
Gayle: To "fwap" is to hit hard, but in a friendly manner

M.

Ah...

My method was still valid though and didn't involve all the buggering about

[Mong]

Originally posted by Gayle28uk
My method was still valid though and didn't involve all the buggering about

True, true... it just seems to "simple" though :-P

M.

[MT]

Originally posted by Mong
MT: You prefer the "interval" method to the "to_days" method? I guess when dealing with days, there's no difference?
M.

True, theres no difference, but when the next question is "AHA, but what if I want everything that happened up to 8 hours ago", I preferto not have to look thru the manual to find the same page over and over.

Mind you, with these forums liking of 'Mass Prune', archiving threads is pointless anyways :)

[Mong]

I think the INTERVAL method is probably best, actually.

Cheers,

M :smiley1:

[Gayle29uk]

Originally posted by MT
True, theres no difference, but when the next question is "AHA, but what if I want everything that happened up to 8 hours ago", I preferto not have to look thru the manual to find the same page over and over.

Time_X_hours_ago = Current_Timestamp - (X*3600)

Expand that equation to include weeks/days/minutes/seconds and you have a generic processor-light way of doint it using nothing more than integer arithmetic. Or do I just like doing things a weird way?