Search code examples
mysqlsql-insertunix-timestamp

Using FROM_UNIXTIME in an INSERT INTO statement

Is it possible to use the FROM_UNIXTIME method in a MySQL INSERT INTO statement, like below?

INSERT INTO mycapacity (timeint, time, datetime) VALUES (FROM_UNIXTIME(20191120085412, '%D %M %Y %h:%i:%s %x'), '20 Nov 2019 16:54:12 GMT', '2019-11-20 08:54:12');

If so, does FROM_UNIXTIME have to be in quotes as the column type is varchar?

Solution

  • MySQL prefers everything to be in ISO 8601 format, or YYYY-MM-DD HH:MM:SS and not some arbitrary text. Use native DATE, DATETIME columns for storing the data.

    FROM_UNIXTIME() emits it in the correct format, that should be fine without the format specifier. It's worth noting that this function takes UNIX Epoch Time values only, not arbitrary date strings like 20191112... which is not valid. The time right now is 1574274411 for example.

    If you have an integer-encoded date-time value then FROM_UNIXTIME will not help and you'll have to parse it using STR_TO_DATE, as in:

    STR_TO_DATE('2019112', '%Y%m%d')

    It's not clear exactly what's going on in that number, mind you, so the format of the remainder will need to be addressed with additional specifiers, if it even can be.

    Keep in mind any columns with names that are reserved keywords must be escaped:

    INSERT INTO mycapacity (timeint, `time`, `datetime`) VALUES (...)

    It's far from clear in your question what format you're intending to use for each column, so specifying the schema would help narrow down an answer.