如何在SQL查询中将日期转换为时间戳？

问题描述：

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

我正在尝试将数据从 old_table strong>迁移到 new_table strong >，但在旧表中，date数据类型为 datetime strong>，在新表中，date数据类型为 int strong>，并且正在接受时间戳值。 p>

那么如何在sql查询中转换旧日期，以便它作为时间戳插入到新表中？ p>

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`; code> pre> \ n 我想要一个将旧日期转换为值到时间戳的函数，例如在上面的查询中 CONVERT_INTO_TIMESTAMP（datein） strong> p> 任何帮助都会很高 p> 新表 date_created strong>字段接受unix时间戳中的值，例如： 1540642765 strong> p> 谢谢 p> div>

答

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.

答

Try UNIX_TIMESTAMP() function:

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,
       Unix_timestamp(datein),
       '123',
       ziua
FROM   `old_table`;

From Docs:

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. The date argument may be a DATE, DATETIME, or TIMESTAMP string, or a number in YYMMDD, YYMMDDHHMMSS, YYYYMMDD, or YYYYMMDDHHMMSS format. The server interprets date as a value in the current time zone and converts it to an internal value in UTC.

答

SELECT date_part('epoch', CURRENT_TIMESTAMP)::int;

如何在SQL查询中将日期转换为时间戳？

相关推荐