date_sub/interval question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

date_sub/interval question

Post by billman »

I have a mysql table that holds data for submitted pictures (not the pictures themselfs) with a datetime field, and I'm trying to sort them by X days ago. I don't want something that was submitted 1 day ago to also show up on the 7 day ago list, I want it to be exact and only show up on the day it was entered. The code I'm using now seems to work, but I'm wondering if it will screw up next month by overlapping day numbers. By the way, $day is just a number, like 1, or 2, or 3, and so on.

Code: Select all

select * from pics where dayofmonth(picdate) = dayofmonth(date_sub(now(), interval $day day))
The code above works fine, but as I said, I'm hoping it won't overlap, eg June 7th pic would show up on July 7th, and I don't want that to happen. :wink:

This code below just does not want to work properly, I HAVE to use the dayofmonth above or it won't display anything.

Code: Select all

select * from pics where picdate = date_sub(now(), interval $day day)
Is there any way to improve this, or should I leave it alone and use dayofmonth (as long as it doesn't overlap!)
User avatar
billman
Forum Newbie
Posts: 9
Joined: Sun Oct 27, 2002 5:06 pm

Post by billman »

Nevermind, this works wonderfully (can't believe I didn't see it before) 8)

Code: Select all

select * from pics where to_days(now()) - to_days(picdate) = $day
Post Reply