I'm on the east coast, my database (mysql) server is on the west. How do we resolve our differences?

[eluser]tmcw[/eluser]
I'm using MySQL 5.x and it's apparently on PDT instead of Eastern (UM5) time. I'd like to make this right in some good way such that I don't have to repeat the adjustment everywhere in my code (especially because I'm moving the app to a new server which will most likely be in UM5). I'm storing stuff with DATETIMEs currently...

I'm considering that maybe if I can find somewhere in CodeIgniter where I can write code right after the bootstrapping stuff, so that I can set my user time zone for MySQL. But it looks like setting timezones for mysql globally is not possible on shared hosting (right now, it's Dreamhost, soon it'll be no-f'ing-write-access university servers).

Any ideas? Thanks all.

[eluser]obiron2[/eluser]
you can get your (the client PC) timezone using javascript

Code:

<!--
var tzo=(new Date().gettimezoneOffset()/60)*(-1);
// -->

and if you are using PHP5 you can get the server offset using

timezone_offset_get()

and you should be able to work out the difference from there.

[eluser]tmcw[/eluser]
I understand... but... is it necessary to put time zone correction code in every query, is what I'm saying? Figuring out where I am (all dates will be in Eastern, anyway) is useful, but I'm really looking for the 'best practice' way to store & retrieve dates in the correct timezone.

[eluser]Michael Wales[/eluser]
I store everything as a Unix timestamp in my database (varchar(10) field) - this makes it very easy to use Code Igniter's Date helpers.

For what you are looking for: I would set the time_reference variable in config.php to gmt, then allow each user to set their own timezone, so that timestamps are displayed with their timezone taken into account.

For instance, if I post something at noon my time, it will show noon for me. But for you, on the east coast, it will show 4 PM.

gmt_to_local()

[eluser]tmcw[/eluser]
Hmm, that seems like the right technique, but I don't think it would work. If I insert, say, NOW(), into MySQL, it is in PDT. So if I set the time reference to GMT, and then set timezones, wouldn't it set timezones around PDT? My web server is also in PDT, if that matters.

[eluser]ssjcory[/eluser]
You will want to resolve your differences by converting both of your times to gmt. Trust me if you dont do it early on it will be a pain later. For example where I work now we work with all of our times in est. This causes SO MANY PROBLEMS! We have over a thousand files that need to be changed now just to change the times. Our design isn't great anyways.. but we are refactoring constantly to try and get to a nice standard. Once, Twice, Refactor. You can do this with php timestamp functions. Also if you use PHP 5 they have a new function(can't remember the name though just search time)
-Cory

[eluser]tmcw[/eluser]
Okay... so the gist is...

(Input)

Get form data -> parse into date/time string -> strtotime() -> local_to_gmt() -> database

(Output)

Get data -> strtotime() -> gmt_to_local() -> display


All right, seems okay to me? Thanks guys. Now just to figure out how to set a global timezone, so I don't specify it everywhere. (This is a public website, you know, pages, calendars, so not many options)

[eluser]ssjcory[/eluser]
[quote author="tmcw" date="1187731402"]Okay... so the gist is...

(Input)

Get form data -> parse into date/time string -> strtotime() -> local_to_gmt() -> database

(Output)

Get data -> strtotime() -> gmt_to_local() -> display


All right, seems okay to me? Thanks guys. Now just to figure out how to set a global timezone, so I don't specify it everywhere. (This is a public website, you know, pages, calendars, so not many options)[/quote]

What do you mean a global timezone?
I would wrap those into a class, and use methods to convert the time.
What's good about gmt_to_local is it gets the local timezone and converts the gmt time to that. Why would you need a global timezone? And if you did it should be GMT to conform to the standard (unless your website is generally only for one specific country)
Hope this helps,
Cory

[eluser]Michael Wales[/eluser]

Hmm, that seems like the right technique, but I don’t think it would work. If I insert, say, NOW(), into MySQL, it is in PDT. So if I set the time reference to GMT, and then set timezones, wouldn’t it set timezones around PDT? My web server is also in PDT, if that matters.

You're not inserting MySQL's NOW() into the database. You are inserting the returned data from Code Igniter's now() function within the Date helper.

[eluser]tmcw[/eluser]

You’re not inserting MySQL’s NOW() into the database. You are inserting the returned data from Code Igniter’s now() function within the Date helper.

actually, I have been using MySQL's NOW(). CI's would return a unix timestamp.

What do you mean a global timezone?
I would wrap those into a class, and use methods to convert the time.
What’s good about gmt_to_local is it gets the local timezone and converts the gmt time to that. Why would you need a global timezone? And if you did it should be GMT to conform to the standard (unless your website is generally only for one specific country)
Hope this helps,

Global as in application-wide. My server and database are in PDT, for whatever reason. I'll be storing datetime fields in GMT. I need to retrieve them as Eastern time. See the problem?