[Resolved] convert text format of date fieldto unix timestamp

[Author]

Posts

[Jeffrey]

Hello, scraped data resource form external, and stored into database, but the custom date field was text format, it can be displayed in front-end, but I have to convert them to Unix timestamp, otherwise, that field cannot be filtered by View. I don't know how to convert them, but I guess that should be two ways

1. add a piece of code to convert that text field to timestamp in database

2. add code to View for converting them to timestamp .

🙂

Can you help with this? Thank you !

[Luo Yang]

Hello,

There isn't such a built-in feature within Views plugin or other Toolset plugin, Views plugin is for rendering the content, it isn't for change the data.

It needs custom codes, for example you can run SQL query to change the date from string to timestamp, see similar thread here:
https://stackoverflow.com/questions/8587177/string-to-timestamp-in-mysql

And you would backup your database first before run those SQL query.

[Jeffrey]

可以要求帮忙吗？我试了半天实在是不会啊，不可以的话 我也完全理解。谢谢~~~

[Luo Yang]

你可以提供你的网站数据库备份文件吗？我需要在本机上进行测试

[Luo Yang]

我已经下载该文件，如果有什么发现，我会更新此贴。

[Luo Yang]

你可以使用MYSQL 工具例如PHPMYADMIN，在你的网站数据库内运行如下SQL语句：

UPDATE wp_postmeta
SET meta_value = (SELECT UNIX_TIMESTAMP(STR_TO_DATE(`meta_value`,'%b-%d-%Y')))
WHERE meta_key='wpcf-available-from'

上面的例子就可以将wpcf-available-from这个custom field 数据更新为timestamp.