I need to select a timestamp and insert a date

views:

answers:

I need to select a timestamp and insert a date

Hi there,

I have a timestamp in a database table. Now everytime a record is amended the timestamp changes - this isn't really what I want as the record represents a sale so everytime the data is amended it looks like the sale time has changed! Thus I have added a new field to the database table called 'sale_date' and I want to select the timestamp field of that record, called 'sale_time' and convert the timestamp to the format dd/mm/yyyy hh:mm:ss. and insert it into the new 'sale_date' field (which is text not date)

Any ideas? I'm rubbish at mysql.

If I haven't explained myself well please say.

+1 A:

The definition of your timestamp column (one that changes on updates) constains ON UPDATE CURRENT_TIMESTAMP clause. Remove it (ALTER TABLE) and it will stop updating.

And please, pleasee, please, do not ever store dates as text.

Mchl 2010-08-23 18:52:36

If I could stop the timestamp udating that would solve my problem and save me a lot of work!

Mike Sav 2010-08-23 18:58:09

Any ideas on how I'd do this in PHPadmin - if my table was called "sales?

Mike Sav 2010-08-23 19:17:19

Select the database and the table you want to edit. Click on the 'structure' tab. Click on the 'pencil' (edit action) nezt to the timestamp field's name. Look for 'attributes' combobox (should be after 'collation' combobox). Make sure ON UPDATE CURRENT_TIMESTAMP is not selected in it and save the changes.Alernatively run this SQL.`ALTER TABLE sales CHANGE yourTimeStampField TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP`

Mchl 2010-08-23 19:32:25

The combobox has no selected value and those available are:binaryunsignedunsigned zerofillThe SQL comes back with an error: [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL DEFAULT CURRENT_TIMESTAMP' at line 1Thanks anyway, I'll keep trawling the web looking for an answer

Mike Sav 2010-08-23 19:46:16

What is your MySQL version? Paste the result of `SHOW CREATE TABLE sales` query

Mchl 2010-08-23 19:55:27

the mysql version will be MySQL4.0CREATE TABLE `sales` ( `Sale_ID` int(11) NOT NULL auto_increment, `Sale_Ref` text, `hsbcReturn` varchar(255) default NULL, `User_ID` int(11) default NULL, `Sale_Time` timestamp(14) NOT NULL, `Cost` decimal(10,2) default NULL, `Total_Booking_Fee` decimal(10,2) default NULL, `Postage_Type` int(11) default NULL, `Total_Cost` decimal(10,2) default NULL, `Refund_Amount` decimal(10,2) NOT NULL default '0.00', `Sale_Status` int(11) default NULL, `Address_Record` text, PRIMARY KEY (`Sale_ID`)) TYPE=InnoDB

Mike Sav 2010-08-23 20:00:06

Oops... 4.0 That's pretty old and not supported anymore. Also you can't change this timestamp behaviour. Any chances of upgrading to 5.1 for you?

Mchl 2010-08-23 20:18:27

that's what I'll do now... watch this space, I'll make a blank mysql5 database and restore the data from a backup script of the database

Mike Sav 2010-08-23 20:21:47

I think you'll need to set the MySQL 4 compatibility mode when importing this into MySQL 5.

Mchl 2010-08-23 20:45:54

ansaurus

tags:

views:

answers:

I need to select a timestamp and insert a date

related questions