timezone
Hello! Im having trouble with timezones. Im in Spain, we have 2 different timezone now we are in GMT+2, in winter, this is the GMT+1. Im looking for an instruction which give me the current timezone, but I cant find it! Do you know how can I now it? Thanks! Rocío Gómez Escribano mailto:r.sanc...@ingenia-soluciones.com r.go...@ingenia-soluciones.com Descripción: cid:image002.jpg@01CB8CB6.ADEBA830 Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com
Re: timezone
Have you populated the timezone tables? Run this query if you are not sure. *SELECT COUNT(*) FROM mysql.time_zone_name;* * * ***If it returns 0 then you need to populate the them as per the instructions here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html* Default timezone in mysql is set at server startup to SYSTEM, which means so long as your system clock is correct the MySQL server should be correct. * * On 3 June 2011 09:55, Rocio Gomez Escribano r.go...@ingenia-soluciones.comwrote: Hello! I’m having trouble with timezones. I’m in Spain, we have 2 different timezone now we are in GMT+2, in winter, this is the GMT+1. I’m looking for an instruction which give me the current timezone, but I cant find it! Do you know how can I now it? Thanks! *Rocío Gómez Escribano* r.go...@ingenia-soluciones.com r.sanc...@ingenia-soluciones.com [image: Descripción: cid:image002.jpg@01CB8CB6.ADEBA830] Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
RE: timezone
Im afraid I dont understand you: mysql select count(*) from mysql.time_zone_name; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) But, when I execute: mysql select now(); +-+ | now() | +-+ | 2011-06-03 11:28:00 | +-+ 1 row in set (0.00 sec) Thats correct, in Spain its that time. So, mysql is using the timezone correctly, isnt it? Thanks! Rocío Gómez Escribano mailto:r.sanc...@ingenia-soluciones.com r.go...@ingenia-soluciones.com Descripción: cid:image002.jpg@01CB8CB6.ADEBA830 Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com De: John Daisley [mailto:daisleyj...@googlemail.com] Enviado el: viernes, 03 de junio de 2011 11:18 Para: Rocio Gomez Escribano CC: mysql@lists.mysql.com Asunto: Re: timezone Have you populated the timezone tables? Run this query if you are not sure. SELECT COUNT(*) FROM mysql.time_zone_name; If it returns 0 then you need to populate the them as per the instructions here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html Default timezone in mysql is set at server startup to SYSTEM, which means so long as your system clock is correct the MySQL server should be correct. On 3 June 2011 09:55, Rocio Gomez Escribano r.go...@ingenia-soluciones.com wrote: Hello! Im having trouble with timezones. Im in Spain, we have 2 different timezone now we are in GMT+2, in winter, this is the GMT+1. Im looking for an instruction which give me the current timezone, but I cant find it! Do you know how can I now it? Thanks! Rocío Gómez Escribano r.go...@ingenia-soluciones.com mailto:r.sanc...@ingenia-soluciones.com ¡Error! Nombre de archivo no especificado. Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 http://www.ingenia-soluciones.com www.ingenia-soluciones.com -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: timezone
now() returns the current system time which doesn't really have a great deal to do with time zones. You can check what the current time zone is set to with the following command *show variables like 'time_zone';* but that is likely to return the value '*SYSTEM*' which means it takes the value from the host operating system ( usually set in /etc/timezone ). You can set the time_zone variable either globally or per session to an offset of UTC as follows *SET time_zone='+00:00:00';* *SET GLOBAL time_zone='+00:00:00';* Or you can specify a '*default_time_zone*' in your my.cnf/my.ini options file. You can also set the time_zone variables to a named offset which will then take account of daylight savings times but to do this you must first load the mysql time zone tables. I strongly suggest you read the manual section relating to time zone support which you can find here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html On 3 June 2011 10:27, Rocio Gomez Escribano r.go...@ingenia-soluciones.comwrote: I’m afraid I don’’t understand you: mysql select count(*) from mysql.time_zone_name; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) But, when I execute: mysql select now(); +-+ | now() | +-+ | 2011-06-03 11:28:00 | +-+ 1 row in set (0.00 sec) That’s correct, in Spain it’s that time. So, mysql is using the timezone correctly, isn’t it? Thanks! *Rocío Gómez Escribano* r.go...@ingenia-soluciones.com r.sanc...@ingenia-soluciones.com [image: Descripción: cid:image002.jpg@01CB8CB6.ADEBA830] Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com *De:* John Daisley [mailto:daisleyj...@googlemail.com] *Enviado el:* viernes, 03 de junio de 2011 11:18 *Para:* Rocio Gomez Escribano *CC:* mysql@lists.mysql.com *Asunto:* Re: timezone Have you populated the timezone tables? Run this query if you are not sure. *SELECT COUNT(*) FROM mysql.time_zone_name;* *If it returns 0 then you need to populate the them as per the instructions here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html* Default timezone in mysql is set at server startup to SYSTEM, which means so long as your system clock is correct the MySQL server should be correct. On 3 June 2011 09:55, Rocio Gomez Escribano r.go...@ingenia-soluciones.com wrote: Hello! I’m having trouble with timezones. I’m in Spain, we have 2 different timezone now we are in GMT+2, in winter, this is the GMT+1. I’m looking for an instruction which give me the current timezone, but I cant find it! Do you know how can I now it? Thanks! *Rocío Gómez Escribano* r.go...@ingenia-soluciones.com r.sanc...@ingenia-soluciones.com *¡Error! Nombre de archivo no especificado.* Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- John Daisley Butterfly Information Systems Microsoft SQL Server Database Administrator Certified MySQL 5 Database Administrator Developer Cognos BI Developer \ Administrator Available for short long term contracts Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: timezone
I would also like to point out, as an aside, that Spain doesn't actually have two timezones - it has a single timezone with daylight savings time. The real question is, thus, to figure out wether or not you are on DST or not. That, however, I have no idea how to do - the system takes care of it automagically. I'm not even sure it gets tracked somewhere on the system, you might need to download and parse the rules yourself in your code. Unless, of course, you're talking about the timezone difference between Spain and the Canaries, but you mentioned summer/winter time explicitly, so I don't think so. - Original Message - From: John Daisley daisleyj...@googlemail.com To: Rocio Gomez Escribano r.go...@ingenia-soluciones.com Cc: mysql@lists.mysql.com Sent: Friday, 3 June, 2011 12:08:34 PM Subject: Re: timezone now() returns the current system time which doesn't really have a great deal to do with time zones. You can check what the current time zone is set to with the following command show variables like 'time_zone'; but that is likely to return the value ' SYSTEM ' which means it takes the value from the host operating system ( usually set in /etc/timezone ). You can set the time_zone variable either globally or per session to an offset of UTC as follows SET time_zone='+00:00:00'; SET GLOBAL time_zone='+00:00:00'; Or you can specify a ' default_time_zone ' in your my.cnf/my.ini options file. You can also set the time_zone variables to a named offset which will then take account of daylight savings times but to do this you must first load the mysql time zone tables. I strongly suggest you read the manual section relating to time zone support which you can find here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html On 3 June 2011 10:27, Rocio Gomez Escribano r.go...@ingenia-soluciones.com wrote: -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: timezone
To take account of daylight savings time in MySQL, load the time zone tables and set the `default_time_zone` variable to the named time zone for your country. On 3 June 2011 11:37, Johan De Meersman vegiv...@tuxera.be wrote: I would also like to point out, as an aside, that Spain doesn't actually have two timezones - it has a single timezone with daylight savings time. The real question is, thus, to figure out wether or not you are on DST or not. That, however, I have no idea how to do - the system takes care of it automagically. I'm not even sure it gets tracked somewhere on the system, you might need to download and parse the rules yourself in your code. Unless, of course, you're talking about the timezone difference between Spain and the Canaries, but you mentioned summer/winter time explicitly, so I don't think so. -- *From: *John Daisley daisleyj...@googlemail.com *To: *Rocio Gomez Escribano r.go...@ingenia-soluciones.com *Cc: *mysql@lists.mysql.com *Sent: *Friday, 3 June, 2011 12:08:34 PM *Subject: *Re: timezone now() returns the current system time which doesn't really have a great deal to do with time zones. You can check what the current time zone is set to with the following command *show variables like 'time_zone';* but that is likely to return the value '*SYSTEM*' which means it takes the value from the host operating system ( usually set in /etc/timezone ). You can set the time_zone variable either globally or per session to an offset of UTC as follows *SET time_zone='+00:00:00';* *SET GLOBAL time_zone='+00:00:00';* Or you can specify a '*default_time_zone*' in your my.cnf/my.ini options file. You can also set the time_zone variables to a named offset which will then take account of daylight savings times but to do this you must first load the mysql time zone tables. I strongly suggest you read the manual section relating to time zone support which you can find here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html On 3 June 2011 10:27, Rocio Gomez Escribano r.go...@ingenia-soluciones.com wrote: -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- John Daisley Butterfly Information Systems Microsoft SQL Server Database Administrator Certified MySQL 5 Database Administrator Developer Cognos BI Developer \ Administrator Available for short long term contracts Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
RE: timezone
From: John Daisley [mailto:daisleyj...@googlemail.com] Sent: Friday, June 03, 2011 6:09 AM To: Rocio Gomez Escribano Cc: mysql@lists.mysql.com Subject: Re: timezone now() returns the current system time which doesn't really have a great deal to do with time zones. [JS] I think that statement is confusing – at least, it is to me. Unless you specify otherwise, NOW() returns the current system time in the system’s time zone. Your comment could be interpreted to mean that it doesn’t use any time zone: i.e., that it returns the UTC. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com http://www.the-infoshop.com/ You can check what the current time zone is set to with the following command show variables like 'time_zone'; but that is likely to return the value 'SYSTEM' which means it takes the value from the host operating system ( usually set in /etc/timezone ). You can set the time_zone variable either globally or per session to an offset of UTC as follows SET time_zone='+00:00:00'; SET GLOBAL time_zone='+00:00:00'; Or you can specify a 'default_time_zone' in your my.cnf/my.ini options file. You can also set the time_zone variables to a named offset which will then take account of daylight savings times but to do this you must first load the mysql time zone tables. I strongly suggest you read the manual section relating to time zone support which you can find here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html On 3 June 2011 10:27, Rocio Gomez Escribano r.go...@ingenia-soluciones.com wrote: I’m afraid I don’’t understand you: mysql select count(*) from mysql.time_zone_name; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) But, when I execute: mysql select now(); +-+ | now() | +-+ | 2011-06-03 11:28:00 | +-+ 1 row in set (0.00 sec) That’s correct, in Spain it’s that time. So, mysql is using the timezone correctly, isn’t it? Thanks! Rocío Gómez Escribano mailto:r.sanc...@ingenia-soluciones.com r.go...@ingenia-soluciones.com Descripción: cid:image002.jpg@01CB8CB6.ADEBA830 Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 http://www.ingenia-soluciones.com www.ingenia-soluciones.com De: John Daisley [mailto:daisleyj...@googlemail.com] Enviado el: viernes, 03 de junio de 2011 11:18 Para: Rocio Gomez Escribano CC: mysql@lists.mysql.com Asunto: Re: timezone Have you populated the timezone tables? Run this query if you are not sure. SELECT COUNT(*) FROM mysql.time_zone_name; If it returns 0 then you need to populate the them as per the instructions here http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html Default timezone in mysql is set at server startup to SYSTEM, which means so long as your system clock is correct the MySQL server should be correct. On 3 June 2011 09:55, Rocio Gomez Escribano r.go...@ingenia-soluciones.com wrote: Hello! I’m having trouble with timezones. I’m in Spain, we have 2 different timezone now we are in GMT+2, in winter, this is the GMT+1. I’m looking for an instruction which give me the current timezone, but I cant find it! Do you know how can I now it? Thanks! Rocío Gómez Escribano r.go...@ingenia-soluciones.com mailto:r.sanc...@ingenia-soluciones.com ¡Error! Nombre de archivo no especificado. Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 http://www.ingenia-soluciones.com www.ingenia-soluciones.com -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk -- John Daisley Butterfly Information Systems Microsoft SQL Server Database Administrator Certified MySQL 5 Database Administrator Developer Cognos BI Developer \ Administrator Available for short long term contracts Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Changing the timezone
Hi everyone, since I'm using the shared hosting, I can't change the default timezone for MySql. Question is: is there any query that I could launch in my connect.php before other queries to make my timezone change? For instance, I make a mysql_query(SET CHARACTER_SET_DATABASE='utf8') or die (Unable to change database charset: .mysql_error()); and a mysql_query(SET NAMES 'utf8') or die (Unable to set names: .mysql_error()); Maybe is there a way to change my timezone to Europe/Kiev? Thank you! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Changing the timezone
set time_zone='Europe/Kiev'; -Original Message- From: Andre Polykanine [mailto:an...@oire.org] Sent: Thursday, February 24, 2011 10:23 AM To: mysql@lists.mysql.com Subject: Changing the timezone Hi everyone, since I'm using the shared hosting, I can't change the default timezone for MySql. Question is: is there any query that I could launch in my connect.php before other queries to make my timezone change? For instance, I make a mysql_query(SET CHARACTER_SET_DATABASE='utf8') or die (Unable to change database charset: .mysql_error()); and a mysql_query(SET NAMES 'utf8') or die (Unable to set names: .mysql_error()); Maybe is there a way to change my timezone to Europe/Kiev? Thank you! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Any way to change timezone WITHOUT mysqld restart?
Trust me, I read it. We had an I18N product at my last company and all our time was stored in UTC in mySQL and we'd alter it on the fly for each user. This isn't rocket science. It's done every day in probably many of the sites you visit and don't even know it. To clarify for you (again): * Per-connection time zones. Each client that connects has its own time zone setting, given by the session http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar _time_zone time_zone variable. Initially, the session variable takes its value from the global http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar _time_zone time_zone variable, but the client can change its own time zone with this statement: mysql SET time_zone = timezone; The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#functi on_now NOW() or http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#functi on_curtime CURTIME(), and values stored in and retrieved from http://dev.mysql.com/doc/refman/5.1/en/datetime.html TIMESTAMP columns. Values for http://dev.mysql.com/doc/refman/5.1/en/datetime.html TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. Don't forget to do this stuff too: http://dev.mysql.com/doc/refman/5.1/en/mysql-tzinfo-to-sql.html So if it's not clear by now, you store all your dates/times in UTC (convert them via some script if you didn't start out that way). Then per web page connection, you read the user's profile TZ (presumably from the user session object or some other persistent means), execute that SQL statement above as one of the first things on the page, and FM ensues. All your properly saved mysql rows will display in the LOCAL timezone instead of UTC. You ALSO have to set the TZ in PHP too don't forget or you'll get whacky discrepencies. http://php.net/manual/en/function.date-default-timezone-set.php There's plenty of info on this out there for using PHP MySQL if that's what you're using too... http://www.ferdychristant.com/blog//archive/DOMM-84NEJN _ From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Saturday, October 02, 2010 5:18 AM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: RE: Any way to change timezone WITHOUT mysqld restart? As a matter of fact I did, the real question is : Did you even read my email? I said WITHOUT a restart... The manual states that a restart of the mysqld is required. The reason for the post to such a list is because on many occasions, user have suggestions on some workaround for things that do work in spite of what the manual says. On Fri, 2010-10-01 at 15:42 -0700, Daevid Vincent wrote: Did you even look at the manual? http://lmgtfy.com/?q=mysql+set+timezone First link. -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Friday, October 01, 2010 10:25 AM To: mysql@lists.mysql.com Subject: Any way to change timezone WITHOUT mysqld restart? Any way to change timezone WITHOUT mysqld restart? It would be a lifesaver if there were some way for me not to have to restart because if mysql restarts then I have to go through a lot of other issues with my other apps.
Re: Any way to change timezone WITHOUT mysqld restart?
I suggest you put your glasses on, then. Getting of that horse might help, too. default-time-zone='*timezone*' If you have the SUPERhttp://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_superprivilege, you can set the global server time zone value at runtime with this statement: On Sat, Oct 2, 2010 at 2:18 PM, Bryan Cantwell bcantw...@firescope.comwrote: As a matter of fact I did, the real question is : Did you even read my email? I said WITHOUT a restart... The manual states that a restart of the mysqld is required. The reason for the post to such a list is because on many occasions, user have suggestions on some workaround for things that do work in spite of what the manual says. On Fri, 2010-10-01 at 15:42 -0700, Daevid Vincent wrote: Did you even look at the manual? http://lmgtfy.com/?q=mysql+set+timezone First link. -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Friday, October 01, 2010 10:25 AM To: mysql@lists.mysql.com Subject: Any way to change timezone WITHOUT mysqld restart? Any way to change timezone WITHOUT mysqld restart? It would be a lifesaver if there were some way for me not to have to restart because if mysql restarts then I have to go through a lot of other issues with my other apps. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: Any way to change timezone WITHOUT mysqld restart?
As a matter of fact I did, the real question is : Did you even read my email? I said WITHOUT a restart... The manual states that a restart of the mysqld is required. The reason for the post to such a list is because on many occasions, user have suggestions on some workaround for things that do work in spite of what the manual says. On Fri, 2010-10-01 at 15:42 -0700, Daevid Vincent wrote: Did you even look at the manual? http://lmgtfy.com/?q=mysql+set+timezone First link. -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Friday, October 01, 2010 10:25 AM To: mysql@lists.mysql.com Subject: Any way to change timezone WITHOUT mysqld restart? Any way to change timezone WITHOUT mysqld restart? It would be a lifesaver if there were some way for me not to have to restart because if mysql restarts then I have to go through a lot of other issues with my other apps.
timezone questions
data from one server in timezone A ported to another server in timezone B, what will happen for the records with datetime columns? What is the recommended way of handling this kind of issues? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: timezone questions
-Original Message- From: Elim PDT [mailto:e...@pdtnetworks.net] Sent: Monday, March 29, 2010 5:57 PM To: mysql@lists.mysql.com Subject: timezone questions data from one server in timezone A ported to another server in timezone B, what will happen for the records with datetime columns? If you have a timezone stored on your server, then all dates/times are stored relative to that. If you now change it, say you were in CA and moved to NY, your times retrieved are now actually 3 hours off technically depending. Even if you set your new timezone to NY. mySQL doesn't store the timezone you started with. It just stores that you created the record at noon let's say. That noon is the noon where the server timezone is. This may or may not be an issue for you depending on your application. If you're moving an entire company and this is a local database with respect to the company (say, a time-off tracker, or payroll or something) you may not care. All you care is that at 3pm some user asked for time off. And since you all now live in NY, it's your 3pm local time. Now, having said that, this in most cases is a huge deal since the web is global and rarely do web pages live in such a local setting. More often, there are people from different time zones accessing them. What is the recommended way of handling this kind of issues? Thanks Real sites set their timezone to UTC and store everything in the database as UTC as well. Then you adjust the time on the client based upon their local timezone. Both PHP and mySQL (and other languages) have functions to convert based upon the TZ variable. ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Change clock/timezone time without restart mysql
Hi, I need to change the computer clock (changing the /etc/zoneinfo) but I would not restart de MySQL service. The NOW() still returning the old time... Thanks, Jonas
Re: Change clock/timezone time without restart mysql
Jonas, your information is somewhat incomplete, but still: Jonas Silveira wrote: Hi, I need to change the computer clock (changing the /etc/zoneinfo) but I would From the file name, I assume it is some Unix platform. not restart de MySQL service. The NOW() still returning the old time... Works as designed: A process inherits time zone information when it starts, from the parent and the then valid environment. This also holds for the MySQL server. That is why time zone information contains the info about when daylight saving time starts / ends: A process starting before the change and still running after that will use the correct time only because it got the information (about the change point) already when it started. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
store timezone
Hi All, Is it possible to store the timezone with datetime data type in mysql or any other approach available to achieve this in mysql. Looking for your help. Thanks, Manoj
Re: store timezone
MySQL doesn't support timezones (I think Postgres does). I usually just store dates as Greenwich Mean Time and store the time zone hours offset in a separate field. Brent Baisley On Tue, Jul 28, 2009 at 7:59 AM, Manoj Singhmanojsingh2...@gmail.com wrote: Hi All, Is it possible to store the timezone with datetime data type in mysql or any other approach available to achieve this in mysql. Looking for your help. Thanks, Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: store timezone
Agreed. Store as UTC. You can always convert to the needed timezone during query or during display. See MySQL's CONVERT_TZ() function. On Tue, Jul 28, 2009 at 8:29 AM, Brent Baisley brentt...@gmail.com wrote: MySQL doesn't support timezones (I think Postgres does). I usually just store dates as Greenwich Mean Time and store the time zone hours offset in a separate field. Brent Baisley On Tue, Jul 28, 2009 at 7:59 AM, Manoj Singhmanojsingh2...@gmail.com wrote: Hi All, Is it possible to store the timezone with datetime data type in mysql or any other approach available to achieve this in mysql. Looking for your help. Thanks, Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: RE: what is the proper way to store timezone information?
Boyd, Todd M. wrote:
My concern is whether the time_zone_id is a fixed reference of the
timezone. If the id might (for whatever reason) change in the
future, I'd have to store the timezone name.
http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html
You can convert back and forth using the system's time zone table.
Read the MySQL manual I've linked to above for more information.
I am using just that, and I have also read the manual on the subject,
but nonetheless I have to store the time-zone identifier somewhere and
in some form.
Apparently, you can even reference them by offset from UTC (i.e.,
-6:00 for US Central). The article warns against using the time zone's
text description, but I saw nothing about dangers of offsets or
time_zone_id.
Yeah, in fact that article doesn't even mention the time_zone_id, which
is why I'm hesitant using it as a definite reference to a time zone.
I'd prefer not to use offset, as I would loose the little bit of
geographical info then. ('Europe/Zurich' has the same offset
as 'Europe/Copenhagen'). For now I'm storing the name of the timezone,
but the manual is clearly lacking some info in this respect.
/Per Jessen, Zürich
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: what is the proper way to store timezone information?
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Saturday, June 14, 2008 6:23 AM To: mysql@lists.mysql.com Subject: RE: what is the proper way to store timezone information? Boyd, Todd M. wrote: When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. My concern is whether the time_zone_id is a fixed reference of the timezone. If the id might (for whatever reason) change in the future, I'd have to store the timezone name. http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html You can convert back and forth using the system's time zone table. Read the MySQL manual I've linked to above for more information. Apparently, you can even reference them by offset from UTC (i.e., -6:00 for US Central). The article warns against using the time zone's text description, but I saw nothing about dangers of offsets or time_zone_id. Todd Boyd Web Programmer
RE: what is the proper way to store timezone information?
Boyd, Todd M. wrote: When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. My concern is whether the time_zone_id is a fixed reference of the timezone. If the id might (for whatever reason) change in the future, I'd have to store the timezone name. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what is the proper way to store timezone information?
All, I will be recording timezone information based on user input using the time zone names from mysql.time_zone_name - names like 'America/Los_Angeles'. When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? thanks Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: what is the proper way to store timezone information?
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 5:17 AM To: mysql@lists.mysql.com Subject: what is the proper way to store timezone information? All, I will be recording timezone information based on user input using the time zone names from mysql.time_zone_name - names like 'America/Los_Angeles'. When recording this information, do I store the full name or just the 'time_zone_id' which is present in mysql.time_zone_name ? This is entirely a matter of choice. It's like asking if you should store formatting when you insert phone numbers into a database--is it easier for you to parse back if you do so? If yes, then store the formatting. If no/probably not/I don't need to parse it, then just store it without. I'd say if it's easier (or perhaps more efficient, if this is a concern) for you to simply store the time_zone_id value, then do it that way. It's not as if it won't store your data if you store the time_zone_name... it just might be harder to use as a variable later, being text instead of an identifying number. Again... totally a matter of your preference. Todd Boyd Web Programmer
Re: Timestamps replicating inconsistently depending on local timezone of server?
Rob Wultsch wrote: On Tue, May 13, 2008 at 2:07 PM, Ed W [EMAIL PROTECTED] wrote: I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Sure - but I'm observing the opposite. My datetime is correct in UTC, but the timestamp col has definitely jumped forward one hour. Orig server: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 17:52:53 New server where the localtime variable has been changed: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 18:52:53 Using default mysql client settings on each server to examine the data, so possibly problem is related to client incorrectly adjusting values for display? I then changed the second servers localtime option, restored the same database as before and again replicated the same data across to catch up and this time they show the same values. So basically the value retrieved from the second database is influenced by the localtime options being different on each server *at the time replication occurs* Anyone shed some light on this? Ed W
Re: Timestamps replicating inconsistently depending on local timezone of server?
On Tue, May 13, 2008 at 11:56 PM, Ed W [EMAIL PROTECTED] wrote: Rob Wultsch wrote: On Tue, May 13, 2008 at 2:07 PM, Ed W [EMAIL PROTECTED] wrote: I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Sure - but I'm observing the opposite. My datetime is correct in UTC, but the timestamp col has definitely jumped forward one hour. Orig server: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 17:52:53 New server where the localtime variable has been changed: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 18:52:53 Using default mysql client settings on each server to examine the data, so possibly problem is related to client incorrectly adjusting values for display? I then changed the second servers localtime option, restored the same database as before and again replicated the same data across to catch up and this time they show the same values. So basically the value retrieved from the second database is influenced by the localtime options being different on each server *at the time replication occurs* Anyone shed some light on this? Ed W This sounds like expected behavior to me. If you set the timezone one hour forward a timestamp will be one hour forward. The data stored on the server is the same, and will display the same if you change the timezone. The timezone setting when the insert occurred should have no effect. mysql CREATE TABLE `t1` (`c1` TIMESTAMP,`c2` DATETIME); Query OK, 0 rows affected (0.05 sec) mysql SET time_zone = '+0:00'; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 VALUES(NOW(),NOW()); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t1; +-+-+ | c1 | c2 | +-+-+ | 2008-05-14 19:43:00 | 2008-05-14 19:43:00 | +-+-+ 1 row in set (0.00 sec) mysql SET time_zone = '+1:00'; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 VALUES(NOW(),NOW()); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t1; +-+-+ | c1 | c2 | +-+-+ | 2008-05-14 20:43:00 | 2008-05-14 19:43:00 | | 2008-05-14 20:43:15 | 2008-05-14 20:43:15 | +-+-+ 2 rows in set (0.00 sec) mysql SET time_zone = '+0:00'; Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM t1; +-+-+ | c1 | c2 | +-+-+ | 2008-05-14 19:43:00 | 2008-05-14 19:43:00 | | 2008-05-14 19:43:15 | 2008-05-14 20:43:15 | +-+-+ 2 rows in set (0.00 sec) But I could be completely off the mark. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamps replicating inconsistently depending on local timezone of server?
Rob Wultsch wrote: This sounds like expected behavior to me. If you set the timezone one hour forward a timestamp will be one hour forward. The data stored on the server is the same, and will display the same if you change the timezone. The timezone setting when the insert occurred should have no effect. OK, your example is clearly demonstrating the effect I am seeing - however, by changing the server localtime option I appear to be influencing the default mysql time offset. I still don't understand the reality of what is happening here - your example appears to show that datetime fields are correctly stored as GMT and adjusted as desired, but that a timestamp is a function of localtime? Either way they appear inconsistent... The end result needs to be that I can get these dates out of the database and correctly adjust them for the desired users localtime. What you are demonstrating here is that I either need to ditch all my timestamp columns (inconvenient) or switch the server to only run in UTC (inconvenient in that I need to mentally adjust in order to make sense of the log files). It would appear that if I run the server with a correct localtime then I have a bag of trouble when I want to figure out the time something happened (as you can see c1 and c2 should be the same in all cases, but not in your example) Can anyone shed some light on the best approach? Thanks Ed W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamps replicating inconsistently depending on local timezone of server?
On Wed, May 14, 2008 at 12:55 PM, Ed W [EMAIL PROTECTED] wrote: Rob Wultsch wrote: This sounds like expected behavior to me. If you set the timezone one hour forward a timestamp will be one hour forward. The data stored on the server is the same, and will display the same if you change the timezone. The timezone setting when the insert occurred should have no effect. OK, your example is clearly demonstrating the effect I am seeing - however, by changing the server localtime option I appear to be influencing the default mysql time offset. I still don't understand the reality of what is happening here - your example appears to show that datetime fields are correctly stored as GMT and adjusted as desired, but that a timestamp is a function of localtime? Either way they appear inconsistent... The end result needs to be that I can get these dates out of the database and correctly adjust them for the desired users localtime. What you are demonstrating here is that I either need to ditch all my timestamp columns (inconvenient) or switch the server to only run in UTC (inconvenient in that I need to mentally adjust in order to make sense of the log files). It would appear that if I run the server with a correct localtime then I have a bag of trouble when I want to figure out the time something happened (as you can see c1 and c2 should be the same in all cases, but not in your example) Can anyone shed some light on the best approach? Thanks Ed W The display of the timestamp is dependent on the local time zone. Datetime is not adjusted for display. I don't use timestamp because I think it is Voodoo or some other form of black magic. I don't trust black magic that is not my own (or for that matter anything I write involving pointers). If I want a record to store NOW() then I tell it NOW(). For whatever it is worth I suggest ditching timestamp and going to datetime. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamps replicating inconsistently depending on local timezone of server?
Hi, can anyone explain mysql 5.0.54 handling of replication between two servers with inconstant /etc/localtime (but the same real clock time for UTC) On one server I inserted a new row with created_at and updated_at as the same time. Server localtime is GMT+1, created col is a date, updated_at col is a timestamp When this replicated to the other server which had localtime set to GMT, and then after it replicated I changed localtime to GMT+1 I find that created_at is no longer the same as updated_at - now updated_at is 1 hour later... I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? Thanks Ed W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamps replicating inconsistently depending on local timezone of server?
On Tue, May 13, 2008 at 2:07 PM, Ed W [EMAIL PROTECTED] wrote: I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) http://dev.mysql.com/doc/refman/5.1/en/timestamp.html -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
zipcode to timezone
Hi there, we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? Thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zipcode to timezone
Hiep Nguyen wrote: Hi there, we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? You may be able to call a web service from your PHP code to find this out. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zipcode to timezone
On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=pXERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2Bmap%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave.
Re: zipcode to timezone
On Fri, 6 Jul 2007, David T. Ashley wrote: On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=pXERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2Bmap%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave. i don't think there is any state got 2 timezones, i could be wrong. but if that the case, state - timezone is working for me. now, how do i look up for timezone if i got state? i don't mind buying the database, but i just don't want to replace the database if something change down the road. thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: zipcode to timezone
-Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 10:51 AM To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone i don't think there is any state got 2 timezones, i could be wrong. Yeah, you are. http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.htm INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zipcode to timezone
On 7/6/07, John Trammell [EMAIL PROTECTED] wrote: -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 10:51 AM To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone i don't think there is any state got 2 timezones, i could be wrong. Yeah, you are. http://worldatlas.com/webimage/countrys/namerica/usstates/timezone.htm One other thought comes to mind. Is there a guarantee that a timezone can't split a zipcode? In other words, is the mapping from zipcode-timezone guaranteed to exist in all cases? I remember a friend in Tennessee who would take her children to school (10 miles away) and the school was in a different timezone than her home, so it got very confusing what time the kids had to be up and to bed and all that. In rural areas where the zipcodes can be rather large, I'm just wondering if there is any guarantee that a zipcode can't span two timzones.
RE: zipcode to timezone
I think Indiana has two time zones. I think there are also some states or portions thereof that don't observe daylight saving time. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 11:51 AM To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone On Fri, 6 Jul 2007, David T. Ashley wrote: On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/w ebimage/countrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas. com/webimage/countrys/namerica/usstat es/timezone.htmh=307w=427sz=23tbnid=pXERv6TKqAu7DM:tbnh=9 1tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2Bmap%26um%3D 1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave. i don't think there is any state got 2 timezones, i could be wrong. but if that the case, state - timezone is working for me. now, how do i look up for timezone if i got state? i don't mind buying the database, but i just don't want to replace the database if something change down the road. thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: zipcode to timezone
Indiana has two time zones as I recall. The state is divided roughly in half between the two. Dirk Bremer - Senior Systems Engineer - Utility - AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 10:51 To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone On Fri, 6 Jul 2007, David T. Ashley wrote: On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/co untrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/we bimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=p XERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2B map%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave. i don't think there is any state got 2 timezones, i could be wrong. but if that the case, state - timezone is working for me. now, how do i look up for timezone if i got state? i don't mind buying the database, but i just don't want to replace the database if something change down the road. thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: zipcode to timezone
On Fri, 6 Jul 2007, Dirk Bremer wrote: Indiana has two time zones as I recall. The state is divided roughly in half between the two. Dirk Bremer - Senior Systems Engineer - Utility - AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 10:51 To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone On Fri, 6 Jul 2007, David T. Ashley wrote: On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/co untrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/we bimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=p XERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2B map%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave. i don't think there is any state got 2 timezones, i could be wrong. but if that the case, state - timezone is working for me. now, how do i look up for timezone if i got state? i don't mind buying the database, but i just don't want to replace the database if something change down the road. thanks T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] now, why do they do this??? it doesn't make any sense at all to have multiple timezones in a state, at least to me. men, they have too much time in their hand and this is they came up, two timezones in a state. thank you all, T. Hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: zipcode to timezone
-Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 12:15 PM To: Dirk Bremer Cc: David T. Ashley; mysql@lists.mysql.com Subject: RE: zipcode to timezone now, why do they do this??? it doesn't make any sense at all to have multiple timezones in a state, at least to me. men, they have too much time in their hand and this is they came up, two timezones in a state. Me neither. I was interested to learn that there are noticeable energy savings associated with the Daylight Savings time change. Who'd a thunk it? JT INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Timezone
At 11:45 +0300 10/21/06, Ahmad Al-Twaijiry wrote: Hi everyone is it possible in Mysql 5.0.1 to set the timezone for a user ? PS: I don't have root access to mysql, so I'm looking for away to do it as a normal user. Time zone support is described here: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html Summary: Any client can set its session time_zone value. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Timezone
Hi everyone is it possible in Mysql 5.0.1 to set the timezone for a user ? PS: I don't have root access to mysql, so I'm looking for away to do it as a normal user. --- Ahmad http://www.v-tadawul.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql timezone problem
after changing my system timezone from UTC to MSD i have the following problem: after restarting mysql server its timezone has not changet at all: mysql show variables like '%zone%'; +--++ | Variable_name| Value | +--++ | system_time_zone | UTC| | time_zone| SYSTEM | +--++ meanwhile system command date says the following: # date Tue Mar 28 18:35:45 MSD 2006 OS: FreeBSD 6.0-RELEASE mysql: mysql Ver 14.12 Distrib 5.0.18, for portbld-freebsd6.0 (i386) using 5.0 -- С уважением, Сергей Аверьянов, ООО Паллант Мобайл, Руководитель отдела разработок +7 9272 702841 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql timezone problem
[EMAIL PROTECTED] schrieb: after changing my system timezone from UTC to MSD i have the following problem: after restarting mysql server its timezone has not changet at all: mysql show variables like '%zone%'; +--++ | Variable_name| Value | +--++ | system_time_zone | UTC| | time_zone| SYSTEM | +--++ This is not necessarily wrong. See if select now(); gives you the correct time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe and timezone settings
Thanks for the help, but this isn't my problem. When you start the server as shown below, the SYSTEM Timezone is used for the MySQL server. This could be seen when executing the query select @@global.time_zone on the server. Than you must get a SYSTEM in your data. The problem is that I wan't to start the server with a different timezone than the system one, which can be done by starting the server with --timzeone=.. Regards Michael Dominik Klein wrote: This was done as root and shows that TZ works. dk:/usr/local/mysql # bin/mysql -V bin/mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0 dk:/usr/local/mysql # echo $TZ dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 802 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 09:26:35 | +-+ 1 row in set (0.05 sec) mysql Bye dk:/usr/local/mysql # support-files/mysql.server stop Shutting down MySQL...STOPPING server from pid file /usr/local/mysql/data/dk.pid 060327 09:26:45 mysqld ended done [1]+ Donebin/mysqld_safe --user=mysql dk:/usr/local/mysql # export TZ=America/Argentina/Mendoza dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 889 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 04:27:09 | +-+ 1 row in set (0.00 sec) mysql Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: mysqld_safe and timezone settings]
The starting of the Server with the timezone settings works fine after setting the TZ variable. I always looked at the output from select @@global.time_zone. This was SYSTEM and so I beliefed the timezone wasn't set right on the server. Thanks and Regards Michael Thanks for the help, but this isn't my problem. When you start the server as shown below, the SYSTEM Timezone is used for the MySQL server. This could be seen when executing the query select @@global.time_zone on the server. Than you must get a SYSTEM in your data. The problem is that I wan't to start the server with a different timezone than the system one, which can be done by starting the server with --timzeone=.. Regards Michael Dominik Klein wrote: This was done as root and shows that TZ works. dk:/usr/local/mysql # bin/mysql -V bin/mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0 dk:/usr/local/mysql # echo $TZ dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 802 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 09:26:35 | +-+ 1 row in set (0.05 sec) mysql Bye dk:/usr/local/mysql # support-files/mysql.server stop Shutting down MySQL...STOPPING server from pid file /usr/local/mysql/data/dk.pid 060327 09:26:45 mysqld ended done [1]+ Donebin/mysqld_safe --user=mysql dk:/usr/local/mysql # export TZ=America/Argentina/Mendoza dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 889 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 04:27:09 | +-+ 1 row in set (0.00 sec) mysql Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe and timezone settings
This was done as root and shows that TZ works. dk:/usr/local/mysql # bin/mysql -V bin/mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0 dk:/usr/local/mysql # echo $TZ dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 802 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 09:26:35 | +-+ 1 row in set (0.05 sec) mysql Bye dk:/usr/local/mysql # support-files/mysql.server stop Shutting down MySQL...STOPPING server from pid file /usr/local/mysql/data/dk.pid 060327 09:26:45 mysqld ended done [1]+ Donebin/mysqld_safe --user=mysql dk:/usr/local/mysql # export TZ=America/Argentina/Mendoza dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 889 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 04:27:09 | +-+ 1 row in set (0.00 sec) mysql Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe and timezone settings
Thanks for your help, but this is the problem I have. I can't specify the timezone right when I start mysql with mysqld_safe --user=mysql --timezone=America/Argentina/Mendoza or by setting the TZ enviroment variable and than start the server. The timezone setting is ignored every time I start my server and the timezone is set to SYSTEM, which is Europe/Berlin for my computer. The only way to change is to connect to the running server and execute set global time_zone='America/Argentina/Mendoza'; After this the timezone of the server is right. I wan't to know if anybody else has this problem or maybe another solution to set the right timezone by starting my server. Maybe I make something wrong, but I don't know what this could be. If anybody has a solution please tell it to me, it isn't very useful to set the timezone manually after every restart. Regards Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe and timezone settings
or what I although could do to start my Server in another than the SYSTEM timezone? I use the MySQL 5.0.18 Server on a Suse Linux 10.0 From: http://dev.mysql.com/doc/refman/5.0/en/timezone-problems.html You can set the time zone for the server with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld. So try inserting export TZ=America/Argentina/Mendoza to the beginning of your mysql-(rc)startscript. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_safe and timezone settings
Hello everybody, I have a problem with replication of data from master to slave server. The problem is, that the master is in a other timezone than the slave and so inserts with using the now() function creates different values on master and slave. If I want to update on the master and use the time as criterion the slave doesn't get the update. After searching the internet the problem is, that the MySQL Server starts with the default system timezone. But this could be changed. So configured my slave server so, that it has the same timezone as the master server. For doing this I used the: set global time_zone='America/Argentina/Mendoza'; command. After changing the timezone everything works fine and all data is replictated correctly. The problem is, that I must configure it manually every time the server restarts and I want to do it automatically by starting. But this doesn't work. I tried to use the mysqld_safe --user=mysql --timezone=America/Argentina/Mendoza command, but the server starts with SYSTEM timezone. I although put an entry in my.cnf configuration file which looks: #[mysqld_safe] timezone = America/Argentina/Mendoza But this doesn't work to. Can anybody tell me what's my error, or what I although could do to start my Server in another than the SYSTEM timezone? I use the MySQL 5.0.18 Server on a Suse Linux 10.0 Regards Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timezone settings
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html [EMAIL PROTECTED] wrote: Dear Friends, I need to do the timezone settings so that now() gives the system time.Actually first i have installed mysql on a different timezone han changed the system time zone but perhaps mysql shows the previous time zone or the default time zone only. Pl. tell me how to change that . I shall be very grateful. -- Regards Abhishek jain mail2web - Check your email from the web at http://mail2web.com/ . -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timezone settings
Dear Friends, I need to do the timezone settings so that now() gives the system time.Actually first i have installed mysql on a different timezone han changed the system time zone but perhaps mysql shows the previous time zone or the default time zone only. Pl. tell me how to change that . I shall be very grateful. -- Regards Abhishek jain mail2web - Check your email from the web at http://mail2web.com/ . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 timezone
Hello. In mysql-5.0.15 it works. Put, say, default_time_zone='+10:00' in [mysqld] section of your file. Raphaël 'SurcouF' Bordet wrote: Hi, I want to set up a different timezone that my operating system for MySQL upper to 4.1. According to this article[1], timezone system variable was changed since 4.1.3. I'm using mysql 4.1.11a from Debian sarge. I was tried to set system_time_zone into /etc/mysql/my.cnf unsuccessfully. The only way I found to set up this timezone is by add --default-time-zone option to boot-up script of mysql. Why system variable doesn't work into my.cnf ? Best regards, --=20 Rapha=C3=ABl 'SurcouF' Bordet http://debianfr.net/ | surcouf at debianfr dot net =20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 timezone
Le jeudi 27 octobre 2005 à 01:12 +0300, Gleb Paharenko a écrit : Hello. In mysql-5.0.15 it works. Put, say, default_time_zone='+10:00' in [mysqld] section of your file. This option works too under MySQL 4.1.11: [mysqld] default_time_zone=UTC mysql SELECT @@global.time_zone, @@session.time_zone; ++-+ | @@global.time_zone | @@session.time_zone | ++-+ | UTC| UTC | ++-+ 1 row in set (0.00 sec) Thanks. -- Raphaël 'SurcouF' Bordet http://debianfr.net/ | surcouf at debianfr dot net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 timezone
Hi, I want to set up a different timezone that my operating system for MySQL upper to 4.1. According to this article[1], timezone system variable was changed since 4.1.3. I'm using mysql 4.1.11a from Debian sarge. I was tried to set system_time_zone into /etc/mysql/my.cnf unsuccessfully. The only way I found to set up this timezone is by add --default-time-zone option to boot-up script of mysql. Why system variable doesn't work into my.cnf ? Best regards, -- Raphaël 'SurcouF' Bordet http://debianfr.net/ | surcouf at debianfr dot net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timezone setting wrong?
I've got a RHEL3 server I just installed with mysql 4.0.16. The hardware clock and system clock are both set to UTC and show the correct time. If I do a select Now(); from mysql it show's the correct time However, Unixtimestamp fields written to a table all are an hour off. They're one hour ahead. All the data on this system is replicated from a master. The master is set correctly, hwclock and sysclock at UTC. The same query: Select max(from_unixtime(timestamp_field)) from table; Run on both servers returns a result 1 hour ahead on the new slave. A show variables on the new slave returns *** 120. row *** Variable_name: timezone Value: IST I'm sure I've missed something simple here but what? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timezone setting wrong?
-Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 21, 2005 08:55 To: mysql@lists.mysql.com Subject: Timezone setting wrong? I've got a RHEL3 server I just installed with mysql 4.0.16. The hardware clock and system clock are both set to UTC and show the correct time. If I do a select Now(); from mysql it show's the correct time However, Unixtimestamp fields written to a table all are an hour off. They're one hour ahead. All the data on this system is replicated from a master. The master is set correctly, hwclock and sysclock at UTC. The same query: Select max(from_unixtime(timestamp_field)) from table; Run on both servers returns a result 1 hour ahead on the new slave. A show variables on the new slave returns *** 120. row *** Variable_name: timezone Value: IST I'm sure I've missed something simple here but what? Thanks, Jeff Ok, update, show variables on the master returns timezone of GMT, the slave IST. The master is on RH9 and the slave on RHEL3. So on mysql 4.0.16 where do I force the timezone to be GMT? I've tried putting TZ = GMT and TZ = UTC into the my.cnf file but then mysql won't even start. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timezone setting wrong?
I've tried putting TZ = GMT and TZ = UTC into the my.cnf file but then mysql won't even start. These should be set as environment variables, not as configuration options. You could also try setting time_zone system variable with set @@time_zone=GMT. Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timezone setting wrong?
I use this in my.cnf (along with mysqld_multi settings fwiw) and it works great (mysql version 4.0.X): [mysqld_safe] timezone = GMT It depends on how you start up mysqld.. If you don't use mysqld_safe, the above wont work for you. Atle - Flying Crocodile Inc, Unix Systems Administrator On Wed, 21 Sep 2005, Petr Chardin wrote: I've tried putting TZ = GMT and TZ = UTC into the my.cnf file but then mysql won't even start. These should be set as environment variables, not as configuration options. You could also try setting time_zone system variable with set @@time_zone=GMT. Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timezone query similiar to pgsql
Hi List, Does MySQL have something similiar to SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Canada/Pacific'; I need to handle the timestamp in the database. Previous posts and some googling suggests it should be in the PHP layer which is not an option for me. My users come from various timezones, and so I plan to store everything in GMT (server time) and select the time based on their timezone preference which is stored in a user preference table. Suggestions always welcome :) Thanks Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timezone query similiar to pgsql
In the last episode (Sep 10), Terence said: Does MySQL have something similiar to SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Canada/Pacific'; I need to handle the timestamp in the database. Previous posts and some googling suggests it should be in the PHP layer which is not an option for me. My users come from various timezones, and so I plan to store everything in GMT (server time) and select the time based on their timezone preference which is stored in a user preference table. Start at http://dev.mysql.com/doc/mysql/en/time-zone-support.html and http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html (especially CONVERT_TZ() ). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeZone
Joseph Cochran wrote: Some countries have multiple timezones, so it is not sufficient to know the country code in order to get the timezone. If they have previously posted the timezone, however, then it should be possible to store that information in a cookie on the client machine that your web layer can retrieve. If you want to permanently tie a timezone to a user (assuming that this is an internal system or other system to which your users authenticate -- if it is a public website you're going to have to use cookies), simply include an extra column in the user's record that has a number that stores its differential from GMT (so the USA east coast would be -5) and save all of your data in GMT, applying the timezone column to the time via datetime functions either in the query or in your web layer. One more complication: daylight savings time are not the same world wide. So I would store the time zone and not the difference with GMT. I personally would do al the time zone calculations in the web layer. Most OSs have libraries with more or less knowledge about daylight savings in various countries/timezones. Using the functions in the language of the web layer you're more likely to get things right. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TimeZone
Hi, There is a request from mgmt, when user browse the request (web), the database will return the requested timestamp at their timezone that previously posted. How do i do that? Do I need store whole country codes together timezones in database mysql ? Is there any way to find full country code together with their timezone? Cheers KH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeZone
Some countries have multiple timezones, so it is not sufficient to know the country code in order to get the timezone. If they have previously posted the timezone, however, then it should be possible to store that information in a cookie on the client machine that your web layer can retrieve. If you want to permanently tie a timezone to a user (assuming that this is an internal system or other system to which your users authenticate -- if it is a public website you're going to have to use cookies), simply include an extra column in the user's record that has a number that stores its differential from GMT (so the USA east coast would be -5) and save all of your data in GMT, applying the timezone column to the time via datetime functions either in the query or in your web layer. -- Joe On 8/8/05, KH [EMAIL PROTECTED] wrote: Hi, There is a request from mgmt, when user browse the request (web), the database will return the requested timestamp at their timezone that previously posted. How do i do that? Do I need store whole country codes together timezones in database mysql ? Is there any way to find full country code together with their timezone? Cheers KH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timezone questions
Dear list, I never tried any timezone features(options) of mysql and like to learn from you. (1) In what situation one need to set mysql timezone? (I know there are application level timezone solutions) (2) What the effect if a custome timezone is settled with mysql? I use mysql for web applications and need to deal with timezone at web page generation level. Also I hope my datetime related data in the database be server timezone independent so that the data can be replicated among database servers located in possibly different zones. Thanks -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 2/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
server timezone and system timezon
Hi, I'm looking for help on timezone issues. I'm using mysql for a web application about time related events. I've two servers in different locations with different timezones. How to setup the database server so that the datetime value will not depend on the server machine's timezone? In other words, how to make the datetime value of the same record fetched from both mysql servers unaffected by locations? Thanks for any helps.
Re: server timezone and system timezon
Have the location selecting adjust the timezone when it selects. Example select t + interval 6 hour from t -- Eric Bergen [EMAIL PROTECTED] On Mon, 27 Sep 2004 12:24:36 -0600, Elim Qiu [EMAIL PROTECTED] wrote: Hi, I'm looking for help on timezone issues. I'm using mysql for a web application about time related events. I've two servers in different locations with different timezones. How to setup the database server so that the datetime value will not depend on the server machine's timezone? In other words, how to make the datetime value of the same record fetched from both mysql servers unaffected by locations? Thanks for any helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] moving database to server in new timezone
I'm, using mysqldump to dump a complete database on a server in The Netherlands and want to load the generated SQL onto a server in the USA to make an exact copy of the database. However, I have Timestamp(14) fields that are dumped as e.g. 2004101015 local time and is read as local time too on the USA server. Ie, the time is then off by 10 odd hours. How can I dump the database to generate SQL timestamps in GMT and also make it being read as GMT on the other side? Thanks __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] moving database to server in new timezone
On 12-Apr-2004 Riaan Oberholzer wrote: I'm, using mysqldump to dump a complete database on a server in The Netherlands and want to load the generated SQL onto a server in the USA to make an exact copy of the database. However, I have Timestamp(14) fields that are dumped as e.g. 2004101015 local time and is read as local time too on the USA server. Ie, the time is then off by 10 odd hours. How can I dump the database to generate SQL timestamps in GMT and also make it being read as GMT on the other side? A. run both servers on the same time-zone. B. load the data and run the query: UPDATE tbl SET ts= DATE_SUB(ts, INTERVAL 10 HOUR) WHERE ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting the timezone
Hi. I am trying to change the timezone for MySQL (version 3.23.56) but it does not seem to work. Currently the timezone is set to MDT (I think its the default for MySQL). I added the following line to /etc/my.cnf at the [mysqld] section: set-variable = timezone=CST But when I try to stop and start the daemon, it does not want to start: --- Starting mysqld daemon with databases from /usr/local/mysql/var 031013 10:04:04 mysqld ended --- It will only start if I remove the timezone line from my.cnf, but then the timezone stays at MDT. Thanks for your time. Kind regards, Juan Antonio Ruiz Zwollo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting the timezone
Hi, I am using these lines in my.cnf at [mysqld_safe] section. [mysqld_safe] timezone = GMT It works fine for me. Best regards, Mikhail. - Original Message - From: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 13, 2003 5:38 PM Subject: Setting the timezone Hi. I am trying to change the timezone for MySQL (version 3.23.56) but it does not seem to work. Currently the timezone is set to MDT (I think its the default for MySQL). I added the following line to /etc/my.cnf at the [mysqld] section: set-variable = timezone=CST But when I try to stop and start the daemon, it does not want to start: --- Starting mysqld daemon with databases from /usr/local/mysql/var 031013 10:04:04 mysqld ended --- It will only start if I remove the timezone line from my.cnf, but then the timezone stays at MDT. Thanks for your time. Kind regards, Juan Antonio Ruiz Zwollo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting the timezone
Hi, Do you use version 4 or 3? I am using version 4.0.14. I thought that I need to use set-variable = timezone = CST for it to work under MySQL 3. MySQL docs: ...set-variable = variable=value This is equivalent to --set-variable variable=value on the command-line. Please note that --set-variable is deprecated since MySQL 4.0; as of that version, program variable names can be used as option names. On the command line, just use --variable=value. In an option file, use variable=value... http://www.mysql.com/doc/en/Option_files.html IMHO the problem is that you have put timezone variable into [mysqld] section, but you need to put it into [mysqld_safe] section. But I am not sure. Check it, please. Best regards, Mikhail. - Original Message - From: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 13, 2003 11:01 PM Subject: Re: Setting the timezone Hi: Do you use version 4 or 3? I thought that I need to use set-variable = timezone = CST for it to work under MySQL 3. Also, does anybody know if CST is recognized? Or do I have to use GMT-6 or something like that? Thanks for your time and help. King regards, Juan Antonio - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] To: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, October 13, 2003 10:50 AM Subject: Re: Setting the timezone Hi, I am using these lines in my.cnf at [mysqld_safe] section. [mysqld_safe] timezone = GMT It works fine for me. Best regards, Mikhail. - Original Message - From: Juan Antonio Ruiz Zwollo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 13, 2003 5:38 PM Subject: Setting the timezone Hi. I am trying to change the timezone for MySQL (version 3.23.56) but it does not seem to work. Currently the timezone is set to MDT (I think its the default for MySQL). I added the following line to /etc/my.cnf at the [mysqld] section: set-variable = timezone=CST But when I try to stop and start the daemon, it does not want to start: --- Starting mysqld daemon with databases from /usr/local/mysql/var 031013 10:04:04 mysqld ended --- It will only start if I remove the timezone line from my.cnf, but then the timezone stays at MDT. Thanks for your time. Kind regards, Juan Antonio Ruiz Zwollo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting the timezone
At 1:25 +0200 10/14/03, Mikhail Entaltsev wrote: Hi, Do you use version 4 or 3? I am using version 4.0.14. I thought that I need to use set-variable = timezone = CST for it to work under MySQL 3. MySQL docs: ...set-variable = variable=value This is equivalent to --set-variable variable=value on the command-line. Please note that --set-variable is deprecated since MySQL 4.0; as of that version, program variable names can be used as option names. On the command line, just use --variable=value. In an option file, use variable=value... http://www.mysql.com/doc/en/Option_files.html IMHO the problem is that you have put timezone variable into [mysqld] section, but you need to put it into [mysqld_safe] section. But I am not sure. Check it, please. That's correct. --timezone is a mysqld_safe option, not a mysqld option. Best regards, Mikhail. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MySQL store TimeZone and DayLight Saving Time changes ?
Hello, We are running MySQL server Ver 3.23.8 on a Linux system. We have a table that contains a field whose type is MODIFIED | timestamp(14) We would like to find out if there an easy way in MySQL to convert the date that is on the MODIFIED field (timestamp) to GMT time without an external program. Does MySQL store TimeZone and DayLight Saving Time changes ? Or does it have a way to find it once you have a field whose type is timestamp ? Thanks a lot in advance for your help, Patrick, Mariella - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Timezone question
Hello, I am using the now() function to insert the current timestamp in a mysql table. My problem is that the server is using EST while I would like the time to reflect PST. Can this be done? Thanks. --Kevin [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone question
Kevin, Monday, June 10, 2002, 5:20:00 PM, you wrote: K I am using the now() function to insert the current timestamp in a mysql K table. K My problem is that the server is using EST while I would like the time to K reflect PST. K Can this be done? K Thanks. It depends on what OS do you use. If you use *nix, it's possible. Run mysqld with --timezone option. If you use Windows you can only set up environment variable. K --Kevin K [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone question
Yes. MySQL attempts to get the current time zone from the OS, but this can be overridden by setting the TZ variable. (The manual suggests doing this in the safe_mysqld script; there's already code in there for setting it from a command line argument.) Valid settings of TZ are technically not time zones but rather time locales, which on linux at least correspond to paths relative to /usr/share/zoneinfo, such as America/Los_Angeles or US/Pacific. Note, however, that this causes problems since most locales actually refer to two time zones- one daylight and one standard- so MySQL will sneakily change time zones behind your back without telling you. If this will cause you problems I suggest staying away from these nefarious locales and using one of the single-zone locales in Etc/, such as Etc/GMT-8. -rob On 10/6/02 at 7:20 am, Kevin [EMAIL PROTECTED] wrote: Hello, I am using the now() function to insert the current timestamp in a mysql table. My problem is that the server is using EST while I would like the time to reflect PST. Can this be done? Thanks. --Kevin [EMAIL PROTECTED] Can be done on the fly as part of a query? Thanks. --Kevin [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: client dependent timezone
Hello Maxim, There's a bit of confusion in this discussion: Do you (a) want to have several databases running on the db-server, and all set to different time zones, or (b) want each client to see db-stored times stated in his/her local timezone? (a) AFAIK (and I claim no special expertise here) a single instance of MySQL will only run in a single TZ - I believe that it is possible to run the MySQL in a different TZ to that set at the OpSys/hardware level. That being the case, you likely have to investigate running multiple instances of MySQL to have Byelorussian time, GMT, and NY Time MySQL servers (for example). (b) MySQL is 'server side' coding. Other tools would be required to ascertain client-side information, such as 'user-local time'. Once the local TZ was ascertained, it would be a simple matter to compare that to the server's TZ, and modify all retrieved/entered time values accordingly. I must admit I haven't bothered. For international applications (lazily or sensibly depending...) I simply state that all times are UTC! (with the exception of my family calendar site, where all times (where stated) are 'local' (to the person's/event's location, eg the times a plane flight leaves and arrives)) You will notice that most sites settle for quoting server-local time (no matter how obscure the combination of their location and the user's) and leave the poor user to work out the math. NB contrary to parochial attitudes, most people find converting their local TZ to and from UTC to be easier than working out what the relationship is between two 'local times', eg NZST and PST (mainly because they probably have to compare both TZ values to UTC first and then do the double-math!?) If you are going for (b), what you propose makes good sense. I will be interested to hear how you get on/interested to discuss it further. Regards, =dn But how people build web-applications then ? How can you use web-hosting in GMT+1 if web-application will be in use in GMT+5 for example? Have anybody an experience of resolving this problem (without embedding this functionality to backend app, because backend app is allready contains ~2 lines of code, and somewhere near to 1000 queries). If someone knows how to play this around - please help! Friday, March 29, 2002, 3:02:57 AM, you wrote: GRJ Per client? Per database? The only timezone setting in the MySQL manual is GRJ for the server (tz), and permits you to set it to your local timezone so it GRJ does not return values in GMT. GRJ If you want clients to have different timezones, perhaps you should write GRJ that capabilty into your front-end application. GRJ Gerald Jensen GRJ - Original Message - GRJ From: Maxim Vysotskiy [EMAIL PROTECTED] GRJ To: [EMAIL PROTECTED] GRJ Sent: Thursday, March 28, 2002 6:46 PM GRJ Subject: client dependent timezone GRJ Hello mysql, GRJ So nobody here (even at MySQL AB) knows how to set a timezone for a GRJ MySQL client ? Not for whole server but per client? (or maybe for GRJ database???) GRJ I already posted this question here few days ago - and didn't get any GRJ reply. GRJ Please! Somebody help! GRJ -- GRJ Best regards, GRJ Maxim mailto:[EMAIL PROTECTED] -- Best regards, Maximmailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[4]: client dependent timezone
Hello DL, I know about the possibility of having different TimeZones for different servers (the mysql manual states that it's possible through setting TZ environment variable). But my situation is that I have a shared webhosting in GMT+1 (and mysql TZ is GMT+1), but application needs to have a GMT+2. And now application is almost finished. And this is the only problem (because there are lots and lots of queries allready designed and so on, so I don't want to rebuild almpst whole app). But it looks like I will have to. Or will pay my webhosting provider some extrabucks to have another instance of MySQL server. But that's very strange, that RDBMS server have no such localization feature. very strange. Can anybody from MySQL AB say when will they implement this? Thanx for all responses. I will notify the list if I'll find out some workarounds with this Friday, March 29, 2002, 1:16:03 PM, you wrote: DN Hello Maxim, DN There's a bit of confusion in this discussion: Do you (a) want to have DN several databases running on the db-server, and all set to different DN time zones, or (b) want each client to see db-stored times stated in DN his/her local timezone? DN (a) AFAIK (and I claim no special expertise here) a single instance of DN MySQL will only run in a single TZ - I believe that it is possible to DN run the MySQL in a different TZ to that set at the OpSys/hardware level. DN That being the case, you likely have to investigate running multiple DN instances of MySQL to have Byelorussian time, GMT, and NY Time MySQL DN servers (for example). DN (b) MySQL is 'server side' coding. Other tools would be required to DN ascertain client-side information, such as 'user-local time'. Once the DN local TZ was ascertained, it would be a simple matter to compare that to DN the server's TZ, and modify all retrieved/entered time values DN accordingly. DN I must admit I haven't bothered. For international applications (lazily DN or sensibly depending...) I simply state that all times are UTC! (with DN the exception of my family calendar site, where all times (where stated) DN are 'local' (to the person's/event's location, eg the times a plane DN flight leaves and arrives)) DN You will notice that most sites settle for quoting server-local time (no DN matter how obscure the combination of their location and the user's) and DN leave the poor user to work out the math. NB contrary to parochial DN attitudes, most people find converting their local TZ to and from UTC to DN be easier than working out what the relationship is between two 'local DN times', eg NZST and PST (mainly because they probably have to compare DN both TZ values to UTC first and then do the double-math!?) DN If you are going for (b), what you propose makes good sense. I will be DN interested to hear how you get on/interested to discuss it further. DN Regards, DN =dn But how people build web-applications then ? How can you use web-hosting in GMT+1 if web-application will be in use in GMT+5 for example? Have anybody an experience of resolving this problem (without embedding this functionality to backend app, because backend app is allready contains ~2 lines of code, and somewhere near to 1000 queries). -- Best regards, Maximmailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
client dependent timezone
Hello mysql, So nobody here (even at MySQL AB) knows how to set a timezone for a MySQL client ? Not for whole server but per client? (or maybe for database???) I already posted this question here few days ago - and didn't get any reply. Please! Somebody help! -- Best regards, Maxim mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: client dependent timezone
Per client? Per database? The only timezone setting in the MySQL manual is for the server (tz), and permits you to set it to your local timezone so it does not return values in GMT. If you want clients to have different timezones, perhaps you should write that capabilty into your front-end application. Gerald Jensen - Original Message - From: Maxim Vysotskiy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 28, 2002 6:46 PM Subject: client dependent timezone Hello mysql, So nobody here (even at MySQL AB) knows how to set a timezone for a MySQL client ? Not for whole server but per client? (or maybe for database???) I already posted this question here few days ago - and didn't get any reply. Please! Somebody help! -- Best regards, Maxim mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: client dependent timezone
Hello Gerald, But how people build web-applications then ? How can you use web-hosting in GMT+1 if web-application will be in use in GMT+5 for example? Have anybody an experience of resolving this problem (without embedding this functionality to backend app, because backend app is allready contains ~2 lines of code, and somewhere near to 1000 queries). If someone knows how to play this around - please help! Friday, March 29, 2002, 3:02:57 AM, you wrote: GRJ Per client? Per database? The only timezone setting in the MySQL manual is GRJ for the server (tz), and permits you to set it to your local timezone so it GRJ does not return values in GMT. GRJ If you want clients to have different timezones, perhaps you should write GRJ that capabilty into your front-end application. GRJ Gerald Jensen GRJ - Original Message - GRJ From: Maxim Vysotskiy [EMAIL PROTECTED] GRJ To: [EMAIL PROTECTED] GRJ Sent: Thursday, March 28, 2002 6:46 PM GRJ Subject: client dependent timezone GRJ Hello mysql, GRJ So nobody here (even at MySQL AB) knows how to set a timezone for a GRJ MySQL client ? Not for whole server but per client? (or maybe for GRJ database???) GRJ I already posted this question here few days ago - and didn't get any GRJ reply. GRJ Please! Somebody help! GRJ -- GRJ Best regards, GRJ Maxim mailto:[EMAIL PROTECTED] -- Best regards, Maximmailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: client dependent timezone
Hello Gerald, But how people build web-applications then ? How can you use web-hosting in GMT+1 if web-application will be in use in GMT+5 for example? Have anybody an experience of resolving this problem (without embedding this functionality to backend app, because backend app is allready contains ~2 lines of code, and somewhere near to 1000 queries). If someone knows how to play this around - please help! Friday, March 29, 2002, 3:02:57 AM, you wrote: GRJ Per client? Per database? The only timezone setting in the MySQL manual is GRJ for the server (tz), and permits you to set it to your local timezone so it GRJ does not return values in GMT. GRJ If you want clients to have different timezones, perhaps you should write GRJ that capabilty into your front-end application. GRJ Gerald Jensen GRJ - Original Message - GRJ From: Maxim Vysotskiy [EMAIL PROTECTED] GRJ To: [EMAIL PROTECTED] GRJ Sent: Thursday, March 28, 2002 6:46 PM GRJ Subject: client dependent timezone GRJ Hello mysql, GRJ So nobody here (even at MySQL AB) knows how to set a timezone for a GRJ MySQL client ? Not for whole server but per client? (or maybe for GRJ database???) GRJ I already posted this question here few days ago - and didn't get any GRJ reply. GRJ Please! Somebody help! GRJ -- GRJ Best regards, GRJ Maxim mailto:[EMAIL PROTECTED] -- Best regards, Maximmailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: client dependent timezone
Depends on the language you are writing in, and the level of your programming skills. If your users/guests have database entries, it would be a rather trivial task to set their timezone in a column in the database, then write your app to calculate their local time based on the offset from GMT. You would either have to rely on the user to use a form or image map of some sort to select a timezone (ala Red Hat's installer), or devise a way to ready the clock on their local machine. IMHO, this is a programming problem as opposed to a MySQL database issue. - Original Message - From: Maxim Vysotskiy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 28, 2002 7:14 PM Subject: Re[2]: client dependent timezone Hello Gerald, But how people build web-applications then ? How can you use web-hosting in GMT+1 if web-application will be in use in GMT+5 for example? Have anybody an experience of resolving this problem (without embedding this functionality to backend app, because backend app is allready contains ~2 lines of code, and somewhere near to 1000 queries). If someone knows how to play this around - please help! Friday, March 29, 2002, 3:02:57 AM, you wrote: GRJ Per client? Per database? The only timezone setting in the MySQL manual is GRJ for the server (tz), and permits you to set it to your local timezone so it GRJ does not return values in GMT. GRJ If you want clients to have different timezones, perhaps you should write GRJ that capabilty into your front-end application. GRJ Gerald Jensen GRJ - Original Message - GRJ From: Maxim Vysotskiy [EMAIL PROTECTED] GRJ To: [EMAIL PROTECTED] GRJ Sent: Thursday, March 28, 2002 6:46 PM GRJ Subject: client dependent timezone GRJ Hello mysql, GRJ So nobody here (even at MySQL AB) knows how to set a timezone for a GRJ MySQL client ? Not for whole server but per client? (or maybe for GRJ database???) GRJ I already posted this question here few days ago - and didn't get any GRJ reply. GRJ Please! Somebody help! GRJ -- GRJ Best regards, GRJ Maxim mailto:[EMAIL PROTECTED] -- Best regards, Maximmailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
unix_timesamp function out by a day when using a different timezone
Description: After setting the TZ environment variable to a different timezone, then using the UNIX_TIMESTAMP(...) function on a mysql timestamp string brings back the correct time, but a date that is a day out, but only for certain dates. It seems that the date becomes the original date plus the difference in the timezones. Only the date is affected - not the time. Ie if the new timezone is +5 hours difference, then it will add 5 hours to the timestamp, use that date, then use the original time of the timestamp. Since a timestamp is a timestamp, it should not be affected by the timezone when displaying it. How-To-Repeat: 1. create a timestamp in the database 2. using a select statement, apply the UNIX_TIMESTAMP function to the timestamp 3. change the TZ environment var to another timezone 4. using a select statement, apply the UNIX_TIMESTAMP function to the timestamp repeat these steps for each of the 24 hours in the day for the original timestamp, and you will see that some of them will have different dates. Fix: ??? Submitter-Id: submitter ID Originator:Dominic Gamble Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: unix_timestamp function out by a day when using a different timezone Severity: Priority: Category: mysql Class: Release: mysql-3.23.47 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.47, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.47-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 2 hours 27 min 11 sec Threads: 7 Questions: 1291 Slow queries: 0 Opens: 61 Flush tables: 1 Open tables: 14 Queries per second avg: 0.146 Environment: System: Linux kepler 2.4.14 #5 Thu Nov 22 11:29:10 EST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011006 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 20 18:08 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1170812 Feb 4 18:03 /lib/libc-2.2.5.so -rw-r--r--1 root root 2667532 Feb 4 18:03 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 4 18:03 /usr/lib/libc.so Configure command: ./configure --prefix=/usr --exec-prefix=/usr --libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info --mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler --with-berkeley-db --with-innodb --enable-static --enable-shared --with-raid --enable-thread-safe-client --without-readline --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL client timezone
Hello 'mysql, Does anybody know is this possible to set some particular timezone for the session? ie: server is in GMT, but application needs time selected in GMT+3 Is there any universal way ? Thanx for any hint in advance -- Best regards, Maxim mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Please Help: 2 (Easy?) Timezone (TZ) Questions
Hi folks - I am still having 2 related problems with the timezone setting for mysqld was hoping to get some help. Basically my squestions are as follows: a) the documentation says to set timezone by simply using --timezone=# when starting mysqld - but WHAT are the valid number values which time zones do the map to? b) I have tried to set the timezone using safe_mysqld --timezone=XX where I have tried various values for XX such as o1, 1, 13, 05,08 BUT near as I can tell ANY value I put in gives me GMT/UTC time (I think that's what it is) rather than giving me time offset by the given amount i entered. This is not what the documentation seems to suggest will happen - or am I being obtuse? Any help or suggestions would be appreciated. 'deep -- Amandeep Jawa Worker Bee Software -- deep AT worker DASH bee DOT com 225A Dolores St. San Francisco, CA 94103-2202 Home: 415 255 6257 (ALL MALP) professional: http://www.worker-bee.com personal: http://www.deeptrouble.com political: http://www.sflcv.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Please Help: 2 (Easy?) Timezone (TZ) Questions
Hi folks - I am still having 2 related problems with the timezone setting for mysqld was hoping to get some help. Basically my squestions are as follows: a) the documentation says to set timezone by simply using --timezone=# when starting mysqld - but WHAT are the valid number values which time zones do the map to? b) I have tried to set the timezone using safe_mysqld --timezone=XX where I have tried various values for XX such as o1, 1, 13, 05,08 BUT near as I can tell ANY value I put in gives me GMT/UTC time (I think that's what it is) rather than giving me time offset by the given amount i entered. This is not what the documentation seems to suggest will happen - or am I being obtuse? Any help or suggestions would be appreciated. 'deep -- Amandeep Jawa Worker Bee Software -- deep AT worker DASH bee DOT com 225A Dolores St. San Francisco, CA 94103-2202 Home: 415 255 6257 (ALL MALP) professional: http://www.worker-bee.com personal: http://www.deeptrouble.com political: http://www.sflcv.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
2 (Easy?) Timezone (TZ) Questions
Hi folks - I am having 2 related problems with the timezone setting for mysqld was hoping to get some help. Basically my squestions are as follows: a) the documentation says to set timezone by simply using --timezone=# when starting mysqld - but WHAT are the valid number values which time zones do the map to? b) I have tried to set the timezone using safe_mysqld --timezone=XX where I have tried various values for XX such as o1, 1, 13, 05,08 BUT near as I can tell ANY value I put in gives me GMT/UTC time (I think that's what it is) rather than giving me time offset by the given amount i entered. This is not what the documentation seems to suggest will happen - or am I being obtuse? Any help or suggestions would be appreciated. 'deep -- Amandeep Jawa Worker Bee Software -- deep AT worker DASH bee DOT com 225A Dolores St. San Francisco, CA 94103-2202 Home: 415 255 6257 (ALL MALP) professional: http://www.worker-bee.com personal: http://www.deeptrouble.com political: http://www.sflcv.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Timezone offset question
I have a data collection script which rolls raw data into a formatted table, based on year/month/day. I've been requested to roll up this raw data based on a different timezone - 3 hours behind - to better coincide with reports from a company in that timezone. This is the rollup SQL query we currently run: SELECT DATE_FORMAT(date,'%Y%m%d'),sitecode,reseller,section,type,COUNT(DISTINCT ip), COUNT(ip) FROM rawdata WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND (DAYOFMONTH(date) = DAYOFMONTH(NOW()) OR DAYOFMONTH(date) = DAYOFMONTH(DATE_SUB(NOW(),INTERVAL 1 DAY))) GROUP BY 1,2,3,4,5 ORDER BY 1,2,3,4,5 Basically it summerizes the totals for today and yesterday. My question is, given that query, what is the best method to rollup this offset data? use DATE_SUB on the SELECT, or perhaps on the DAYOFMONTH? Any suggestions would be appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone offset question
Start the server with the desired TZ set. Jeremy Wilson wrote: I have a data collection script which rolls raw data into a formatted table, based on year/month/day. I've been requested to roll up this raw data based on a different timezone - 3 hours behind - to better coincide with reports from a company in that timezone. This is the rollup SQL query we currently run: SELECT DATE_FORMAT(date,'%Y%m%d'),sitecode,reseller,section,type,COUNT(DISTINCT ip), COUNT(ip) FROM rawdata WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND (DAYOFMONTH(date) = DAYOFMONTH(NOW()) OR DAYOFMONTH(date) = DAYOFMONTH(DATE_SUB(NOW(),INTERVAL 1 DAY))) GROUP BY 1,2,3,4,5 ORDER BY 1,2,3,4,5 Basically it summerizes the totals for today and yesterday. My question is, given that query, what is the best method to rollup this offset data? use DATE_SUB on the SELECT, or perhaps on the DAYOFMONTH? Any suggestions would be appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone offset question
At 01:07 PM 11/13/01 -0600, Gerald Clark wrote: Start the server with the desired TZ set. That's all fine and good, but difficult switch back and forth every 10 minutes while that query runs for local data, then for the remote data. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone offset question
Jeremy Wilson wrote: At 01:07 PM 11/13/01 -0600, Gerald Clark wrote: Start the server with the desired TZ set. That's all fine and good, but difficult switch back and forth every 10 minutes while that query runs for local data, then for the remote data. The way I deal with it (and I don't know that it will help you) is that I store datetime as an integer from time() GMT. Then in the report I convert the time to the correct timezone based on the user's IP (internal network, different subnets for different TZ/locations). Of course this is a bad idea sometime in 2039 (?) or the end of Unix time as we know it. b. mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone offset question
Start the server with the desired TZ set. That's all fine and good, but difficult switch back and forth every 10 minutes while that query runs for local data, then for the remote data. =Agreed you must stick with either one or the other, right? =Wrong! To fix the problem of different offices spread out around the world, you either allow each office to report in local time, ie today's data, last month's data, etc; or you standardise all time-date oriented data into one common timebase/zone. =There are two 'standards': American = whichever time zone head office is; International = GMT/UTC/Zulu =PHP users are always happy at the latter because there are a neat set of built-in time functions that run at UTC [gm*()] regardless of the server/client computers' ToD clocks. =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone problem.
The small problem in mysql is it does not check if parameter is correct in configure script - this produced the problem... Peter mailto:[EMAIL PROTECTED] Yes, I don't know of any clear way of handling this with autoconf. Basically, if your option starts with --with-, then the script just sets a variable and moves on. If anyone knows a good way to validate --with- and --enable- options in an autoconf-generated configure script, let me know. Tim -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Tim Smith [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-time Developer /_/ /_/\_, /___/\___\_\___/ Boone, NC USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Timezone problem.
Peter Zaitsev writes: Hello Michael, Tuesday, August 07, 2001, 10:20:18 PM, you wrote: The question is which place do you do aply timezone ? I found the following interesting thing: Then mysql is started it uses correct timestamp, therefore INNODB is started with wrong timestamp... 010807 12:59:44 mysqld started 010807 8:59:45 InnoDB: Started The above means that timezone has changed between the two calls .. Between the two was tzset call. This is a typical case when a zone is changed from GMT to something else !!! And you are 4 hours before GMT, aren't you ?? If on Linux, check where does /etc/localtime point to. Simply, your startup scripts are not well setup. TZ should be set at the machine boot. -- Best regards, Petermailto:[EMAIL PROTECTED] -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Timezone problem.
Hello mysql, It seems like you made an incomportable changes in 3.23.40 without taking a time to write a release note :( The mysql 3.23.39 used the system timezone by default: maindb:/spylog/mysql/logs # date Tue Aug 7 13:31:56 MSD 2001 | transaction_isolation | READ-COMMITTED | | | | timezone| MSD | | | | tmp_table_size | 4194304 Therefore MYSQL 3.23.40 does not determinate the timezone correctly: | | transaction_isolation | READ-COMMITTED | | | | timezone| Local time zone must be set--see´ manual page | | | | tmp_table_size | 4194304 | | | It uses GMT in this case which could dammage the data (as went in my case) The other thing is the manual does not contains much info about setting timezone - only option to safe_mysqld which exports TZ variable, which does not work: root 19380 1 0 13:43 pts/800:00:00 sh /usr/local/mysql/bin/safe_mysqld --mysqld=mysqld --user=mysql --pid-file=/spylog/db/mysqld.pid --timezone=MSD --datadir=/spylog/db It set's the timezone according to required value but date still bad: | transaction_isolation | READ-COMMITTED | | | | timezone| MSD | | | | tmp_table_size | 4194304 mysql select now(); +-+ | now() | +-+ | 2001-08-07 09:44:44 | +-+ 1 row in set (0.00 sec) Therefore date returns correct value. rat:/spylog/layers # date Tue Aug 7 13:47:05 MSD 2001 rat:/spylog/layers # Do you have any ideas about this ? How I can fix the problem ? -- Best regards, Peter mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Timezone problem.
Hello Sinisa, Tuesday, August 07, 2001, 4:48:56 PM, you wrote: 1) SUSE. Destributions does not have TZ variable set by default. And the idea is it worked quite well wothout it with .39 2) The strange thing is why settng TZ makes mysql to print correct timezone in variables but still produces the wrong time ? SM Peter Zaitsev writes: Hello mysql, It seems like you made an incomportable changes in 3.23.40 without taking a time to write a release note :( The mysql 3.23.39 used the system timezone by default: maindb:/spylog/mysql/logs # date Tue Aug 7 13:31:56 MSD 2001 | transaction_isolation | READ-COMMITTED | | timezone| MSD | | tmp_table_size | 4194304 Therefore MYSQL 3.23.40 does not determinate the timezone correctly: | | transaction_isolation | READ-COMMITTED | | timezone| Local time zone must be set--see´ manual page | | tmp_table_size | 4194304 | It uses GMT in this case which could dammage the data (as went in my case) The other thing is the manual does not contains much info about setting timezone - only option to safe_mysqld which exports TZ variable, which does not work: root 19380 1 0 13:43 pts/800:00:00 sh /usr/local/mysql/bin/safe_mysqld --mysqld=mysqld --user=mysql --pid-file=/spylog/db/mysqld.pid --timezone=MSD --datadir=/spylog/db It set's the timezone according to required value but date still bad: | transaction_isolation | READ-COMMITTED | | timezone| MSD | | tmp_table_size | 4194304 mysql select now(); +-+ | now() | +-+ | 2001-08-07 09:44:44 | +-+ 1 row in set (0.00 sec) Therefore date returns correct value. rat:/spylog/layers # date Tue Aug 7 13:47:05 MSD 2001 rat:/spylog/layers # Do you have any ideas about this ? How I can fix the problem ? Peter mailto:[EMAIL PROTECTED] SM Hi Pjotr, SM I just tested the above, and it worked just fine for me with 4.0. SM But I have noticed something. If I mess up my shell, it will mess up SM all programs started from it, including MySQL. We can not do anything SM about it ... SM If a shell that starts up MySQL does not have TZ set properly then SM there is not much we can do about it ... -- Best regards, Petermailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Timezone problem.
Hi!
"Peter" == Peter Zaitsev [EMAIL PROTECTED] writes:
Peter Hello mysql,
Peter It seems like you made an incomportable changes in 3.23.40 without
Peter taking a time to write a release note :(
Peter The mysql 3.23.39 used the system timezone by default:
Peter maindb:/spylog/mysql/logs # date
Peter Tue Aug 7 13:31:56 MSD 2001
Peter | transaction_isolation | READ-COMMITTED
|
Peter | timezone| MSD
|
Peter | tmp_table_size | 4194304
Peter Therefore MYSQL 3.23.40 does not determinate the timezone correctly:
Peter |
Peter | transaction_isolation | READ-COMMITTED
|
Peter | timezone| Local time zone must be set--see´ manual
page
|
Peter | tmp_table_size | 4194304
|
Are you sure you are not starting mysqld differently ?
According to my knowing, we have not changed anything in timezone
usage in MySQL.
The name if the timezone mysqld displays is what we get from the
following call:
{
struct tm tm_tmp;
localtime_r(start_time,tm_tmp);
strmov(time_zone,tzname[tm_tmp.tm_isdst == 1 ? 1 : 0]);
}
I don't know why localtime_r() doesn't work in your case, but I would guess
something in your setup that has changed.
The only way to affect the timezone is to set the TZ variable before
starting mysqld/safe_mysqld or by starting safe_mysqld with the
--timezone= option.
Peter It uses GMT in this case which could dammage the data (as went in my
Peter case)
Peter The other thing is the manual does not contains much info about
Peter setting timezone - only option to safe_mysqld which exports TZ
Peter variable, which does not work:
Peter root 19380 1 0 13:43 pts/800:00:00 sh
/usr/local/mysql/bin/safe_mysqld --mysqld=mysqld --user=mysql
--pid-file=/spylog/db/mysqld.pid --timezone=MSD --datadir=/spylog/db
Peter It set's the timezone according to required value but date still bad:
Peter | transaction_isolation | READ-COMMITTED
|
Peter | timezone| MSD
|
Peter | tmp_table_size | 4194304
mysql select now();
Peter +-+
Peter | now() |
Peter +-+
Peter | 2001-08-07 09:44:44 |
Peter +-+
Peter 1 row in set (0.00 sec)
Peter Therefore date returns correct value.
Peter rat:/spylog/layers # date
Peter Tue Aug 7 13:47:05 MSD 2001
Peter rat:/spylog/layers #
Peter Do you have any ideas about this ? How I can fix the problem ?
Sorry, no ideas; It looks like there is some problem with your glibc
library.
Did you try the MySQL 3.23.39 and 3.23.40 binaries on the same
machine?
If not, then I think this is a glibc problem!
Regards,
Monty
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: Timezone problem.
Peter Zaitsev writes: Hello Sinisa, Tuesday, August 07, 2001, 4:48:56 PM, you wrote: 1) SUSE. Destributions does not have TZ variable set by default. And the idea is it worked quite well wothout it with .39 2) The strange thing is why settng TZ makes mysql to print correct timezone in variables but still produces the wrong time ? -- Best regards, Petermailto:[EMAIL PROTECTED] Hello Pjotr, We did not changed it recently. If you take a look at Changelog, you will see that nothing like that was introduced. mysqld.cc has the following code at the beginning : tzset(); and that is all. Regarding a strange thing when you set TZ that it changes time, this is /bin/sh doing by help of kernel, of course. Check it out. Change TZ and watch your time change. We truly can't do anything about it. We must use OS C API functions for such things. Anyway, all those functions we use are POSIX compliant. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
