Convert timestamp to date in SQL

Mar 10, 2022#sql #时间戳61

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 for a blog and Typecho integration project. The author faced difficulties in displaying the data in the frontend due to the timestamp format, so they used a SQL query to convert it. They also used the "as" parameter to customize the query and make it easier to call in the future. The author thanks someone named "梦繁星" for their guidance.

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 choose 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 use the "as" parameter to customize the previous statement.

$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.