EN
MySQL - combine DATE and TIME column into TIMESTAMP
0 points
In this article, we would like to show you how to combine DATE and TIME column into TIMESTAMP in MySQL.
Quick solution
xxxxxxxxxx1
SELECT TIMESTAMP(`date_column`, `time_column`) AS 'alias_name'2
FROM `table_name`;To show how to combine DATE and TIME column into TIMESTAMP, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will combine registration_date and registration_time column into one - registration_timestamp.
Query:
xxxxxxxxxx1
SELECT TIMESTAMP(`registration_date`, `registration_time`) AS 'registration_timestamp'2
FROM `users`;Result:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `users` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`username` VARCHAR(50) NOT NULL,4
`registration_date` DATE NOT NULL,5
`registration_time` TIME NOT NULL,6
PRIMARY KEY (`id`)7
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `users`2
(`username`, `registration_date`, `registration_time` )3
VALUES4
('Tom', '2021-01-01', '11:41:31'),5
('Chris','2021-01-02', '11:42:45'),6
('Jack','2021-01-03', ' 15:13:39'),7
('Kim','2021-01-03', '15:24:51'),8
('Marco','2021-01-04', '22:35:38'),9
('Kate','2021-01-04', '22:46:51'),10
('Nam','2021-01-04', '22:57:37');