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

And what's your exact problem? Wrong format? Nothing at all? — Louis Huppenbauer, Sep 08 '12 at 18:42
What do `$gutschein->startDate` and `$gutschein->endDate` contain? What purpose does `$time` serve? — eggyal, Sep 08 '12 at 18:44
1346882400000 = Thu, 02 Jan 44651 16:00:00 GMT, did your database need dates from future? — Arthur Halma, Sep 08 '12 at 18:50
You're not doing any [SQL escaping](http://bobby-tables.com/) and this will probably lead to **serious** problems if this application is available on the internet. Use of proper SQL placeholders like `?` is **required** to ensure your queries do not malfunction and/or cost you your job. — tadman, Sep 08 '12 at 20:49
@tadman: As mentioned below, that's a little presumptious. It's not clear from the extract in the OP's question whether those variables have previously been escaped. — eggyal, Sep 08 '12 at 20:56

eggyal · Accepted Answer · 2020-07-28T16:33:41.847

19

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.

edited Jul 28 '20 at 16:33

answered Sep 08 '12 at 18:57

eggyal

122,705
18
212
237

1

You should really strip dangerous data from your response by replacing things with `?` or `:title`. By including these in your answer you're basically an accomplice. – tadman Sep 08 '12 at 20:50
@tadman: I think that's a little harsh. It's not clear whether those variables have been safely escaped, or I would have made that point more emphatically (feel free to look through my many many many detailed responses to posts that are obviously vulnerable to SQL injection). I think that, in this case, the pointer towards parameterised statements is more than sufficient. Accusing me of being an accomplice to propagating SQL injection vulnerabilities is a little OTT. – eggyal Sep 08 '12 at 20:55
1

If it's not absolutely clear they are escaped, which using placeholders ensures, then you can't assume anything. Since these values seem to be coming directly from some kind of object directly, it's extremely unlikely they're escaped. If you rip them out and replace them with placeholders, it conveys the answer without pretending that kind of data insertion isn't a severe bug. If `shortDescription` is `Bob's Shoes` this query is going to fail. – tadman Sep 09 '12 at 02:07
@Downvoter: Care to explain in what way you feel this answer is not useful? – eggyal Mar 20 '14 at 08:20
1

microseconds -> milliseconds – blueFast Dec 05 '17 at 17:25

score 0 · Answer 2 · answered Sep 08 '12 at 18:55

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.

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

$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

FROM_UNIXTIME Reference

The date format should be ISO-standard and for UTC time-zones. — tadman, Sep 08 '12 at 20:52

insert unix timestamp in mysql

2 Answers2