mysql.timezone

[minyanmaps]

Is it possible to have the mysql timezone tables populated so that I could set the timezone as something like 'America/New_York' instead of of -05:00?

[redsox9]

Take a look at this thread: http://forum.bluehost.com/showthread.php?t=11585

[minyanmaps]

Thanks for the reply, but I think you might have misunderstood my question. I was asking about MySQL, not PHP. I would like to 1) set the mysql timezone on the fly with something like set time_zone = "America/New_York", and 2) store these TZ values in a column so that I could use CONVERT_TZ. Without the mysql.timezone tables, I would only be able to use values such as "-05:00". Does this make sense?

[Basil]

You can't change the MySQL timezone. You either need to insert it in the time zone you want, or modify it on retrieval.

[SomeSQLGuy]

One thing you might consider is capturing a UTC date in the database and using php to modify that date to display the correct time to the user.

[minyanmaps]

Thanks for responding,
That happens to not be true.
SET time_zone = "-00:00" will put you in GMT.
And even if I couldn't change timezones, I could still use CONVERT_TZ, which I need to use. I just need to use the TZ names, not the offsets.
Please read this (http://dev.mysql.com/doc/mysql/en/time-zone-support.html)

[minyanmaps]

One thing you might consider is capturing a UTC date in the database and using php to modify that date to display the correct time to the user.

The problem is that I have to do this entirely on the SQL end, since this is being done in midst of a query.

[Basil]

How am I wrong, that would be modifying it ON RETRIEVAL (setting the timezone every session) which is one of the things I said you could do. You can't change the timezone of the mysql daemon, which is what you appear to have asked to do.

Please provide an example of what you're actually doing, because you're not making sense here.

[SomeSQLGuy]

One thought that came to mind (if you needed this tonight) is to create the tables on a local system and then just export the data to table you created on bluehost. I realize you burn a table that way, but it might get you up and running in the short term. You could then try the normal support routes with bluehost of getting that running. If anything it might buy you sometime. Downfall is you might need to rewrite code.

[felgall]

There is no limit on the number of tables you can have in the one database on BlueHost - just on the number of databases you can have. Unless you are using Fantastico to install scripts or have some other reason for wanting to keep the data in separate databases you could put everything for 1,000,000 domains all into the one database just by using different prefixes on the table names. Creating your own table to hold the info you require will therefore only cost you the time to set up and maintain that table.

[Basil]

Okay, nevermind. You want the value in the database to be the timezone in words rather than a number (for unknown reasons, since that will make the database unnecessarily bigger). I have no idea how you're putting the values into the database if you aren't using PHP. If you're doing it manually why aren't you just typing out the timezone?

It would be very easy to run a PHP script over the database to convert the values.

[SomeSQLGuy]

There is no limit on the number of tables you can have in the one database on BlueHost - just on the number of databases you can have. Unless you are using Fantastico to install scripts or have some other reason for wanting to keep the data in separate databases you could put everything for 1,000,000 domains all into the one database just by using different prefixes on the table names. Creating your own table to hold the info you require will therefore only cost you the time to set up and maintain that table.

Thanks for clarifying that. For some reason I thought there was a limit on the tables too.

Thanks again.

[minyanmaps]

@Basil
Did you read the link I gave? Its more than just storing it as a word, its automatically factoring things like DST and if Australia/Lord_Howe would suddenly decide to keep the same time as the rest of NSW, Australia (assuming of course that the timezone table and the systems TZ database were kept up to date).
As for taking up more space, if I was worried about that I would just store the TZ name in a foreign key.