mysql 字符串转unix时间戳,在MySQL中将日期/时间字符串转换为Unix时间戳

We have a database with all the dates and times stored in one column called timestamp. The format of the date/time in the column "timestamp" is as: 03 Aug 08:10am.

I would like to convert this (03 Aug 08:10am) to UNIX TIMESTAMP in MySQL and not PHP because we already have over 500 rows with this format: 03 Aug 08:10am.

I tried create a new INT column called new_timestamp and ran this query:

UPDATE table_name SET new_timestamp = UNIX_TIMESTAMP(timestamp);

However, it shows that 0 rows were affected.

This is not a duplicate, don't redirect me to how to convert in PHP. Read the question first :)

解决方案

The UNIX_TIMESTAMP() function requires a valid date/time format to convert correctly, so you need to convert your existing date/time format to a valid/recognised format (including the year) first. You can do this using MySQL's STR_TO_DATE() function, telling it what format you are passing in, and concatenating in a hard-coded year value as it's always 2016 in your case.

STR_TO_DATE(CONCAT('2016-', ), '%Y-%d %b %h:%i%p')

You can then use the UNIX_TIMESTAMP() function to convert that valid date to your unix timestamp and update all those records in a single step:

UPDATE table_name

SET new_timestamp =

UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('2016-', timestamp), '%Y-%d %b %h:%i%p'));

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值