Combine date and time column into a timestamp in MySQL?

To combine date and time column into a timestamp, you can use cast() function with concat().

The syntax is as follows −

select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName;

In the above concept, you will use cast() when your date and time is in string format. The cast() function can be used only for datetime. To understand the above syntax, let us create a table.

The query to create a table is as follows −

mysql> create table DateAndTimeToTimestamp
   −> (
   −> Duedate date,
   −> DueTime time
   −> );
Query OK, 0 rows affected (0.51 sec)

Insert records in the table using insert command. The query is as follows −

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval 2 day),'10:30:02');
Query OK, 1 row affected (0.34 sec)

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval -2 day),'12:20:45');
Query OK, 1 row affected (0.17 sec)

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval 1 day),'01:32:42');
Query OK, 1 row affected (0.21 sec)

mysql> insert into DateAndTimeToTimestamp values(date_add(curdate(),interval -1 day),'14:25:58');
Query OK, 1 row affected (0.30 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from DateAndTimeToTimestamp;

The following is the output −

+------------+----------+
| Duedate    | DueTime  |
+------------+----------+
| 2018-12-16 | 10:30:02 |
| 2018-12-12 | 12:20:45 |
| 2018-12-15 | 01:32:42 |
| 2018-12-13 | 14:25:58 |
+------------+----------+
4 rows in set (0.00 sec)

Here is the query to combine date and time column into timestamp −

mysql> select concat(Duedate, ' ', DueTime) as timestampDemo from DateAndTimeToTimestamp;

The following is the output −

+---------------------+
| timestampDemo       |
+---------------------+
| 2018-12-16 10:30:02 |
| 2018-12-12 12:20:45 |
| 2018-12-15 01:32:42 |
| 2018-12-13 14:25:58 |
+---------------------+
4 rows in set (0.00 sec)