I need to find MySQL records that are after today.. but are in JD

[djbeta]

Hi,

I have a MySQL table that has calendar events in it.. the event dates are in Julian Daycount format... so they look like this: 2453748

I'd like to write a SELECT statement in my php script that selects records from the table that are after today.

I'm having a devil of a time getting my head around all the php & mysql date format commands..

could anyone help me ?

 

[djbeta]

by George.. I think I may have got it..

$month = date('m');
$year = date('Y');
$day = date('d');

$jd = GregorianToJD ($month,$day,$year);

$query = ("select * from calendar where startdate > '$jd') order by startdate ASC;");

how does this look ?

-- the only problem I'm having now is that for some reason this SQL command is finding dates after Feb. 21, 2006, but today is March 5, 2006.. so it looks like it's off by 2 weeks. anyone have an idea of why this would be?

I'm pretty sure the server's date is correct. Perhaps the jd date in the database is off by 2 weeks..

 

[jpadie]

beware - julian days start at gregorian noon. therefore you need to set a time as well as a date.

i would also use the following to obtain the julian date

$jd = GregoriantoJD(date("n"), date("j"), date("Y")); //no leading zeros
//from php.net
$dayfrac = date('G') / 24 - .5;
  if ($dayfrac < 0) $dayfrac += 1;

  //now set the fraction of a day
  $frac = $dayfrac + (date('i') + date('s') / 60) / 60 / 24;
//end from
$jd = $jd + $frac;

and for your sql - i would not use quotes around the julian date as it is a numeral (ensure the correct datatype in the db).

i'm assuming that all of your date parsing is done server side so it is unlikely that the dates in the db should be *relatively* different.

why are you using julian dates rather than a unix timestamp?

 

[djbeta]

Thanks for all the great tips.

I'm using Julian Dates only because they are what is used by the calendar script that we have installed by our workgroup:

http://www.easyphpcalendar.com

Thanks..I'll try to make these changes. I'm not sure I know what you mean about not putting the date in quotes yet... (i'm a newbie).. my guess is that you mean don't include it in the string and parse it with a period ??

thanks again

 

[djbeta]

Oh.. duh (sorry it's early)
I think you mean to remove the single quotes around $jd