Compare two unix time stamps to see if they are on the same date in MySql

Question

I have read various questions here on Stackoverflow about the use of FROM_UNIXTIME but none directly deal with what I am trying to do. I have one timestamp in a variable coming from php (that has been reformatted - e.g. 25 March 2014) to a function which uses a database query to determine if there are other entries in the database that have the same date (not time). I've run across various methods for formatting and comparing timestamp entries using MySql and ended up with the following but I understand that it isn't very efficient. Does anyone know of a better way to accomplish this?

FROM_UNIXTIME(sd.timestart, "%e %M %Y") = ?'

where the variable in my array for comparison is the date format listed above. This accomplishes what I want but, again, I don't think it is the most efficient way to get this done. Any advice and/or ideas will be much appreciated.

*EDIT* My timestamp is stored as an integer so I'm trying to use:

$thissessiondate = strtotime($date->timestart, strtotime('today')); 

and

$tomorrowdate = strtotime($date->timestart, strtotime('tomorrow'));

to do trim to midnight but get an error (strtotime() expects parameter 2 to be long) and when I move 'today' to the first argument position, I get a conversion to 11 pm instead of 0:00...? I'm making some progress but my very incomplete knowledge of both PHP and MySQL are holding me back.

Answer 1

If you store your date as an int timestamp, you can do this

round(sd.timestart/86400)=round(UNIX_TIMESTAMP(NOW())/86400)

This will get everything in your database that is from the same day.

For example:

SELECT id FROM uploads WHERE (approved=0 OR approved is NULL) AND round(uploads.date/86400)<=round(UNIX_TIMESTAMP(NOW())/86400) order by uploads.date DESC LIMIT 20

Will display all the uploads for today and the days before, without showing the future uploads. 86400 is the number of seconds in one day.

Answer 2

If you can avoid it, don't wrap columns used in predicates in expressions.

Have your predicates on bare columns to make index range scans possible. You want the datatype conversion to happen over on the literal side of the predicate, wherever possible.

The STR_TO_DATE function is the most convenient for this.

Assuming the timestart column is DATE, DATETIME or TIMESTAMP (which it really should be, if it represents a point in time.)

WHERE sd.timestart >= STR_TO_DATE( ? , "%e %M %Y") 
  AND sd.timestart <  STR_TO_DATE( ? , "%e %M %Y") + INTERVAL 1 DAY

Effectively, what that's doing is taking the string passed in as the first argument to the STR_TO_DATE function, MySQL is going to convert that string to a DATETIME, based on the format specified as the second argument. And that effectively becomes a literal that MySQL can use to compare to the stored values in the column.

If there's an appropriate index available, MySQL will consider an index range scan operation to satisfy that predicate.

You'd need to pass in the same value twice, but that's not really a problem.

On the second line, we're just adding a day to the same value. So what MySQL is seeing is this:

WHERE sd.timestart >= STR_TO_DATE( '25 March 2014' , "%e %M %Y") 
  AND sd.timestart <  STR_TO_DATE( '25 March 2014' , "%e %M %Y") + INTERVAL 1 DAY

In terms of performance, that's equivalent to:

WHERE sd.timestart >= '2014-03-15 00:00:00'
  AND sd.timestart <  '2014-03-16 00:00:00'

If you do it the other way around, and wrap timestart in a function, that's going to require MySQL to evaluate the function on every single row (or at least, on every row that isn't filtered out by another predicate first.)

IMPORANT NOTE

Be aware that MySQL interprets datetime values as being in the timezone of the MySQL connection, which defaults to the timezone setting of the MySQL server. MySQL is going to interpret datetime literals in the current setting of the timezone. For example, if MySQL timezone is set to +00:00, then datetime literals will be interpreted as UTC.

---

I assumed the format string matches the data being passed in, I don't use %e or %m. The %Y is a four digit year. (The list of format elements is in the MySQL documentation, under the DATE_FORMAT function.

Reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

---

If your timestart column is INTEGER or other numeric datatype, representing a number of seconds or some other unit of time since the beginning of an era, you can use the same approach for performance benefits.

In the predicate, reference bare columns from the table, and do any conversions required on the literal side.

If you aren't using MySQL functions to do the conversion to "seconds since Jan 1, 1970 UTC" when rows are inserted (which is really what the TIMESTAMP datatype is doing internally), then I wouldn't recommend using MySQL functions to do the conversion in the query either.

If you're doing the conversion from date and time to an integer type "timestamp" in PHP, then I'd do the inverse conversion in PHP as well, and do the trimming to midnight and the adding of a day in PHP.

In that case, your MySQL query would be of the simple form:

  WHERE sd.timestart >= ? 
    AND sd.timestart <  ?

Where you would pass in the appropriate integer values, to compare to the INTEGER timestamp column.

---

Note that MySQL does provide a function for converting to "seconds since Jan 1 1970 UTC", so if timestart is seconds since Jan 1 1970 UTC, then something like this is valid:

WHERE sd.timestart >= UNIX_TIMESTAMP(STR_TO_DATE( '25 March 2014' , "%e %M %Y")) 
  AND sd.timestart <  UNIX_TIMESTAMP(STR_TO_DATE( '25 March 2014' , "%e %M %Y") + INTERVAL 1 DAY)

BUT... again, be aware of timezone conversion issues; if the MySQL database has a different timezone setting than the web server. If you are going to store "integer", then I wouldn't muck that up with the conversion that MySQL does, which may not be exactly the same as the conversion functions the web server does.