Timezones, Timestamps, and Ages

[JTallis]

As you may of guessed, it's a nightmare.

If my birthday is on 10th June GMT, it would still be my birthday on the 11th June in Australian time. Though.. the 11th June, is not my birthday. It's not hard to understand this bit as Australia is like.. many hours ahead. Though when it comes to PHP... if I used some Timezone plug-in then this would bring up the same result as it does now. Though when that persons age is displayed... if my birthday is 10th June at 1AM, in America, it would be the 9th June. There for, they won't see my age change to 18 until it is 10th June their time.

 

To prevent this, is when calculating the persons age, you would be adding their timezone offset onto their birthday timestamp. So regardless on what day it is around the world, if they are celebrating their birthday... then it's their birthday all around the world even though it's different times.

 

To do this, i would need timezone offsets. I would have a field in the database I assume. I would convert their DOB to timestamp. I would add their offset onto that timestamp or take it away and display whatever result that gives to every user. This would display their age as whatever to every user.

 

The next problem is... strtotime works in MM/DD/YYYY, so how do I make it work with DD/MM/YYYY ?

 

I'm actually not making much sense, how would you work with Timestamps and Ages? Also for my next question question, if I had someones DOB, how do I bring up their next birthday out of it for when I am displaying "Bobs birthday is on the..."

 

Now for final, apparently on 19th Jan 2038 the UNIX Timestamp will cease to work due to a 32-bit overflow. How can this be true when converting a date in the year 2500 still brings a timestamp?

 

[jcbones]

strtotime should work with dd/mm/yyyy.  As it is part of the available date_formats

[JTallis]

strtotime should work with dd/mm/yyyy.  As it is part of the available date_formats

 

Unfortunately, nope. Try this out

echo strtotime("24/12/2010") . ' - ' . strtotime("12/24/2010");

The first is DD/MM/YYYY, the second is MM/DD/YYYY.

For me, it would only display the second date in timestamp. I was told the first, strtotime is thinking 24 is a month, but it isn't a month so it fails.

[JTallis]

For an update of the above, with strtotime. Here..

http://www.horble.com/test.php The 2 without timestamps, do not work. The 2 with, do work. I used this code.

 

echo strtotime("2010/24/12") . ' (YYYY/DD/MM)<Br />' . strtotime("12/24/2010") . ' (MM/DD/YYYY)<br />' . strtotime("2010/12/24") . ' (YYYY/MM/DD)<br />' . strtotime("24/12/2010") . ' (DD/MM/YYYY)';

 

All based on 24th December 2010.

[JTallis]

The CSS of this forum has gone crazy with this thread. lol. It's all pushed across >

 

Anyway, along with what I've posted and asked... how can I get upcoming birthdays without having to do hundreds of queries for each user.

[Jumpy09]

I've been working with JTallis trying to figure this out.

 

He was working on one part of the problem, while I am trying to figure out another part of the problem.

 

I'm using DATE on my birthday field.

 

I've come up with the following to limit the query to only pulling information by the people who fall into the dates:

 

$query = $database->execute("SELECT uid, bdaytest FROM `users` WHERE DATE_FORMAT(bdaytest, '%m%d') BETWEEN DATE_FORMAT(NOW(), '%m%d') AND CONCAT(ADDDATE(CURDATE(), INTERVAL 7 DAY), DATE_FORMAT('%m%d')) ORDER BY DATE_FORMAT(bdaytest, '%m%d') ASC");

 

I chose to use ORDER BY in that way to order birthdays from the closest to the furthest.

 

I'm pulling an error which is confusing me:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ORDER BY DATE_FORMAT(bdaytest, '%m%d') ASC' at line 1

 

This line of query isn't in my level of expertise, but using Google you can learn anything.

 

I figure using this query will allow for an Upcoming Birthday Page, not 100% for displaying someone's birthday for their 24 hour period.  I figure though I would use a different query to pull only birthdays for the current date +/- offset within a 48 hour period to allow the showing of a birthday only if it's a person's birthday in their own timezone.

 

If you want to know why we are doing this, I'm more than happy to explain it.

 

So anyone got any suggestions as to why my query isn't working right?

[PFMaBiSmAd]

Birthdays should be stored as a yyyy-mm-dd value (mysql DATE format.) Doing so will allow you to use the many mysql date/time functions directly in the query to do anything you want with the value.

 

Due to the limitations of a Unix Timestamp (and any of the php functions that relies on them, such as strtotime, mktime...) you should not use them for birthdays. Nor would you do anything that involves timezones.

 

