sort timestamp by day

[EfEnrico]

is it possible to sort a timestamp by day?

I use the function realdate that stores data in the database like this:

19-06-1981 (date) = 4187 (date stored in database)

I know how to convert it back to a date like 19-06-1981 but if I want to have a overview of peeps that have a birtday in june it sorts them by year.. I want to have it sort it by day.. is that possible?

thnx in advance..

here is the script I use now but doesn't work:

$DeMaanden['01'] = "Januari"; 
while(list($maand,$naam) = each($DeMaanden)) {
echo("<b>$naam</b></td></tr><tr><td class=verjaardagkalender>");
$day = realdate("m");
$query = "SELECT * FROM phpbb_users WHERE user_birthday!=999999 ORDER BY $day";
$resultaat = mysql_query($query) or die ("Fout in query: $query. " . mysql_error());
while($data = mysql_fetch_array($resultaat)) {
$geboortedatum = $data['user_birthday'];
$id = $data['user_id'];
$gebruiker = $data['user_realname'];
$maandgebruiker = realdate ('m',$geboortedatum);
$daggebruiker = realdate ('d', $geboortedatum);
$jaargebruiker = realdate ('Y', $geboortedatum);
if ( $maandgebruiker == $maand ) {
echo ("$daggebruiker <a href='forum/profile.php?mode=viewprofile&u=$id'>$gebruiker [$jaargebruiker]</a><br>");
}}

[RonPK]

I can't find realdate() in the online manual... what is it?

It would probably be a lot easier if you'd use normal dates in the database. The date type was invented for working with dates. If dates are stored like '1993-04-23', your query could be something like

"SELECT * FROM phpbb_users WHERE MONTH(user_birthday) = 4 ORDER BY user_birthday"

to find all born in April.

[EfEnrico]

thnx for your reply...

I would use that if I could.. only the dates are being stored by a very complex script (birthday addon for phpbb) so I am stuck to it..

realdate is an optimized version of the date function.

now I managed it to get work by using a loop that makes the day numbers and checkes if there are values in the database and if yes displays them.

but it is really slowing the script down.. (loop to big).. Maybe you know how to speed it up?

<table border='0' cellspacing='2' cellpadding='5'>
<tr>
<td class=verjaardagmaand>");
$DeMaanden['01'] = "Januari"; 
while(list($maand,$naam) = each($DeMaanden)) {
echo("<b>$naam</b></td>
</tr>
<tr>
<td class=verjaardagkalender>");
$count = 0;
while ($count <= 31) {
$query = "SELECT user_id, user_birthday, user_realname FROM phpbb_users WHERE user_birthday!=999999 ORDER BY user_birthday" ;
$resultaat = mysql_query($query) or die ("Fout in query: $query. " . mysql_error());
while($data = mysql_fetch_array($resultaat)) {
$geboortedatum = $data['user_birthday'];
$id = $data['user_id'];
$gebruiker = $data['user_realname'];
$maandgebruiker = realdate ('m',$geboortedatum);
$daggebruiker = realdate ('d', $geboortedatum);
$jaargebruiker = realdate ('Y', $geboortedatum);
if ( $daggebruiker == $count & $maandgebruiker == $maand ) {
echo ("$daggebruiker <a href='forum/profile.php?mode=viewprofile&u=$id'>$gebruiker [$jaargebruiker]</a><br>");
}}
$count++;
}}
echo("
</td>
</tr>
</table>");

[ergophobe]

4187 (date stored in database)

What form of timestamp is this? As a Unix timestamp, that would be 1 January 1970, just after 1:00 (3600 would be 1:00).

As a MySql timestamp(4) that would be an illegal value (either 1941 or 2041, both of which fall outside the allowed values.

edit: I did some looking: it's just a unix timestamp which is just the date part and divided by 86400

[EfEnrico]

indeed your correct.. here is the function:

// Add function mkrealdate for Birthday MOD
// the originate php "mktime()", does not work proberly on all OS, especially when going back in time
// before year 1970 (year 0), this function "mkrealtime()", has a mutch larger valid date range,
// from 1901 - 2099. it returns a "like" UNIX timestamp divided by 86400, so
// calculation from the originate php date and mktime is easy.
// mkrealdate, returns the number of day (with sign) from 1.1.1970.

function mkrealdate($day,$month,$birth_year)
{
// range check months
if ($month<1 ¦¦ $month>12) return "error";
// range check days
switch ($month)
{
case 1: if ($day>31) return "error";break;
case 2: if ($day>29) return "error";
$epoch=$epoch+31;break;
case 3: if ($day>31) return "error";
$epoch=$epoch+59;break;
case 4: if ($day>30) return "error" ;
$epoch=$epoch+90;break;
case 5: if ($day>31) return "error";
$epoch=$epoch+120;break;
case 6: if ($day>30) return "error";
$epoch=$epoch+151;break;
case 7: if ($day>31) return "error";
$epoch=$epoch+181;break;
case 8: if ($day>31) return "error";
$epoch=$epoch+212;break;
case 9: if ($day>30) return "error";
$epoch=$epoch+243;break;
case 10: if ($day>31) return "error";
$epoch=$epoch+273;break;
case 11: if ($day>30) return "error";
$epoch=$epoch+304;break;
case 12: if ($day>31) return "error";
$epoch=$epoch+334;break;
}
$epoch=$epoch+$day;
$epoch_Y=sqrt(($birth_year-1970)*($birth_year-1970));
$leapyear=round((($epoch_Y+2) / 4)-.5);
if (($epoch_Y+2)%4==0)
{// curent year is leapyear
$leapyear--;
if ($birth_year >1970 && $month>=3) $epoch=$epoch+1;
if ($birth_year <1970 && $month<3) $epoch=$epoch-1;
} else if ($month==2 && $day>28) return "error";//only 28 days in feb.
//year
if ($birth_year>1970)
$epoch=$epoch+$epoch_Y*365-1+$leapyear;
else
$epoch=$epoch-$epoch_Y*365-1-$leapyear;
return $epoch;
}

// Add function realdate for Birthday MOD
// the originate php "date()", does not work proberly on all OS, especially when going back in time
// before year 1970 (year 0), this function "realdate()", has a mutch larger valid date range,
// from 1901 - 2099. it returns a "like" UNIX date format (only date, related letters may be used, due to the fact that
// the given date value should already be divided by 86400 - leaving no time information left)
// a input like a UNIX timestamp divided by 86400 is expected, so
// calculation from the originate php date and mktime is easy.
// e.g. realdate ("m d Y", 3) returns the string "1 3 1970"

// UNIX users should replace this function with the below code, since this should be faster
//
//function realdate($date_syntax="Ymd",$date=0)
//{ return create_date($date_syntax,$date*86400+1,0); }

function realdate($date_syntax="Ymd",$date=0)
{
global $lang;
$i=2;
if ($date>=0)
{
return create_date($date_syntax,$date*86400+1,0);
} else
{
$year= -(date%1461);
$days = $date + $year*1461;
while ($days<0)
{
$year--;
$days+=365;
if ($i++==3)
{
$i=0;
$days++;
}
}
}
$leap_year = ($i==0)? TRUE : FALSE;
$months_array = ($i==0)?
array (0,31,60,91,121,152,182,213,244,274,305,335,366) :
array (0,31,59,90,120,151,181,212,243,273,304,334,365);
for ($month=1;$month<12;$month++)
{
if ($days<$months_array[$month]) break;
}

$day=$days-$months_array[$month-1]+1;
//you may gain speed performance by remove som of the below entry's if they are not needed/used
return strtr ($date_syntax, array(
'a' => '',
'A' => '',
'\\d' => 'd',
'd' => ($day>9)? $day : '0'.$day,
'\\D' => 'D',
'D' => $lang['day_short'][($date-3)%7],
'\\F' => 'F',
'F' => $lang['month_long'][$month-1],
'g' => '',
'G' => '',
'H' => '',
'h' => '',
'i' => '',
'I' => '',
'\\j' => 'j',
'j' => $day,
'\\l' => 'l',
'l' => $lang['day_long'][($date-3)%7],
'\\L' => 'L',
'L' => $leap_year,
'\\m' => 'm',
'm' => ($month>9)? $month : '0'.$month,
'\\M' => 'M',
'M' => $lang['month_short'][$month-1],
'\\n' => 'n',
'n' => $month,
'O' => '',
's' => '',
'S' => '',
'\\t' => 't',
't' => $months_array[$month]-$months_array[$month-1],
'w' => '',
'\\y' => 'y',
'y' => ($year>29)? $year-30 : $year+70,
'\\Y' => 'Y',
'Y' => $year+1970,
'\\z' => 'z',
'z' => $days,
'\\W' => '',
'W' => '') );
}
// End add - Birthday MOD

?>

[RonPK]

Aha, then what about
$query = "SELECT * FROM phpbb_users
WHERE MONTH(FROM_UNIXTIME(user_birthday * 86400, '%Y-%m-%d')) = $month
ORDER BY user_birthday";

[EfEnrico]

great RonPK,

that worked.. now I can only select the specific month from the database..

though still it is ordered by "user_birthday". that makes the list sort it on year instead of month..

how can I have the script sort the birthday's by month?

thnx in advance

[RonPK]

I don't get it... you want a list of all people born in say June. But in what order?

Pete: June 4, 1961
Jack: June 5, 1950
Mary: June 5, 1978
ORDER BY DAYOFMONTH(FROM_UNIXTIME(user_birthday * 86400, '%Y-%m-%d')), YEAR(FROM_UNIXTIME(user_birthday * 86400, '%Y-%m-%d'))

or

Jack: June 5, 1950
Pete: June 4, 1961
Mary: June 5, 1978
ORDER BY user_birthday

You may want to have a look at MySQL's many date functions [dev.mysql.com].

[EfEnrico]

YOU ARE THE MAN RonPK

that first option is exactly what I needed..

sorry for not being clear at first.. it is hard to explain yourself in another language then your native one :)

it now works okidokie!

thnx a big bunch