MYSQL: Convert Timestamp --> Time

Question

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 :).

Why is the type of your time column int(100) ? what is the format you want to use ? — krtek, Mar 02 '11 at 15:36
@Krtek the Unix timestamp. @Tomalak Geret'kal Sadly I can't :/. — user606346, Mar 02 '11 at 15:44
`int(100)` - the (100) portion is simply a display hint - "show 100 digits". It has no bearing on how the number is stored. internally an int is an int and occupies 4 bytes of storage space, whether you want 1 digit or 100. — Marc B, Mar 02 '11 at 15:50
@user606346 like said in my answer, mysql timestamps are in fact unix timestamps, there's no point to the conversion you want to make. Internaly it will be **exactly** the same, but without the type checking mysql can do with the timestamp type. — krtek, Mar 02 '11 at 15:53

score 2 · Accepted Answer · answered Mar 02 '11 at 15:43

2

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...

answered Mar 02 '11 at 15:43

Álvaro González

142,137
41
261
360

MySQL's TIMESTAMP type are in fact Unix timestamps internaly, like explained in my answer, there's no confusion about this ;) But I agree about keeping the timestamp column – krtek Mar 02 '11 at 15:51

score 2 · Answer 2 · answered Mar 02 '11 at 15:45

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.

ugh it was all changed . my apologies – Your Common Sense Mar 02 '11 at 15:47 — Your Common Sense, Mar 02 '11 at 15:47

score 2 · Answer 3 · answered Mar 02 '11 at 16:09

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").

score 0 · Answer 4 · answered Mar 02 '11 at 15:38

0

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

answered Mar 02 '11 at 15:38

Detect

2,049
1
12
21

1

This conversion has absolutely no sense, MySQL store the timestamp type as an unix timestamp internaly. If your time column will be used to store the unix timestamp, you can stick with timestamp it is **exactly the same**. The following quote from the documentation "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." backups my claim ;) – krtek Mar 02 '11 at 15:40

MYSQL: Convert Timestamp --> Time

4 Answers4

Linked