I have a date comparison problem that I'm sure is simple enough but I'm just
not experienced enough to figure it out. I simply want to grab every entry
in a given table that has been entered within the last three days.
I'm using Perl so I first start with a Perl timestamp and convert it to a
MySQL timestamp.
########################
($sec,$min,$hr,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year = $year + 1900;
if ($mon <= 9) { $mon = "0$mon"; }
if ($mday <= 9) { $mday = "0$mday"; }
if ($hr <= 9) { $hr = "0$hr"; }
if ($min <= 9) { $min = "0$min"; }
if ($sec <= 9) { $sec = "0$sec"; }
$currenttime = "$year$mon$mday$hr$min$sec";
########################
Now I want to select all entries that are >= ($currenttime - 3 days)
It would work most of the time if I just subtracted 3000000 from
$currenttime, however wouldn't things get messy at the beginnings of every
month? For example, with a good, regular date like 20010915103934
(09/15/01), subtracting 3000000 would give me 20010912103934 (09/12/01).
That would work. However, if $currenttime = 20010901103934 (09/01/01) and I
subtracted 3000000, I would get 20010898103934 (08/98/01). See, that would
be completely screwed up.
How can I accomplish my goal? Please forgive the MySQL newbie for his
stupidity.
Thanks,
--
Ned Dupont
Web Developer
WS Packaging Group Inc.
920-487-6270
[EMAIL PROTECTED]
http://www.wspackaging.com
--
International Webmasters Association
http://iwanet.org
> At 5:03 PM -0700 9/4/01, Chad Berryman wrote:
>> I am trying to create a MySQL SELECT statement using PHP where I am
>> getting a filtered list back by a date in the database formatted as
>> yyyy-mm-dd where the date falls between a one week window that is in
>> constant motion.
>>
>> For example, take todays date (as a julian calendar number) and a
>> date that is set 7 days in the future (also as a julian calendar
>> number and find all rows in the database where the date field (as a
>> julian number) falls between the julian number of the window, i.e.
>> today and one week from today.
>>
>> The SELECT statement that I came up with is this
>>
>> SELECT *,DATE_FORMAT(birthday,'%M %d'),
>> DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d'))
>> FROM table_name
>> WHERE DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d')) >= $yday AND
>> DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d')) <= $yday_hi";
>>
>> The PHP variables $yr, $yday, and $yday_hi are set as
>> $yr is the current 4 digit year
>> $yday is the current julian calendar date for today
>> $yday_hi is the julian calendar date one week in the future.
>
> You're making this way too hard, I suspect. To find all birthdays from
> a target date to the target date plus a week, do this:
>
> ... WHERE birthday >= $target_date
> AND birthday <= DATE_ADD($target_date,INTERVAL 7 DAY)
>
>>
>> This select statement works fine until I get to December 24th of any
>> year, and the $yday_hi goes beyond the actual number of julian days
>> in this year. So I have created an if statement that says if the
>> date in the future is bigger than the julian date for December 31 of
>> this year, then subtract the 2 and set the date in the future as the
>> difference.
>>
>> For example, if there are 364 days this year, and today's julian
>> calendar date is 360, the one week in the future would be (360 + 7)
>> or 367. So subtract 364 from 367 which is 3 and set the $yday_hi
>> variable to 3.
>> This makes the numbers work. So if today is December 30th (or 364)
>> the week in the future date would become Jan 6 (or 6 as a julian
>> date).
>>
>> It is at this time that the select statement look like
>> SELECT ....... WHERE birthday >= 364 (today) AND <= 6 (one week from today)
>>
>> My select statement will ONLY work if $yday is smaller than $yday_hi.
>>
>> Is there a way to make MySQL understand that I need dates at the end
>> of the year, and at the beginning ??
>>
>> I hope that makes sense.
>>
>> Chad
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
