MySQL Timestamp format

Question

I have exported my database to a CSV file and the timestamp now liiks like this:

1384204028

How can I convert it to the typical format, for example 2013-01-19 03:14:07 ?

Nancinancie

Answer 1

Use FROM_UNIXTIME()

SELECT FROM_UNIXTIME(1384204028);

or (equivalent but with parameter to control the format):

SELECT FROM_UNIXTIME(1384204028, '%Y-%m-%d %H:%i:%s');

Perished

Answer 2

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') 

Galven

Answer 3

if you just want to add a line in your DB-table, where you have a field as TIMESTAMP, you don't need to call a function. You just can pas a string, sql will do the rest.

INSERT INTO `DB_TABLE`(`id`, `message`, `next_time`) VALUES (12, 'hallo world', '20180601151343')

and will even work like this:

INSERT INTO `DB_TABLE`(`id`, `message`, `next_time`) VALUES (12, 'hallo world', '2018-06-01 15:13:43')

Circumfuse