Re: Newbie question: Association table and Foreign Key

[S�ndor Hal�sz]

 2011/03/01 20:23 +0800, Wagyu Beef 
Part of the database consists of two tables "events" and "categories" which
look like this

+---+-+
 |  eventID   | eventName |
+---+-+
 |   1   | Event A  |
 |   2   | Event B  |
 |   3   | Event C  |
+---+-+
Primary Key: eventID

+---+-+
 |  categoryID  |   categoryName   |
+---+-+
 |   1   | Category A |
 |   2   | Category B |
 |   3   | Category C |
+---+-+

Primary Key: categoryID


The idea is that an event may have multiple categories and from what I've
read here (http://lists.mysql.com/mysql/171645), many-to-many relationships
in the database should be avoid.  According to the link and a couple of
others I found, I'm supposed to create a separate "events_categories" table
and make linkages using a Foreign Key.  Am not sure how to translate this to
a SQL query. 


Well, if your problem is really like that in the example that you quote, then 
look up 'REFERENCES' under 'CREATE TABLE'. That shows you what to put in the 
common table.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Convert unix time to 12:00 AM of that same day

[Joerg Bruehe]

Hi Bryan, all!


Bryan Cantwell wrote:
> That's closer:
> SELECT
>   UNIX_TIMESTAMP() + 86400 - (UNIX_TIMESTAMP() % 86400);
> Gives me 6:00 PM today...

The Unix timestamp is UTC-based (old name: GMT).
You don't write which timezone you are using, but your notation "6:00
PM" makes me assume you are US-based.

Is it 6:00 PM in your timezone at UTC midnight?


Joerg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Convert unix time to 12:00 AM of that same day

[Bryan Cantwell]

That's closer:
SELECT
  UNIX_TIMESTAMP() + 86400 - (UNIX_TIMESTAMP() % 86400);
Gives me 6:00 PM today...

On 03/01/2011 12:32 PM, Singer X.J. Wang wrote:

SELECT
 unix_timestamp() + 86400 - (unix_timestamp() % 86400);


--


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Convert unix time to 12:00 AM of that same day

[Bryan Cantwell]

SELECT
  unix_timestamp() + 86400 + (unix_timestamp() % 86400);
currently gives me 6:39 AM tomorrow

SELECT
  UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(1299003702)));
actually gives me what I want, but seems really stupid way of getting 
something that is probably very simple




On 03/01/2011 12:03 PM, Singer X.J. Wang wrote:

http://en.wikipedia.org/wiki/Midnight

On Tue, Mar 1, 2011 at 13:00, Singer X.J. Wang > wrote:


That's cause there's two midnights.. use
= 1298999201 + 86400 + (1298999201 % 86400)



On Tue, Mar 1, 2011 at 12:58, Bryan Cantwell
mailto:bcantw...@firescope.com>> wrote:

It was of course a typo, and even with the correct number
isn't the answer


On 03/01/2011 11:47 AM, Claudio Nanni wrote:


You can start by using 60*60*24=86400
;)

On Mar 1, 2011 6:17 PM, "Bryan Cantwell"
mailto:bcantw...@firescope.com>
>> wrote:
> I'd asked before how to convert a unix timestamp to the
hour that it is
> in (and got the perfect answer) :
> 1298999201 = 3/1/2011 11:06:41 AM
> (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM
>
> Now getting the timestamp converted to midnight of that
same day isn't
> as simple as:
> 1298999201 - (1298999201 % 85400)
> That just gives me a unix time from yesterday...
>
> How can I convert 1298999201 (3/1/2011 11:06:41 AM) to
1298959200
> (3/1/2011 12:00:00 AM)?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
>





--
The best compliment you could give Pythian for our service is a referral.

Re: Question about performance

[András Lukács]

Or you can interrupt the query instead, although I've seen it not to 
work on occasions: KILL QUERY id;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: versions 5.1, 5.5

[András Lukács]

From the command line there was no way to run "mysql.exe"


I may be dwelling on the obvious, but have you checked the "place MySQL 
system libraries in the system path" (or something similar) checkbox on 
install? v5.5.9 runs fine with me on Win7.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Convert unix time to 12:00 AM of that same day

[Johnny Withers]

You could use:

CONCAT(DATE_FORMAT(FROM_UNIXTIME(1298999201),'%Y-%m-%d'),' 12:00:00')

JW

On Tue, Mar 1, 2011 at 11:58 AM, Bryan Cantwell wrote:

> It was of course a typo, and even with the correct number isn't the answer
>
>
> On 03/01/2011 11:47 AM, Claudio Nanni wrote:
>
>>
>> You can start by using 60*60*24=86400
>> ;)
>>
>> On Mar 1, 2011 6:17 PM, "Bryan Cantwell" > bcantw...@firescope.com>> wrote:
>> > I'd asked before how to convert a unix timestamp to the hour that it is
>> > in (and got the perfect answer) :
>> > 1298999201 = 3/1/2011 11:06:41 AM
>> > (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM
>> >
>> > Now getting the timestamp converted to midnight of that same day isn't
>> > as simple as:
>> > 1298999201 - (1298999201 % 85400)
>> > That just gives me a unix time from yesterday...
>> >
>> > How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200
>> > (3/1/2011 12:00:00 AM)?
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
>> >
>>
>
>
>


-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: Convert unix time to 12:00 AM of that same day

[Bryan Cantwell]

It was of course a typo, and even with the correct number isn't the answer

On 03/01/2011 11:47 AM, Claudio Nanni wrote:


You can start by using 60*60*24=86400
;)

