insert unix timestamp in mysql

Question

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

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

Answer 1

Your timestamps (e.g. 1346882400000) appear to be in milliseconds since the UNIX epoch, whereas FROM_UNIXTIME() expects an argument in seconds since the UNIX epoch. You should therefore divide the argument by 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.