timestamp problem

[cram81]

hi

i have a table which is like this:

event
(event_id, user_id, tsStart, tsFinish)
where tsStart & tsFinish are timestamps...

now when i add an entry i only add data to the first 3 columns leaving the tsFinish blank (null)...

ie: INSERT INTO event values('1','1',now,null)

now when i try to update the tsFinish column instead of just that column updating, both columns which contain a timestamp (tsStart, tsFinish) get updated to the new
now()???

does anyone know why this could be happening... im using the following query to update..
UPDATE event SET tsFinish = now()
WHERE event_id = '6';

and am using windows xp, and mysql v 4.0.16???

many thanks

mark

 

[sleipnir214]

The this page of the MySQL online manual states, "If you have multiple TIMESTAMP columns, only the first one is updated automatically."

I suspect that what that sentence actually means is "If you have multiple TIMESTAMP columns, MySQL will only update the first column that is not being explicitly set to some value."

What's happening is that you're inserting and updating a table with two timestamp columns, but you only specify an explicit value for one each time. So MySQL automagically updates the other one for you.

Timestamp columns really aren't useful for what you're doing. I think the rationale behind the type is that you'll have one timestamp column in a table to record when a record was last changed.

I recommend that instead of using two columns of type timestamp, you use two columns of type datetime. The two columns store the same values, but datetime columns are not automatically updated. You'll just have to set explicit values for the column whenever you insert or update your records. But you were doing that anyway.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[cram81]

thanks for ur reply ...
im doing this in java so ill be able to use java to generate the date and time and then use the date:time like u said...


mark

 

[sleipnir214]

Why generate the datetime strings in Java? You can reference MySQL's builtin functions from within queries.

The insert query:

INSERT INTO
event (event_id, user_id, tsStart)
values('1','1',now())

and the update query:

update even set tsFinish = now() where event_id = 1

will both use MySQL's builtin function now(). Java can just treat the function reference as a string.

Want the best answers? Ask the best questions!

TANSTAAFL!!