There's a situation at work :

In the table of the log system , The time field stores 13 A time stamp timestamp Not date data , And in business , We need to go through time and ip To query the data of a given date in groups .

Of course, you can choose to convert the incoming date into a timestamp in the business layer , Then go to the inquiry , But since mysql Now that you can convert directly , So why not save the operation in the business layer ?

1. Let's first introduce mysql A function that converts a timestamp to a date :

Time stamp converted to date FROM_UNIXTIME():
FROM_UNIXTIME(1429063399,'%Y-%m-%d %H:%i:%s')
If you don't need hours, minutes, seconds ,'%Y-%m-%d' Just fine
In the example above 10 A time stamp , if 13 Bit timestamp requires /1000, as follows :
FROM_UNIXTIME(1429063399123/1000,'%Y-%m-%d %H:%i:%s')

Date to timestamp UNIX_TIMESTAMP():
UNIX_TIMESTAMP('2015-04-15')

%Y year 、%m month 、%d Japan 、%H when 、%i branch 、%s The second is most commonly used

2. The actual use

In my practical use , I will also
DATE_FORMAT() function (DATE_FORMAT(data,format) Function to display dates in different formats / Time data ) and FROM_UNIXTIME() To switch :
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')

? As a placeholder