MYSQL:转换时间戳->时间 [英] MYSQL: Convert Timestamp --> Time

问题描述

我有这个MYSQL表:

I have this MYSQL Table:

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

...等等

现在，我需要将TIMESTAMP值复制到TIME行中.新的TIME行是通过PHP命令time();

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

问题:我不知道该怎么做.也许是MySQL命令，也许是通过PHP?请帮帮我！

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

我的桌子:

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:如果您对我有一个总体提示，请查看此表:).

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

推荐答案

您可以使用以下功能在日期和Unix时间戳(不要与MySQL的TIMESTAMP列类型混淆)之间来回切换:

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

• UNIX_TIMESTAMP()
• FROM_UNIXTIME()

• UNIX_TIMESTAMP()
• FROM_UNIXTIME()

关于您的表，很好，我看不到将日期存储为整数的用处.如果不进行事先转换，则无法进行任何日期计算(甚至显示为日期).此外，PHP中的Unix时间戳不是非常可移植的:某些平台甚至不允许存储1970年之前的日期...

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

这篇关于MYSQL:转换时间戳->时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！