dslijian2015
2018-10-27 12:20
浏览 694
已采纳

如何在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

图片转代码服务由CSDN问答提供 功能建议

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

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

  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`; 
   \  n 
 

我想要一个将旧日期转换为值到时间戳的函数,例如在上面的查询中 CONVERT_INTO_TIMESTAMP(datein)

任何帮助都会很高

新表 date_created 字段接受unix时间戳中的值,例如: 1540642765

谢谢

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douruoshen1449 2018-10-27 12:23
    已采纳

    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.

    点赞 评论
  • duanhe3393 2018-10-27 12:24

    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.

    点赞 评论
  • douquanzhan0315 2019-06-30 14:16
    SELECT date_part('epoch', CURRENT_TIMESTAMP)::int;
    
    点赞 评论

相关推荐 更多相似问题