在MySQL中将日期/时间字符串转换为Unix时间戳 [英] Converting date/time string to unix timestamp in MySQL
问题描述
我们有一个数据库,其中所有日期和时间都存储在称为时间戳的列中. 时间戳"列中的日期/时间格式为:08 Aug,08:10am.
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.
我想在MySQL(而不是PHP)中将此(03 Aug 08:10 am)转换为UNIX TIMESTAMP,因为我们已经有超过500行采用以下格式:03 Aug 08:10 am.
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.
我尝试创建一个名为new_timestamp
的新INT列并运行此查询:
I tried create a new INT column called new_timestamp
and ran this query:
UPDATE table_name SET new_timestamp = UNIX_TIMESTAMP(timestamp);
但是,它表明有0行受到影响.
However, it shows that 0 rows were affected.
这不是重复的内容,请勿将我重定向到如何在PHP中进行转换.首先阅读问题:)
推荐答案
UNIX_TIMESTAMP()
函数需要有效的日期/时间格式才能正确转换,因此您需要将现有的日期/时间格式转换为有效/可识别的格式(包括年份)第一.您可以使用MySQL的STR_TO_DATE()
函数执行此操作,告诉它您要传入的格式,并以硬编码的年份值进行连接,因为在您的情况下始终为2016.
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-', <your date/time value>), '%Y-%d %b %h:%i%p')
然后,您可以使用UNIX_TIMESTAMP()
函数将该有效日期转换为您的unix时间戳,并在一个步骤中更新所有这些记录:
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'));
这篇关于在MySQL中将日期/时间字符串转换为Unix时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!