mysql - Is there a way in Php to compare whether the year, month and day of two times are equal?

My data inventory may contain timestamps of hours, minutes and seconds. Now there is a time that only has year, month and day. I need to compare it. If they are equal, check it out. Can any expert guide me on how to judge and query? Thank you very much.

Reply content:

My data inventory may contain timestamps of hours, minutes and seconds. Now there is a time that only has year, month and day. I need to compare it. If they are equal, check it out. Can any expert guide me on how to judge and query? Thank you very much.

There are quite a few methods, but I think you need a fuzzy query, so I will talk about one that is easier to understand
1. Use the sorttime function to convert the time that only has "year-month-day" into Timestamp, so you get the time at 0 o'clock that day time1;
2. Still use the sorttime function to get the time of +1, or directly add the total number of seconds in a day. The variable name is time2, so there are 2 The time is up
3. Use between query, so that you can get all the data of the day
==========If I understand it wrong, please correct me and I will change it==========

Introducing two methods, the first one is recommended

• As long as the year, month and day are equal, it means querying the data within a certain day range and actively controlling the time range, such as querying the data of '2016-10-28'

<code>$st = strtotime("2016-10-28");
$et = strtotime("2016-10-28 23:59:59");
select * from table where date_field between $st and $et</code>

• Use directly mysql built-in function FROM_UNIXTIME(date_field, '%Y%m%d')

<code>select * from table where FROM_UNIXTIME(date_field,'%Y%m%d') ="2016-10-28"
</code>

The following is the sql that saves the fields in datetime

format

<code>select * from table where date_field between "2016-10-28" and "2016-10-28 23:59:59"</code>

<code>select * from table where DATE_FORMAT(date_field , '%Y%m%d') ="2016-10-28"</code>

If the date formats in the database are the same, use MySQL’s substr function to intercept the date and then compare it

The poster’s question is actually:
Query the data of a certain day (such as October 28, 2016) based on the time field:

<code><?php
$min = strtotime('2016-10-28');
$max = strtotime('2016-10-29');
echo "select * from t where time >= $min and time < $max";
// 输出 select * from t where time >= 1477584000 and time < 1477670400</code>

If the time field time stores not a timestamp, but data in date('YmdHis') format, you can do this:

<code>select * from t where time >= 20161028000000 and time < 20161029000000</code>