Example of converting timestamp to Date in MySQL

Preface

I encountered a situation at work:

In the table of the log system, the time field stores a 13-digit timestamp instead of date data. In business, we need to query the data of a given date by grouping by time and IP.

Of course, you can choose to convert the incoming date to a timestamp at the business layer before querying it, but since MySQL can convert it directly, why not save the operation at the business layer?

1. First, let me introduce the functions in MySQL that convert timestamps and dates into each other:

Convert timestamp to date FROM_UNIXTIME():

FROM_UNIXTIME(1429063399,'%Y-%m-%d %H:%i:%s')

If you don't need hours, minutes, and seconds, '%Y-%m-%d' is fine

The above example uses a 10-digit timestamp. If it is a 13-digit timestamp, you need to use /1000, as follows:

FROM_UNIXTIME(1429063399123/1000,'%Y-%m-%d %H:%i:%s')

Convert date to timestamp using UNIX_TIMESTAMP():

UNIX_TIMESTAMP('2015-04-15')

%Y year, %m month, %d day, %H hour, %i minute, %s second are most commonly used

2. Actual use

In my actual use, I will also
DATE_FORMAT() function (DATE_FORMAT(data,format) function is used to display date/time data in different formats) and FROM_UNIXTIME() to convert:

DATE_FORMAT(FROM_UNIXTIME(DateTime/1000),'%Y-%m-%d')

SELECT ip,DATE_FORMAT(FROM_UNIXTIME(timestamp/1000),'%Y-%m-%d') as date,count(*)
FROM s_page
where DATE_FORMAT(FROM_UNIXTIME(timestamp/1000),'%Y-%m-%d') = ?
GROUP BY ip,DATE_FORMAT(FROM_UNIXTIME(timestamp/1000),'%Y-%m-%d')

? is a placeholder

Summarize

This is the end of this article about converting timestamp to Date in MySQL. For more information about converting timestamp to Date in MySQL, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!