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
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
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]
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]
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]
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]
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: 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
Re: Timezone problem.
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] Hi Pjotr, I just tested the above, and it worked just fine for me with 4.0. But I have noticed something. If I mess up my shell, it will mess up all programs started from it, including MySQL. We can not do anything about it ... If a shell that starts up MySQL does not have TZ set properly then there is not much we can do about it ... -- 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
Re: Timezone problem.
Hello Michael,
Tuesday, August 07, 2001, 10:20:18 PM, you wrote:
OK guys I think I've found the problem.
It seems like new innodb version (40b) does something bad with time.
I've tested 3 binaries
1) Plain .40 with normal GLIBC - OK
2) Plain .40 with patched GLIBC - OK
3) Plain .40 with new INNODB with doublewrite - This binary shiftes
the time.
Could you Heikke please check if you have something similar ?
MW 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
|
MW Are you sure you are not starting mysqld differently ?
MW According to my knowing, we have not changed anything in timezone
MW usage in MySQL.
MW The name if the timezone mysqld displays is what we get from the
MW following call:
MW {
MW struct tm tm_tmp;
MW localtime_r(start_time,tm_tmp);
MW strmov(time_zone,tzname[tm_tmp.tm_isdst == 1 ? 1 : 0]);
MW }
MW I don't know why localtime_r() doesn't work in your case, but I would guess
MW something in your setup that has changed.
MW The only way to affect the timezone is to set the TZ variable before
MW starting mysqld/safe_mysqld or by starting safe_mysqld with the
MW --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 ?
MW Sorry, no ideas; It looks like there is some problem with your glibc
MW library.
MW Did you try the MySQL 3.23.39 and 3.23.40 binaries on the same
MW machine?
MW If not, then I think this is a glibc problem!
MW Regards,
MW Monty
--
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
Re: Timezone problem.
Hi!
No idea what is wrong. Below is the code which
prints the timestamp in InnoDB:
.
struct tm cal_tm;
struct tm* cal_tm_ptr;
time_t tm;
time(tm);
#ifdef HAVE_LOCALTIME_R
localtime_r(tm, cal_tm);
cal_tm_ptr = cal_tm;
#else
cal_tm_ptr = localtime(tm);
#endif
fprintf(file,%02d%02d%02d %2d:%02d:%02d,
cal_tm_ptr-tm_year % 100,
cal_tm_ptr-tm_mon+1,
cal_tm_ptr-tm_mday,
cal_tm_ptr-tm_hour,
cal_tm_ptr-tm_min,
cal_tm_ptr-tm_sec);
...
I tested 3.23.40b on our Linux computer and got the following:
heikki@donna:~/mysql-3.23.40/sql mysqld
010807 18:01:01 Warning: setrlimit couldn't increase number of open files
to mo
re than 1024
010807 18:01:01 Warning: Changed limits: max_connections: 250 table_cache:
382
010807 18:01:02 InnoDB: Started
010807 18:01:02 mysqld: Table 'mysql.func' doesn't exist
010807 18:01:02 Can't open mysql/func table
mysqld: ready for connections
mysql select now();
+-+
| now() |
+-+
| 2001-08-07 18:03:24 |
+-+
1 row in set (0.00 sec)
mysql
..
Any ideas anybody?
Regards,
Heikki
-Original Message-
From: Peter Zaitsev [EMAIL PROTECTED]
To: Michael Widenius [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]; [EMAIL PROTECTED]
[EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED]
Date: Tuesday, August 07, 2001 5:41 PM
Subject: Re: Timezone problem.
Hello Michael,
Tuesday, August 07, 2001, 10:20:18 PM, you wrote:
OK guys I think I've found the problem.
It seems like new innodb version (40b) does something bad with time.
I've tested 3 binaries
1) Plain .40 with normal GLIBC - OK
2) Plain .40 with patched GLIBC - OK
3) Plain .40 with new INNODB with doublewrite - This binary shiftes
the time.
Could you Heikke please check if you have something similar ?
MW 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
|
MW Are you sure you are not starting mysqld differently ?
MW According to my knowing, we have not changed anything in timezone
MW usage in MySQL.
MW The name if the timezone mysqld displays is what we get from the
MW following call:
MW {
MW struct tm tm_tmp;
MW localtime_r(start_time,tm_tmp);
MW strmov(time_zone,tzname[tm_tmp.tm_isdst == 1 ? 1 : 0]);
MW }
MW I don't know why localtime_r() doesn't work in your case, but I would
guess
MW something in your setup that has changed.
MW The only way to affect the timezone is to set the TZ variable before
MW starting mysqld/safe_mysqld or by starting safe_mysqld with the
MW --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=/sp
ylog/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 ?
MW Sorry, no ideas; It looks like there is some problem with your glibc
MW library.
MW Did you try the MySQL 3.23.39 and 3.23.40 binaries on the same
MW machine?
MW If not, then I think this is a glibc problem!
MW Regards,
MW Monty
--
Best regards,
Petermailto:[EMAIL PROTECTED]
-
Before posting, please check:
http
Re: Timezone problem.
On Tuesday 07 August 2001 09:10, Heikki Tuuri wrote: Hi! No idea what is wrong. Below is the code which prints the timestamp in InnoDB: . struct tm cal_tm; struct tm* cal_tm_ptr; time_t tm; time(tm); #ifdef HAVE_LOCALTIME_R localtime_r(tm, cal_tm); cal_tm_ptr = cal_tm; #else cal_tm_ptr = localtime(tm); #endif fprintf(file,%02d%02d%02d %2d:%02d:%02d, cal_tm_ptr-tm_year % 100, cal_tm_ptr-tm_mon+1, cal_tm_ptr-tm_mday, cal_tm_ptr-tm_hour, cal_tm_ptr-tm_min, cal_tm_ptr-tm_sec); ... I tested 3.23.40b on our Linux computer and got the following: heikki@donna:~/mysql-3.23.40/sql mysqld 010807 18:01:01 Warning: setrlimit couldn't increase number of open files to mo re than 1024 010807 18:01:01 Warning: Changed limits: max_connections: 250 table_cache: 382 010807 18:01:02 InnoDB: Started 010807 18:01:02 mysqld: Table 'mysql.func' doesn't exist 010807 18:01:02 Can't open mysql/func table mysqld: ready for connections mysql select now(); +-+ | now() | +-+ | 2001-08-07 18:03:24 | +-+ 1 row in set (0.00 sec) mysql .. Any ideas anybody? Peter - can you strace the bad mysqld and the good one on startup and compare? Also compile the bad one with debugging, set a breakpoint in getenv() and backtrace every time it stops on both binaries. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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
