MySQL 在mysql中插入unix时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12333461/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert unix timestamp in mysql
提问by deroccha
I have the following table the attributes are as they follow
我有下表，属性如下
CREATE TABLE `test` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`active` varchar(4) CHARACTER SET latin1 DEFAULT NULL,
`von` date DEFAULT NULL,
`bis` date DEFAULT NULL,
`rabatt` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`code` varchar(64) CHARACTER SET latin1 DEFAULT NULL,
`text_kurz` text CHARACTER SET latin1,
`linkname` varchar(1024) CHARACTER SET latin1 DEFAULT NULL,
`link` varchar(2048) CHARACTER SET latin1 DEFAULT NULL,
`special` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`type` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`hersteller` varchar(128) CHARACTER SET latin1 DEFAULT NULL,
`smb` smallint(1) DEFAULT NULL,
`dhs` smallint(1) DEFAULT NULL,
`sidebar` varchar(4) CHARACTER SET latin1 DEFAULT 'ja',
`img_tag` text CHARACTER SET latin1,
`dm_bild` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `von` (`von`),
KEY `bis` (`bis`),
KEY `type` (`type`),
KEY `active` (`active`),
KEY `code` (`code`),
FULLTEXT KEY `Volltext` (`text_kurz`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
and I would like to insert unix timestamp in german format with php I have been trying the following
我想用 php 以德语格式插入 unix 时间戳我一直在尝试以下
$time = "1057941242";
$qry = 'INSERT INTO test (active, von, bis, rabatt, code, text_kurz)
VALUES("ja",FROM_UNIXTIME('.$gutschein->startDate.'),
FROM_UNIXTIME('.$gutschein->endDate.'),
"'.$gutschein->title.'",
"'.$gutschein->code.'",
"'.$gutschein->shortDescription.'")'
回答by eggyal
Your timestamps (e.g. 1346882400000) appear to be in microsecondssince the UNIX epoch, whereas FROM_UNIXTIME()expects an argument in secondssince the UNIX epoch. You should therefore divide the argument by 1000:
自 UNIX 时代以来，您的时间戳（例如 1346882400000）似乎以微秒为单位，而FROM_UNIXTIME()期望自 UNIX 时代以来以秒为单位的参数。因此，您应该将参数除以 1000：
$qry = 'INSERT INTO test (active, von, bis, rabatt, code, text_kurz)
VALUES("ja",FROM_UNIXTIME('.$gutschein->startDate/1000.'),
FROM_UNIXTIME('.$gutschein->endDate/1000.'),
"'.$gutschein->title.'",
"'.$gutschein->code.'",
"'.$gutschein->shortDescription.'")'
You also ought to consider using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL.
您还应该考虑使用准备好的语句，您将变量作为参数传递到其中，而不会为 SQL 求值。
回答by Nikolaos Dimopoulos
I would suggest that you change your approach a bit.
我建议你稍微改变一下你的方法。
Instead of storing localized data in your database, you could store them in a particular format and "translate" it from there. For instance you are storing dates in the von and bis DATE fields. Instead you can just convert those fields to INT and just store the unix timestamp.
您可以将它们存储在特定格式中并从那里“翻译”它，而不是将本地化数据存储在您的数据库中。例如，您将日期存储在 von 和 bis DATE 字段中。相反，您可以将这些字段转换为 INT 并仅存储 unix 时间戳。
This approach will give you the benefit of translating the dates to whatever locale/format you require - in your case the German one.
这种方法将使您受益于将日期转换为您需要的任何语言环境/格式 - 在您的情况下是德语。
If however, you just want the above to work, then you will need to change your INSERT statement as follows (I am assuming that the $gutschein->startDate is a number i.e. unix timestamp
但是，如果您只想让上述内容起作用，那么您需要按如下方式更改 INSERT 语句（我假设 $gutschein->startDate 是一个数字，即 unix 时间戳
$time = "1057941242";
$qry = 'INSERT INTO test (active, von, bis, rabatt, code, text_kurz) '
. 'VALUES('
. '"ja", '
. 'FROM_UNIXTIME(' . $gutschein->startDate . ', "%Y-%m-%d %H:%i:%s"), '
. 'FROM_UNIXTIME(' . $gutschein->endDate . ', "%Y-%m-%d %H:%i:%s"), '
. '"' . $gutschein->title . '", '
. '"'.$gutschein->code.'", '
. '"'.$gutschein->shortDescription.'")'
If the date format is not the correct one for Germany, please reference the link below for a format that suits you best
如果日期格式不适合德国，请参考以下链接以获取最适合您的格式
