Convert timestamp to date in SQL

Mar 10, 2022#sql #时间戳44

AI Translation

This post is translated from Chinese into English through AI.View Original

AI-generated summary

The article discusses how to convert timestamps to dates in SQL. The author explains that they are integrating their blog with the Typecho backend to create an online "say" feature. They mention that Typecho exports data in timestamp format, which makes it inconvenient for frontend use. To solve this issue, they suggest using the SQL query statement `FROM_UNIXTIME(created)` to convert the timestamp to a readable date format. They provide an example of the SQL query statement and mention that the exported data can be customized using the `AS` parameter. The author concludes by expressing gratitude for guidance received.

Requirement: I want to integrate my blog with the typecho backend and create a functionality for online status updates. I will input the content in typecho and then call an API to display the status updates on my blog. It may sound a bit complicated, but I think it's worth trying.

By default, the data exported from typecho is in timestamp format, which makes it inconvenient for frontend calls. Therefore, I chose to convert it directly in the SQL query. "created" is a parameter in the table.

// Conversion statement for time
FROM_UNIXTIME(created)

// Database query statement
$sql = "select FROM_UNIXTIME(created),text from ... order by created desc";

The exported data looks like this.

So, I will customize the previous statement using the "as" parameter.

$sql = "select FROM_UNIXTIME(created) as created,text from ... order by created desc";

The successful export makes the subsequent calls much simpler.

Thanks to 梦繁星 for the guidance.