How to Insert custom date into MySQL timestamp field?

The problem with UNIX_TIMESTAMP() function is that it returns an integer while we want to insert custom date i.e. not any integer part to MySQL date.

Do not use UNIX_TIMESTAMP() for your column defined as TIMESTAMP because UNIX_TIMESTAMP() returns an integer.

Check the UNIX_TIMESTAMP. The query is as follows −

mysql> select UNIX_TIMESTAMP( '2019-01-09 15 −48 −23') AS IntegerValue;

The following is the output −

+--------------+
| IntegerValue |
+--------------+
| 1547029103   |
+--------------+
1 row in set (0.00 sec)

Look at the sample output, the UNIX_TIMESTAMP() function returns an integer of corresponding date and time.

The syntax is as follows to insert custom date for your column TIMESTAMP.

INSERT INTO yourTableName(yourColumnName) values('yourCustomDateTime');

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table TimestampDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> yourTimestamp TIMESTAMP,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (2.14 sec)

To insert custom date to MySQL timestamp field, the following is the query −

mysql> insert into TimestampDemo(yourTimestamp) values('2019-01-09 15 −48 −23');
Query OK, 1 row affected (0.37 sec)

mysql> insert into TimestampDemo(yourTimestamp) values('2018-03-14 16 −4 −23');
Query OK, 1 row affected (0.37 sec)

mysql> insert into TimestampDemo(yourTimestamp) values('2017-12-10 10 −8 −20');
Query OK, 1 row affected (0.16 sec)

mysql> insert into TimestampDemo(yourTimestamp) values('2016-4-05 13 −45 −21');
Query OK, 1 row affected (0.30 sec)

Now you can display all records from the table using select statement. The query is as follows −

mysql> select *from TimestampDemo;

The following is the output −

+----+-----------------------+
| Id | yourTimestamp         |
+----+-----------------------+
| 1  | 2019-01-09 15 −48 −23 |
| 2  | 2018-03-14 16 −04 −23 |
| 3  | 2017-12-10 10 −08 −20 |
| 4  | 2016-04-05 13 −45 −21 |
+----+-----------------------+
4 rows in set (0.00 sec)