Search code examples
mysqlsql

How to convert date into timestamp in SQL query?

I'm trying to migrate data from old_table to new_table, but in old table the date datatype is datetime and in new table the date datatype is int and is accepting timestamp value.

So how to convert the old date in sql query so that it get inserted in new table as timestamp?

INSERT INTO `new_table` (`id`, `user_id`, `doctor_id`, `message_id`, `type`, `is_message`, `is_note`, `doctor_initials`, `call_status`, `message`, `date_created`, `date_updated`, `day`)
  SELECT id, usid, drid, message_id, type, is_message, is_note, doctor, kall, message, datein, 123, ziua
  FROM `old_table`;

I want a function which convert old date to value to timestamp e.g in the above query CONVERT_INTO_TIMESTAMP(datein)

Any help will be highly appreciated.

New Table date_created field accepts values in unix timestamp such as : 1540642765

Thanks

Solution

  • The function you are looking for is UNIX_TIMESTAMP(), e.g.

    select UNIX_TIMESTAMP('1970-01-01 00:00:00');

    gives 0 in the UTC timezone.