MYSQL: convert timestamp & # 8594; time

Question

I have this MYSQL table:

[ID] [TIMESTAMP] [TIME] [1] [2010-05-29 01:17:35] [] [1] [2010-05-29 01:23:42] [] 

... etc.

Now I need to copy the TIMESTAMP value to TIME strings. New TIME lines are created using the PHP time () command;

Problem: I have no idea how to do this. Maybe iwht is a MySQL command, maybe through PHP? Please help me!

My table:

 CREATE TABLE `comments` ( `id` int(10) unsigned NOT NULL auto_increment, `ip` int(10) unsigned NOT NULL, `name` varchar(40) NOT NULL, `comment` text NOT NULL, `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `email` varchar(30) NOT NULL, `time` int(100) NOT NULL, PRIMARY KEY (`id`), KEY `news_id` (`m0arid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17198 ; 

Purpose: I want to delete a Timestamp object. Time is better, as I see it.

PS: If you have a general allusion to me, looking at this table, tell me :).

Answer 1

You can switch between Unix dates and timestamps (do not confuse MySQL's TIMESTAMP column TIMESTAMP ) with the following functions:

• UNIX_TIMESTAMP ()
• FROM_UNIXTIME ()

As in your table, I use little to store dates as integers. You cannot calculate a date (or even show it as a date) without first converting it. Also, Unix timestamps in PHP are not extremely portable: some platforms do not even allow dates to be stored until 1970 ...

Answer 2

If you plan to store the unix date stamp in your time column, as suggested by the type ( int(100) ), this makes absolutely no sense.

Internally, Timestamp columns are stored in MySQL as a Unix timestamp. The following quote (from http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp ):

When UNIX_TIMESTAMP () is used in the TIMESTAMP column, the function returns the internal value of the timestamp, without any implicit conversion of "string-to-Unix-timestamp".

confirms my point.

The result you see when you execute the query is simply MySQL, which is trying to print the information.

I suggest saving the timestamp column and using UNIX_TIMESTAMP(timestamp) in your queries when you really need a value as a timestamp.

Answer 3

I would recommend using the date () function instead of time (). Change your time attribute in the MySQL database to a datetime data type. Wherever you are in your php, where you need to record the time, use:

 date("Ymd H:i:s"); 

This will result in a string that exactly matches the MySQL date and time data type format.

(for example, "2011-03-02 08:04:32").

Answer 4

UNIX_TIMESTAMP () http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp