If I have a PHP string in the format YYYY-DD-MM and a timestamp in MySQL, is there a good way to convert between them?

Question

I'm interested in doing comparisons between the date string and the MySQL timestamp. However, I'm not seeing an easy conversion. Am I overlooking something obvious?

Solution 1

Converting from timestamp to format:

date('Y-m-d', $timestamp);

Converting from formatted to timestamp:

mktime(0, 0, 0, $month, $day, $year, $is_dst);

See date and mktime for further documentation.

When it comes to storing it's up to you whether to use the MySQL DATE format for stroing as a formatted date; as an integer for storing as a UNIX timestamp; or you can use MySQL's TIMESTAMP format which converts a numeric timestamp into a readable format. Check the MySQL Doc for TIMESTAMP info.

Solution 2

You can avoid having to use strtotime() or getdate() in PHP by using MySQL's UNIX_TIMESTAMP() function.

SELECT UNIX_TIMESTAMP(timestamp) FROM sometable

The resulting data will be a standard integer Unix timestamp, so you can do a direct comparison to time().

Solution 3

I wrote this little function to simplify the process:

/**
 * Convert MySQL datetime to PHP time
 */
function convert_datetime($datetime) {
  //example: 2008-02-07 12:19:32
  $values = split(" ", $datetime);

  $dates = split("-", $values[0]);
  $times = split(":", $values[1]);

  $newdate = mktime($times[0], $times[1], $times[2], $dates[1], $dates[2], $dates[0]);

  return $newdate;
}

I hope this helps