What exactly ARE you attempting to get your query to find/return?

[Jumpy09]

What he was looking for was a method of displaying birthdays on a person's actual birthday.

 

$offsettimestamp = $Timestamp + ($offset x 60 x 60)

Would give a different timestamp and if $offsettimestamp > mktime(0, 0, 0, $todaysmonth, $todaysday, $todaysyear) OR $offsettimestamp < mktime(0, 0, 0, $tomorrowsmonth, $tomorrowsday, $tomorrowsyear)

I think that is how it would be done that way, once again this was before we found out about the limitations and I found all the MySQL Date Functions.

 

For instance, if the Server time is set to GMT and you live in Hawaii -10GMT, there would be a complication with it displaying your birthday.

 

At 12AM GMT or 2PM -10GMT your birthday would display all the way to 2PM the next day -10GMT.  Which during the evening of your birthday, your birthday wouldn't be displayed.

 

So he got the idea of using Timezones to display someone's birthday on their actual birthday.

 

So at 12AM GMT your offset would set your day at 2PM the day before.  Which since your birthday isn't until 10 hours from then, through the calculation it wouldn't display your birthday.  Then when it reached 12PM -10GMT it would then display your birthday.

 

I wasn't aware of the Limitation from UNIX Timestamp until a while ago looking into this possibility.  But chances of someone being born before 1901-12-13 (Fri) 15:45:52 (2147483648 or -2147483648) accessing your website is relatively slim, and 2038-01-18 (Mon) 22:14:07 (2147483647) is still 27 years away.  Which I'd assume they would have already come up with a newer system, maybe even a 128-Bit Operating System.  Operating Systems havn't improved that much over the last 10 years, but in the last 20 years they have made significant improvements.

 

So the limitations won't exactly cause a problem anytime in the near future, which allows for the use of timestamps even though the limitation is there.  If a website happens to be online in 27 years, I'm confident that we would be coding in entirely different ways by then.  Modifications could be made when they are appropriate.

 

So in order to customize the display of a person's birthday during the 24 hours they are actually celebrating their birthday, it is relatively easy to use timestamps in this fashion to allow that feature to users of a website.

 

I do also suppose it's easy to just use "DATE_ADD(NOW(), INTERVAL " . $offset . " HOUR", but then the problem comes from snagging the offset to begin with.

 

So my idea, since me and him were working on this together earlier, was to pull the birthdays by DAY and MONTH between NOW and 1 WEEK, which even in GMT that should sort of work.  I'm not exactly counting the 12 hours that still occur once GMT is the next day, which if I can get this query of mine which I posted a couple posts back, then I could edit it to pull NOW() - 1 DAY from GMT.. which would theoretically work... but display birthdays for 12 hours after expiration on the Upcoming Birthdays list. 

 

Then you would use the queries you receive from Upcoming Birthdays and further limit it with PHP to only show birthdays of todays current date depending on user's offset.

 

If I can get the query above working, then I can work on another query to do exactly what he wants to do... attempting to stay away from Timestamps, but still use the OFFSET given by timezones.

[JTallis]

PFMaBiSmAd, how would you display birthdays within a 7 day range... (So it'd display 7 days before their birthday, and would stop displaying after their birthday)

Like this..

User1's birthday is on Sunday, October the 3rd

User2's birthday is on Monday, October the 4th

 

At the moment, we came up with something similar to this..

User1's birthday is on Sunday, October the 5th

User2's birthday is on Tuesday, October the 6th

User3's birthday is on Friday, October the 7th

User4's birthday is on Sunday, October the 8th

 

As you see from this, the day after Tuesday, October the 6th.. is Friday the 7th. So... we kinda screwed up. How would you do it?

 

[jcbones]

strtotime should work with dd/mm/yyyy.  As it is part of the available date_formats

 

Unfortunately, nope. Try this out

echo strtotime("24/12/2010") . ' - ' . strtotime("12/24/2010");

The first is DD/MM/YYYY, the second is MM/DD/YYYY.

For me, it would only display the second date in timestamp. I was told the first, strtotime is thinking 24 is a month, but it isn't a month so it fails.

 

You are correct it doesn't work with dd/mm/yyyy, of course I stated that it did, but the link I sent you to stated that it DOES NOT.

 

IT DOES however accept the following construct.

 

dd\tmm\tYYYY or dd-mm-YYYY or dd.mm.YYYY = day month year seperated by tabs, dots, or dashes.