Timestamp issue

Mysql Version::

Server version 3.23.47-nt
Protocol version 10
Connection . via named pipe
UNIX socket MySQL

OS::
Windows NT 4.0 Workstation


Hello
Following is the description of the problem being faced.

Problem:
The first timestamp column in a table is set to current date-time value =
as soon as we update one or more columns in the table.

Example:

The guest information is stored in a table named GUEST. The schema for =
it is as below.

create table guest (guestID int, arrivalDate timestamp, departureDate =
timestamp, status char(1));

Above table is populated using following queries.

insert into guest values (1, '2002-09-30', '2002-10-14', 'Y');
insert into guest values (2, '2002-09-20', '2002-10-01', 'Y');
insert into guest values (3, '2002-09-15', '2002-09-25', 'Y');
insert into guest values (4, '2002-09-12', '2002-09-20', 'Y');


If we use following query to update the status from Y to N, then =
arrivalDate column is set to CURRENT(system) date-time value.

update guest set status =3D 'N';

Does anybody have any information on this issue?

Thanx in advance.

Prafulla

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Timestamp issue

Prafulla,

please check documentation http://www.mysql.com/doc/en/DATETIME.html
"...
The TIMESTAMP column type provides a type that you can use to automatically
mark INSERT or UPDATE operations with the current date and time. If you have
multiple TIMESTAMP columns, only the first one is updated automatically.
...."
You need to change type of fields from timestamp to datetime.

Best regards,
Mikhail.


----- Original Message -----
From: "Prafulla Girgaonkar"
To:
Cc:
Sent: Tuesday, September 17, 2002 1:19 PM
Subject: Timestamp issue



Mysql Version::

Server version 3.23.47-nt
Protocol version 10
Connection . via named pipe
UNIX socket MySQL

OS::
Windows NT 4.0 Workstation


Hello
Following is the description of the problem being faced.

Problem:
The first timestamp column in a table is set to current date-time value as
soon as we update one or more columns in the table.

Example:

The guest information is stored in a table named GUEST. The schema for it is
as below.

create table guest (guestID int, arrivalDate timestamp, departureDate
timestamp, status char(1));

Above table is populated using following queries.

insert into guest values (1, '2002-09-30', '2002-10-14', 'Y');
insert into guest values (2, '2002-09-20', '2002-10-01', 'Y');
insert into guest values (3, '2002-09-15', '2002-09-25', 'Y');
insert into guest values (4, '2002-09-12', '2002-09-20', 'Y');


If we use following query to update the status from Y to N, then arrivalDate
column is set to CURRENT(system) date-time value.

update guest set status = 'N';

Does anybody have any information on this issue?

Thanx in advance.

Prafulla

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Timestamp issue

At 16:49 +0530 9/17/02, Prafulla Girgaonkar wrote:
>Mysql Version::
>
>Server version 3.23.47-nt
>Protocol version 10
>Connection . via named pipe
>UNIX socket MySQL
>
>OS::
>Windows NT 4.0 Workstation
>
>
>Hello
>Following is the description of the problem being faced.
>
>Problem:
>The first timestamp column in a table is set to current date-time
>value as soon as we update one or more columns in the table.

As documented in the MySQL Reference Manual.

>Example:
>
>The guest information is stored in a table named GUEST. The schema
>for it is as below.
>
>create table guest (guestID int, arrivalDate timestamp,
>departureDate timestamp, status char(1));
>
>Above table is populated using following queries.
>
>insert into guest values (1, '2002-09-30', '2002-10-14', 'Y');
>insert into guest values (2, '2002-09-20', '2002-10-01', 'Y');
>insert into guest values (3, '2002-09-15', '2002-09-25', 'Y');
>insert into guest values (4, '2002-09-12', '2002-09-20', 'Y');
>
>
>If we use following query to update the status from Y to N, then
>arrivalDate column is set to CURRENT(system) date-time value.
>
>update guest set status = 'N';
>
>Does anybody have any information on this issue?

The manual does. It also describes the DATETIME type, which may be
preferable for what you're trying to accomplish.

The manual is your friend. It's a good idea to take advantage of it.

>
>Thanx in advance.
>
>Prafulla


------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Timestamp issue

Hi!

>>>>> "Prafulla" == Prafulla Girgaonkar writes:

Prafulla> Mysql Version::

Prafulla> Server version 3.23.47-nt
Prafulla> Protocol version 10
Prafulla> Connection . via named pipe
Prafulla> UNIX socket MySQL



Prafulla> create table guest (guestID int, arrivalDate timestamp, departureDate timestamp, status char(1));

Prafulla> Above table is populated using following queries.

Prafulla> insert into guest values (1, '2002-09-30', '2002-10-14', 'Y');
Prafulla> insert into guest values (2, '2002-09-20', '2002-10-01', 'Y');
Prafulla> insert into guest values (3, '2002-09-15', '2002-09-25', 'Y');
Prafulla> insert into guest values (4, '2002-09-12', '2002-09-20', 'Y');

Prafulla> If we use following query to update the status from Y to N, then arrivalDate column is set to CURRENT(system) date-time value.

Prafulla> update guest set status = 'N';

Prafulla> Does anybody have any information on this issue?

The reason for that is that you are using a TIMESTAMP column, which by
definition is automaticly updated for each insert/update.

Fix (one of):

- Change the timestamp type to datetime.
- Change the update to update the timestamp colum with it's old value
update guests set status='n' and timestamp=timestamp

We will add a new section about this problem to the
'Query Related Issues' section in the manual.

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 bugs-thread12595@lists.mysql.com
To unsubscribe, e-mail