CODEWITHSUNDEEP
phpmysqltimetimestamp
user606346
user606346

Reputation: 157

MYSQL: Convert Timestamp --> Time

I have this MYSQL Table:

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

... and so on

Now I need to copy the TIMESTAMP value into the TIME rows. The new TIME rows are created via the PHP command time();

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

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 ;

Goal: I want to remove the Timestamp thing. Time is better, as I see it.

PS: If you have a general hint for me, looking at this table, please tell me :).

Upvotes: 4

Views: 10028

Answers (4)

Tim Habersack
Tim Habersack

Reputation: 1512

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

date("Y-m-d H:i:s");

This will generate a string that exactly fits the format of the MySQL datetime data type.

(ex. "2011-03-02 08:04:32").

Upvotes: 2

krtek
krtek

Reputation: 26617

If you plan to store the unix timestamp of the date in your time column as suggested by the type (int(100)), this has absolutely no sense at all.

Internally, Timestamp columns are stored using as Unix timestamp by MySQL. 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 on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.

proves my point.

The result you see when you do a query is only MySQL trying to pretty print the information.

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

Upvotes: 2

Álvaro González
Álvaro González

Reputation: 146640

You can switch back and forth between dates and Unix timestamps (don't confuse with MySQL's TIMESTAMP column type) with these functions:

As about your table, well, I see little use for storing dates as integers. You cannot do any date calculation (or even display as date) without making prior conversions. Furthermore, Unix timestamps in PHP are not extremely portable: some platforms won't even allow to store dates before 1970...

Upvotes: 2

Detect
Detect

Reputation: 2069

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

Upvotes: 0

Related Questions