Jump to content


Photo

problems with timestamp datatype


  • Please log in to reply
5 replies to this topic

#1 sambib

sambib
  • Members
  • PipPipPip
  • Advanced Member
  • 35 posts

Posted 02 November 2005 - 05:06 AM

I've set up a database table (mysql 4.1) as such:

create table fixtures (
id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
date TIMESTAMP,
heading TEXT,
details TEXT,
notes TEXT,
PRIMARY KEY(id)
);

now when I enter new data (a new set of fixtures) the date returned on the page is fine (the time that i entered the data)

code on page to return date:

<p><?php echo $fixtures['date']; ?></p> or I've used

<p><?php echo date(strtotime($fixtures['date'])); ?></p>

however when I go to edit or update that data the date gets messed up and a random date is returned on the page even if my update query doesn't include and reference to the date field. Also if I view the data using MySql Query Browser it shows up as a row of zeros.

any clues...?

#2 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 02 November 2005 - 06:50 AM

You need to change your date column type to DATETIME. Reason is MySQL automatically updates the first TIMESTAMP column it encounters in a table with the date and time of the last operation performed on the row. Check out the manual under TIMESTAMP for more.
Cook

#3 sambib

sambib
  • Members
  • PipPipPip
  • Advanced Member
  • 35 posts

Posted 02 November 2005 - 11:06 PM

You need to change your date column type to DATETIME. Reason is MySQL automatically updates the first TIMESTAMP column it encounters in a table with the date and time of the last operation performed on the row. Check out the manual under TIMESTAMP for more.

View Post


but that's what i want it to do.....?

ie: if i update a page i want to diplay the date when the changes occured.

i've tried using datetime as shown and i get similar results as before.

create table fixtures (
id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
date DATETIME,
heading TEXT,
details TEXT,
notes TEXT,
PRIMARY KEY(id)
);

any other clues as to why this occurs....?


#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 02 November 2005 - 11:53 PM

it's probably your version of mysql doesn't have the functionality to auto update TIMESTAMP. so just pass NOW() when you update.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 sambib

sambib
  • Members
  • PipPipPip
  • Advanced Member
  • 35 posts

Posted 03 November 2005 - 12:15 AM

it's probably your version of mysql doesn't have the functionality to auto update TIMESTAMP. so just pass NOW() when you update.

View Post


so how would the update query look(in php):

$query = "update fixtures set
heading = '$heading',
details = '$details',
notes = '$notes',
date = 'NOW()' I know it's not like this as I've tried it.....:)
where id = '$id'";

i'm using mysql 4.1.9

cheers


#6 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 03 November 2005 - 12:42 AM

Just drop the single quotes around NOW(). This is a MySQL function call, not a string. What you're instructing MySQL to do here is to store the string NOW() into your timestamp column, which does not work, because the string NOW() is not a value MySQL can interpret as a date/time value.
Cook




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users