+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: How to get entries from DB depending on timezone? (/showthread.php?tid=29455)
Pages:
1
2
How to get entries from DB depending on timezone? - El Forum - 04-10-2010
[eluser]esset[/eluser]
I have a small issue I've never been faced with before, and I need some help
I have TV-episodes in a database. They have a name, airdate and airtime. Like this:
Code:
************************************
name | airdate | airtime
************************************
Show 1 | 2010-04-01 | 12:00
Show 2 | 2010-04-02 | 23:00
Show 3 | 2010-04-02 | 23:30
************************************Now I have a calender were I want to display which day the episode airs depending on the users timezone prefs.
So it let's say an american person visits the calendar "Show 2" will be on the 2th, but if a european person visits the calendar "Show 2" might be on the 3th (for him).
Any idea how I can accomplish this?
All ideas are welcomed. Thank you.
How to get entries from DB depending on timezone? - El Forum - 04-10-2010
[eluser]luben[/eluser]
Hello, here is a good article that can help you to solve the problem:
http://blog.redfin.com/devblog/2007/08/getting_the_time_zone_from_a_web_browser.html
BR, Lyuben
How to get entries from DB depending on timezone? - El Forum - 04-10-2010
[eluser]esset[/eluser]
Hi Luben
Thanks for your response. After reading through the page I can't say I'm much further to solving my issue, hehe.
When he say..
Quote:If we store “absolute” times in the database, then displaying times to users becomes a localization issue.
.. he kind of lost me. Does that mean storing the date as if it was GMT +- 0 ? So for "Show 1" which let's say have GMT+3 we store it as 9:00(?), then work from there?
I'll have as he says making the user choose their GMT offset, like +1, +2, -5 or so. But how do I then query my database to get the correct results for the correct day?
Thanks for you help
How to get entries from DB depending on timezone? - El Forum - 04-12-2010
[eluser]luben[/eluser]
Hello, as I understand the article, the author uses javascript to take the date of the user's machine and send it back to the server. However he's making some convertions for his specific needs to prepare US state time zone. Here I found another article, that explains in more simple terms how to get the time zone of a user:
<a href="http://www.onlineaspect.com/2007/06/08/auto-detect-a-time-zone-with-javascript/" target="_blank">http://www.onlineaspect.com/2007/06/08/auto-detect-a-time-zone-with-javascript/</a>
He creates a date-time in javascript (local user's time) and converts it to GMT time (built-in javascript function) and after that he creates one more local date-time and compares both. Also he has come up with how to deal with daylight savings.
This can be used in case you want/can use javascript for that purpose.
I was just curious about the issue but I never really had to deal with that problem before, so I just found these articles as observer of the problem
Best regards and good luck with solving it.
Lyuben
How to get entries from DB depending on timezone? - El Forum - 04-12-2010
[eluser]esset[/eluser]
Thanks for your help Lyuben.
My trouble isn't really detecting the timezone, since i'll let my user choose his/hers timezone from a settings dropdown.
What I wonder is how I store the correct time for the shows, and how I query the database to recieve the correct results depending on the users timezone (GMT offset).
I might add that each show also have it's GMT offset (like GTM +5) in it's own field.
Any ideas regarding that?
Thanks
How to get entries from DB depending on timezone? - El Forum - 04-12-2010
[eluser]danmontgomery[/eluser]
You have the user's GMT offset, and the GMT offset for each show time... What's the dilemma?
Code:
SELECT * FROM my_table WHERE timezone = 'GMT+5'How to get entries from DB depending on timezone? - El Forum - 04-12-2010
[eluser]esset[/eluser]
No no noctrum, you missunderstand me.
I have the offset which lets my know where the user is in the world. Now I want to know the date for which the show airs depending on the user location.
So let's say "Show 1" airs 11pm on the 2th in LA, then it would be the 3rd in Sweden since we are ahead in time. But in my database I only have the date as the airdate in America (2010-04-02) and the GMT+5 offset in another table. So the query someone needs to fetch the show as it would be aired on the 3rd (or some similuar solution... here's were I'm a little lost
)Ideas?
Thanks
How to get entries from DB depending on timezone? - El Forum - 04-12-2010
[eluser]Jelmer[/eluser]
I solve this problem by saving the UNIX timestamp (generated by strtotime()) instead of a formated date. That makes it possible to use dates/times with calculation. That way you save everything in seconds before or after the UNIX epoch (January 1st, 1970 GMT) and it allows for easy conversion to whatever limits you want. All you have to do is caculate the timestamp of you limits (taking the timezone into consideration) to and query using those.
How to get entries from DB depending on timezone? - El Forum - 04-12-2010
[eluser]danmontgomery[/eluser]
[quote author="Jelmer" date="1271125692"]I solve this problem by saving the UNIX timestamp (generated by strtotime()) instead of a formated date. That makes it possible to use dates/times with calculation. That way you save everything in seconds before or after the UNIX epoch (January 1st, 1970 GMT) and it allows for easy conversion to whatever limits you want. All you have to do is caculate the timestamp of you limits (taking the timezone into consideration) to and query using those.[/quote]
It's not really any easier to perform date calculations in unix timestamps than it is a datetime field... Any comparison, formatting, or manipulation is done natively in MySQL with datetime fields, and can be easily converted using strtotime() in PHP... It really just comes down to personal preference.
That said, you might be able to use the CONVERT_TZ() function in mysql, something like:
Code:
SELECT CONVERT_TZ('2010-04-02 23:00:00', '-05:00', '+02:00')http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz
How to get entries from DB depending on timezone? - El Forum - 04-12-2010
[eluser]Jelmer[/eluser]
Quote:It’s not really any easier to perform date calculations in unix timestamps than it is a datetime fieldYour point is assuming he's already using the mysql timestamps isn't it? Which is already better in some ways then saving it seperately (although it might become usefull again if you want to filter at showing times, but hen you should save the times seperatly and the date as timestamps) But even when comparing the UNIX timestamp with the mySQL timestamp, you can still do more calculations with UNIX timestamps in native PHP then with the preformatted mySQL timestamps. Using the date function to convert to readable and the strtotime function to create the timestamps, and calculating with things like strtotime('+1 week').
Also I must admit I like it better to do the work in PHP, instead of within mySQL:
Code:
$start_date = strtotime('2010-04-12 00:00:00 -0500');
$end_date = strtotime('2010-04-12 23:59:59 -0500');
$this->db->from('shows')->where('date >', $start_date)->where('date <', $end_date)->get();Code:
$start_date = strtotime('2010-04-12 00:00:00 +0100');
$end_date = strtotime('2010-04-12 23:59:59 +0100');
$this->db->from('shows')->where('date >', $start_date)->where('date <', $end_date)->get();Either way, whichever you like more, it's always better to save the basis using a standarized format - whether it be a UNIX timestamp or a mySQL timestamp. If you need other methods of selecting, like all the shows that are on at primetime during weekdays, consider those your own indexes and create extra columns that are filled automaticly when creating or editing.