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
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
timediff hours and days
Hello! Have this anwer from mysql, ++ | TIMEDIFF(Date,now()) | -++ | 70:56:06 | ++ Is it possible to convert it to - days, hour, minutes? 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
operation with dates
Hello! Im trying to subtract two dates in my consult, but I dont get it, I did: mysql select userID from user where (userPaymentDate - now()) 365 ; It didnt work. Do you know how to do it? Thank you so much! Regards 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: operation with dates
I found it, mysql select userID from user where datediff(now(), userPaymentDate) 365; Thanks Rocío Gómez Escribano r.go...@ingenia-soluciones.com Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com -Mensaje original- De: Andrew Moore [mailto:eroomy...@gmail.com] Enviado el: jueves, 12 de mayo de 2011 17:11 Para: Rocio Gomez Escribano CC: mysql@lists.mysql.com Asunto: Re: operation with dates Rocio, there are specific date functions that you need to learn to allow you to complete this kind of query. Please check out the MySQL documentation for this. HTH Andy On Thu, May 12, 2011 at 4:05 PM, Rocio Gomez Escribano r.go...@ingenia-soluciones.com wrote: Hello! Im trying to subtract two dates in my consult, but I dont get it, I did: mysql select userID from user where (userPaymentDate - now()) 365 ; It didnt work. Do you know how to do it? Thank you so much! Regards *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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: RV: independent tables
Tables client an user are quite similar, but they don't have any intersection, I mean, if somebody is a client, he or she cant be a user. So, I have his or her driving license and I need to know what kind of person is. Im trying some join left, right, but I'm unable to get it!! Rocío Gómez Escribano r.go...@ingenia-soluciones.com Polígono Campollano C/F, nº21T 02007 Albacete (España) Tlf:967-504-513 Fax: 967-504-513 www.ingenia-soluciones.com -Mensaje original- De: Halász Sándor [mailto:h...@tbbs.net] Enviado el: miércoles, 04 de mayo de 2011 22:43 Para: Rocio Gomez Escribano CC: mysql@lists.mysql.com Asunto: Re: RV: independent tables 2011/05/04 16:57 +0200, Rocio Gomez Escribano I suppose my solution is an Join, but they have no intersection Really? Your examples are very much like a simple join, a special case of ... client OUTER JOIN user ON clientCodeDrivingLicense = userCodeDrivingLicense What is wrong with that? (although actually MySQL does not do full outer joining. It is needful to get that through a union of left join and right join, care taken that the inner join in only one of them appear.) Actually, your tables client and user look like the same table with field names changed, no other difference. Field names have nothing to do with intersection. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RV: independent tables
The databas estructure: mysql describe user; ++-+--+-+-+- ---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+- ---+ | userID | int(11) | NO | PRI | NULL| auto_increment | | userName | varchar(20) | YES | | NULL| | | userCodeDrivingLicense | varchar(20) | YES | | NULL| | ++-+--+-+-+- ---+ 3 rows in set (0.00 sec) mysql describe client; +--+-+--+-+-+--- -+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+--- -+ | clientID | int(11) | NO | PRI | NULL| auto_increment | | clientName | varchar(20) | YES | | NULL| | | clientCodeDrivingLicense | varchar(20) | YES | | NULL| | +--+-+--+-+-+--- -+ 3 rows in set (0.00 sec) 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: Rocio Gomez Escribano [mailto:r.go...@ingenia-soluciones.com] Enviado el: martes, 03 de mayo de 2011 8:09 Para: mysql@lists.mysql.com Asunto: independent tables Everyone has his/her own driving license, and I need to know what kind of person (client or user) is. mysql select userID, clientID from client, user where (clientCodeDrivingLicense= 321321321 || userCodeDrivingLicense = 321321321); ++---+ | userID | clientID | ++---+ | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 2 | | 5 | 2 | ++---+ 5 rows in set (0.00 sec) But, what I want is something like that: ++---+ | userID | clientID | ++---+ | Null | 2 | ++---+ I tried something like this: select COUNT(DISTINCT u.userID), userID, clientID from client, user where (clientCodeDrivingLicense = 321321321 || userCodeDrivingLicense = 321321321); +--++---+ | COUNT(DISTINCT u.userID) | userID | clientID | +--++---+ |5 | 1 | 2 | +--++---+ 1 row in set (0.00 sec) But it wont be efficient enough in the future. I suppose my solution is an Join, but they have no intersection, so, I cant imagine how do it Thank you!! Regards 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
independent tables
Everyone has his/her own driving license, and I need to know what kind of person (client or user) is. mysql select userID, clientID from client, user where (clientCodeDrivingLicense= 321321321 || userCodeDrivingLicense = 321321321); ++---+ | userID | clientID | ++---+ | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 2 | | 5 | 2 | ++---+ 5 rows in set (0.00 sec) But, what I want is something like that: ++---+ | userID | clientID | ++---+ | Null | 2 | ++---+ I tried something like this: select COUNT(DISTINCT u.userID), userID, clientID from client, user where (clientCodeDrivingLicense = 321321321 || userCodeDrivingLicense = 321321321); +--++---+ | COUNT(DISTINCT u.userID) | userID | clientID | +--++---+ |5 | 1 | 2 | +--++---+ 1 row in set (0.00 sec) But it wont be efficient enough in the future. I suppose my solution is an Join, but they have no intersection, so, I cant imagine how do it Thank you!! Regards 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
left join two tables
Hi!! Is it possible to create a left join consult with 2 tables?? I mean: SELECT * FROM table1 LEFT JOIN (table2, table3) on table1.ID = table2.subID and table1.ID= table3.subID 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
empty set
Hi! I'm not sure it would be possible what I want to. I have a huge query, most of them are independent, and they could have not answer. What I want is when this happen, I want to change the empty set by void or 0 or something like that, here it is an example: mysql select * from usuarios; +---++ | usuarioID | codigo | +---++ | 1 |456 | | 2 |789 | | 3 |123 | +---++ 3 rows in set (0.00 sec) mysql SELECT usuarioID FROM usuarios WHERE codigo = 45; Empty set (0.00 sec) +---+ | usuarioID | +---+ | Void | +---+ Is it possible??? Thank you!!
RE: empty set
Thank you so much!! It works!! mysql SELECT COUNT(*) usuarios,codigo FROM usuarios WHERE codigo = 45; +--++ | usuarios | codigo | +--++ |0 | NULL | +--++ 1 row in set (0.00 sec) -Mensaje original- De: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Enviado el: martes, 26 de abril de 2011 15:02 Para: mysql@lists.mysql.com Asunto: Re: empty set As far as I know, it isn´t possible. The right way is to check how many rows your select has returned. -- João Cândido de Souza Neto Rocio Gomez Escribano r.go...@ingenia-soluciones.com escreveu na mensagem news:000601cc03e2$fd3c9540$f7b5bfc0$@go...@ingenia-soluciones.com... Hi! I'm not sure it would be possible what I want to. I have a huge query, most of them are independent, and they could have not answer. What I want is when this happen, I want to change the empty set by void or 0 or something like that, here it is an example: mysql select * from usuarios; +---++ | usuarioID | codigo | +---++ | 1 |456 | | 2 |789 | | 3 |123 | +---++ 3 rows in set (0.00 sec) mysql SELECT usuarioID FROM usuarios WHERE codigo = 45; Empty set (0.00 sec) +---+ | usuarioID | +---+ | Void | +---+ Is it possible??? Thank you!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=r.go...@ingenia-soluciones.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org