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

Question

I am interested in making comparisons between a date string and a MySQL timestamp. However, I do not see an easy conversion. Can I ignore something obvious?

Answer 1

Convert from timestamp to format:

date('Ym-d', $timestamp); 

Converting from formatted to timestamp:

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

See date and mktime for further documentation.

When you need to save it to you, should you use the MySQL DATE format for a string as a formatted date; as an integer to be stored as a UNIX timestamp; or you can use the MySQL TIMESTAMP format, which converts a numeric timestamp into a readable format. Check the Docs file for TIMESTAMP information.

Answer 2

You can not use strtotime() or getdate() in PHP with the MySQL UNIX_TIMESTAMP() function.

 SELECT UNIX_TIMESTAMP(timestamp) FROM sometable 

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

Answer 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

Answer 4

strtotime () and getdate () are two functions that can be used to get dates from strings and timestamps. However, there is no standard library function that converts MySQL and PHP timestamps.

Answer 5

Use the PHP Date function. You may need to convert the mysql timestamp to the Unix timestamp in your query using the UNIX_TIMESTAMP function in mysql.

Answer 6

Form Date String:

 YYYY-MM-DD 

has no time associated with it. The MySQL timestamp is:

 YYYY-MM-DD HH:mm:ss 

to compare the two, you will need to add time to the date string, for example, at midnight, for example

 $datetime = '2008-08-21'.' 00:00:00'; 

and then use the function to compare the time between them

 if (strtotime($datetime) > strtotime($timestamp)) { echo 'Datetime later'; } else { echo 'Timestamp equal or greater'; }