Blaze Mathew Blaze Mathew - 3 months ago 7
MySQL Question

MYSQL timestamp coming as null on insert

I am adding a user selected timestamp to MYSQL table using the query

INSERT INTO schedule( pending_id, scheduled_time )
VALUES (
"477", TIME( STR_TO_DATE( "09/02/2016 12:33 PM", "%m/%d/%y %h:%i %p")))


But the value of field scheduled_time is coming as NULL in DB. What could be the issue?

Answer

The MySQL TIME() function extracts the time portion of a timestamp. Hence your INSERT query is the same as doing this:

INSERT INTO schedule (pending_id, scheduled_time)
VALUES ("477", '12:33:00')

In other words, you are not actually inserting a timestamp, but rather just a time string fragment. This is resulting in a NULL value being inserted.

Instead, you should have just stopped after calling STR_TO_DATE:

INSERT INTO schedule (pending_id, scheduled_time)
VALUES ("477", STR_TO_DATE("09/02/2016 12:33 PM", "%m/%d/%Y %h:%i %p"))

which would become

INSERT INTO schedule (pending_id, scheduled_time)
VALUES ("477", '2016-09-02 12:33:00')

Update:

I also changed the format string you were using in STR_TO_DATE from

%m/%d/%y %h:%i %p

to

%m/%d/%Y %h:%i %p

since your input string has the year as 4 digits (%Y), rather than two (%y).