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.

you can get your (the client PC) timezone using javascript

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.

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.

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()

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 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

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)

tmcw - 21 August 2007 05:23 PM

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)

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

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.

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?

No. If you store your date time fields in gmt. Each customer should be able to choose their timezone. You can convert time to a timezone based on its offset from gmt. IE eastern could be -5 gmt. There is a list available if you search timezones it should tell you which are which. IE Search eastern time. It should say -6 or -5 or something from gmt. Subtract that from your gmttime and it will work fine.

Note: You can store your users timezones in session if they are short term… Or along with user data if its long term.
-Cory

It’s a public website, for a school, with a calendar. Selecting timezones is handy, but it’s ridiculous to think that everyone is going to choose one before viewing the site, especially when almost everyone of interest is on the east coast and I can simply say that all times are east coast…

I’m thinking that the code I’ll need is…

gmt_to_local($timestamp, {call to date config object});

It’s a public website, for a school, with a calendar. Selecting timezones is handy, but it’s ridiculous to think that everyone is going to choose one before viewing the site, especially when almost everyone of interest is on the east coast and I can simply say that all times are east coast…

Set a default? ie. - EST

CI’s date helpers are amazing and working with Unix timestamps is infinitely easier the timestamp fields within MySQL.

Oh I see what you mean Just set a config var for it then override it with database if you want to :-p
-Cory

No, not a config var. The config can only hold ‘local’ or ‘gmt’.

Here’s my final word, on how I would do this.
1. Change your date fields to Unix timestamps.
2. Change the application run off gmt, rather than local.
3. Set a timezone field within your user information table in your database, with a default of ‘UM5’ (the EST abbreviation in the Code Igniter Date helper.
4. When you perform your inserts, use Code Igniter’s now() function for the Unix timestamp fields. This will insert GMT times into your database.
5. When you display these dates to the user, run it through gmt_to_local() using the GMT Unix timestamp from the database, the timezone field from the database (which can be changed in the user’s profile, if they want), and whether daylight savings time is in effect.
6. Profit.

It’s really not that hard…

Hell, if you are really bent on keeping the MySQL datestamp/timestamp fields (which are going to cause you problems, I swear by this) - you can just run the returned data through the mysql_to_unix() function.