On Mar 1, 2011 6:17 PM, "Bryan Cantwell" > wrote:

> I'd asked before how to convert a unix timestamp to the hour that it is
> in (and got the perfect answer) :
> 1298999201 = 3/1/2011 11:06:41 AM
> (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM
>
> Now getting the timestamp converted to midnight of that same day isn't
> as simple as:
> 1298999201 - (1298999201 % 85400)
> That just gives me a unix time from yesterday...
>
> How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200
> (3/1/2011 12:00:00 AM)?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com

>

Re: Convert unix time to 12:00 AM of that same day

[Claudio Nanni]

You can start by using 60*60*24=86400
;)
On Mar 1, 2011 6:17 PM, "Bryan Cantwell"  wrote:
> I'd asked before how to convert a unix timestamp to the hour that it is
> in (and got the perfect answer) :
> 1298999201 = 3/1/2011 11:06:41 AM
> (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM
>
> Now getting the timestamp converted to midnight of that same day isn't
> as simple as:
> 1298999201 - (1298999201 % 85400)
> That just gives me a unix time from yesterday...
>
> How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200
> (3/1/2011 12:00:00 AM)?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
>

Convert unix time to 12:00 AM of that same day

[Bryan Cantwell]

I'd asked before how to convert a unix timestamp to the hour that it is 
in (and got the perfect answer) :

1298999201 = 3/1/2011 11:06:41 AM
(1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM

Now getting the timestamp converted to midnight of that same day isn't 
as simple as:

1298999201 - (1298999201 % 85400)
That just gives me a unix time from yesterday...

How can I convert  1298999201 (3/1/2011 11:06:41 AM)  to 1298959200 
(3/1/2011 12:00:00 AM)?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Newbie question: Association table and Foreign Key

[Wagyu Beef]

Oh yeah, forgot to mention that.  Yes, one event will have multiple
categories.  And one category can be applicable to multiple events.

On Tue, Mar 1, 2011 at 8:33 PM, Dhaval Jaiswal <
jaiswal.dha...@enzenglobal.com> wrote:

>
> Just curious as it is not mentioned.  Can Category ID also have multiple
> event id ?
>
>
> --
> Cheers
> Dhaval Jaiswal
>
>
> On 01/03/2011 5:53 PM, Wagyu Beef wrote:
>
>> Hey guys,
>>
>> Am a newbie here and need a little help.
>>
>> Part of the database consists of two tables "events" and "categories"
>> which
>> look like this
>>
>> +---+-+
>>  |  eventID   | eventName |
>> +---+-+
>>  |   1   | Event A  |
>>  |   2   | Event B  |
>>  |   3   | Event C  |
>> +---+-+
>> Primary Key: eventID
>>
>> +---+-+
>>  |  categoryID  |   categoryName   |
>> +---+-+
>>  |   1   | Category A |
>>  |   2   | Category B |
>>  |   3   | Category C |
>> +---+-+
>>
>> Primary Key: categoryID
>>
>>
>> The idea is that an event may have multiple categories and from what I've
>> read here (http://lists.mysql.com/mysql/171645), many-to-many
>> relationships
>> in the database should be avoid.  According to the link and a couple of
>> others I found, I'm supposed to create a separate "events_categories"
>> table
>> and make linkages using a Foreign Key.  Am not sure how to translate this
>> to
>> a SQL query.  Can I get some help.
>>
>> Thanks a million!
>>
>> Regards,
>> Suren
>>
>>
>>
> This e-mail, and any attachments
> are strictly confidential and may also contain legally privileged
> information. It is intended for the addressee(s) only. If you are not the
> intended recipient, please do not print, copy, store or act in reliance on
> the e-mail or any of its attachments. Instead, please notify the sender
> immediately and then delete the e-mail and any attachments.
>
> Unless expressly stated to the contrary, the views expressed in this e-mail
> are not necessarily the views of Enzen Global Solutions (P) Limited or any
> of its subsidiaries or affiliates (Group Companies), and the Group
> Companies, their directors, officers and employees makes no representation
> and accept no liability for the accuracy or completeness of this e-mail. You
> are responsible for maintaining your own virus protection and the Group
> Companies do not accept any liability for viruses. Enzen reserves the right
> to monitor and review the content of all messages sent to or from this
> e-mail address. Messages sent to or from this e-mail address may be stored
> on the Enzen e-mail system.
>

Re: Newbie question: Association table and Foreign Key

[Dhaval Jaiswal]

Just curious as it is not mentioned.  Can Category ID also have multiple 
event id ?



--
Cheers
Dhaval Jaiswal

On 01/03/2011 5:53 PM, Wagyu Beef wrote:

Hey guys,

Am a newbie here and need a little help.

Part of the database consists of two tables "events" and "categories" which
look like this

+---+-+
  |  eventID   | eventName |
+---+-+
  |   1   | Event A  |
  |   2   | Event B  |
  |   3   | Event C  |
+---+-+
Primary Key: eventID

+---+-+
  |  categoryID  |   categoryName   |
+---+-+
  |   1   | Category A |
  |   2   | Category B |
  |   3   | Category C |
+---+-+

Primary Key: categoryID


The idea is that an event may have multiple categories and from what I've
read here (http://lists.mysql.com/mysql/171645), many-to-many relationships
in the database should be avoid.  According to the link and a couple of
others I found, I'm supposed to create a separate "events_categories" table
and make linkages using a Foreign Key.  Am not sure how to translate this to
a SQL query.  Can I get some help.

Thanks a million!

Regards,
Suren

   

This e-mail, and any attachments are 
strictly confidential and may also contain legally privileged information. It is 
intended for the addressee(s) only. If you are not the intended recipient, please do 
not print, copy, store or act in reliance on the e-mail or any of its attachments. 
Instead, please notify the sender immediately and then delete the e-mail and any 
attachments.

Unless expressly stated to the contrary, the views expressed in this e-mail are not 
necessarily the views of Enzen Global Solutions (P) Limited or any of its 
subsidiaries or affiliates (Group Companies), and the Group Companies, their 
directors, officers and employees makes no representation and accept no liability for 
the accuracy or completeness of this e-mail. You are responsible for maintaining your 
own virus protection and the Group Companies do not accept any liability for viruses. 
Enzen reserves the right to monitor and review the content of all messages sent to or 
from this e-mail address. Messages sent to or from this e-mail address may be stored 
on the Enzen e-mail system.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Newbie question: Association table and Foreign Key

[Wagyu Beef]

Hey guys,

Am a newbie here and need a little help.

Part of the database consists of two tables "events" and "categories" which
look like this

+---+-+
 |  eventID   | eventName |
+---+-+
 |   1   | Event A  |
 |   2   | Event B  |
 |   3   | Event C  |
+---+-+
Primary Key: eventID

+---+-+
 |  categoryID  |   categoryName   |
+---+-+
 |   1   | Category A |
 |   2   | Category B |
 |   3   | Category C |
+---+-+

Primary Key: categoryID


The idea is that an event may have multiple categories and from what I've
read here (http://lists.mysql.com/mysql/171645), many-to-many relationships
in the database should be avoid.  According to the link and a couple of
others I found, I'm supposed to create a separate "events_categories" table
and make linkages using a Foreign Key.  Am not sure how to translate this to
a SQL query.  Can I get some help.

Thanks a million!

Regards,
Suren

Re: Tool for shifting tables from Mysql to Postgresql

[Dhaval Jaiswal]

By default Postgresql database encoding in UTF8.

It seems to me by seeing error that database encoding in mysql is 
different from it.



--
Cheers,
Dhaval Jaiswal




On 01/03/2011 3:57 PM, Adarsh Sharma wrote:

Dear all,

I want to convert some tables from Mysql database to Postgresql 
Database in Linux Systems ( Ubuntu-10.4, CentOS ).


Can someone Please tell me tool for it that makes it easier.

I am able to done it through FW tools in Windows System but i want to 
achieve it in Linux ( CentOS ) System.


I researched a lot & tried below steps :

1. mysqldump --compatible=postgresql wiki20100130 > 
/hdd4-1/wiki20100130_mysql108feb22.sql


2. sed "s/\\\'/\'\'/g" wiki20100130_mysql108feb22.sql

3. bin/psql -Upostgres wiki20100130 < 
/hdd4-1/wiki20100130_mysql108feb22.sql


invalid byte sequence for encoding "UTF8": 0xe3ba27
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

ERROR:  invalid byte sequence for encoding "UTF8": 0xee6c65
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

ERROR:  invalid byte sequence


I think a tool would ease that work.

Thanks & best Regards,

Adarsh Sharma


This e-mail, and any attachments are 
strictly confidential and may also contain legally privileged information. It is 
intended for the addressee(s) only. If you are not the intended recipient, please do 
not print, copy, store or act in reliance on the e-mail or any of its attachments. 
Instead, please notify the sender immediately and then delete the e-mail and any 
attachments.

Unless expressly stated to the contrary, the views expressed in this e-mail are not 
necessarily the views of Enzen Global Solutions (P) Limited or any of its 
subsidiaries or affiliates (Group Companies), and the Group Companies, their 
directors, officers and employees makes no representation and accept no liability for 
the accuracy or completeness of this e-mail. You are responsible for maintaining your 
own virus protection and the Group Companies do not accept any liability for viruses. 
Enzen reserves the right to monitor and review the content of all messages sent to or 
from this e-mail address. Messages sent to or from this e-mail address may be stored 
on the Enzen e-mail system.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Tool for shifting tables from Mysql to Postgresql

[Jaime Crespo Rincón]

2011/3/1 Adarsh Sharma :
> Dear all,
>
> I want to convert some tables from Mysql database to Postgresql Database in
> Linux Systems ( Ubuntu-10.4, CentOS ).
[...]
> invalid byte sequence for encoding "UTF8": 0xe3ba27
> HINT:  This error can also happen if the byte sequence does not match the
> encoding expected by the server, which is controlled by "client_encoding".
> ERROR:  invalid byte sequence for encoding "UTF8": 0xee6c65
> HINT:  This error can also happen if the byte sequence does not match the
> encoding expected by the server, which is controlled by "client_encoding".
> ERROR:  invalid byte sequence

This is not a MySQL-specific error. You are using a character set
encoding as if it were another. Please, check documentation for the
options to export and import database dumps such as:



-- 
Jaime Crespo
MySQL & Java Instructor
Software Developer
Warp Networks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Tool for shifting tables from Mysql to Postgresql

[Adarsh Sharma]

Dear all,

I want to convert some tables from Mysql database to Postgresql Database 
in Linux Systems ( Ubuntu-10.4, CentOS ).


Can someone Please tell me tool for it that makes it easier.

I am able to done it through FW tools in Windows System but i want to 
achieve it in Linux ( CentOS ) System.


I researched a lot & tried below steps :

1. mysqldump --compatible=postgresql wiki20100130 > 
/hdd4-1/wiki20100130_mysql108feb22.sql


2. sed "s/\\\'/\'\'/g" wiki20100130_mysql108feb22.sql

3. bin/psql -Upostgres wiki20100130 < /hdd4-1/wiki20100130_mysql108feb22.sql

invalid byte sequence for encoding "UTF8": 0xe3ba27
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

ERROR:  invalid byte sequence for encoding "UTF8": 0xee6c65
HINT:  This error can also happen if the byte sequence does not match 
the encoding expected by the server, which is controlled by 
"client_encoding".

ERROR:  invalid byte sequence


I think a tool would ease that work.

Thanks & best Regards,

Adarsh Sharma

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org