How to make a date formatted to insert into MySQL database using PHP

This is great, goes with the jquery ui pop up calendar datepicker. Formats the date to be inserted into mysql’s funky date field.

$my_formatted_date = date(‘Y-m-d’, strtotime($my_unformatted_date));

 

Here is a MySQL select for nothing older than 15 minutes.
select darecord, dadate from mytable where dadate > NOW() – INTERVAL 15 MINUTE

Here is how to calculate the amount time between two time entries:
SELECT TIMEDIFF(`time_in`, `time_out`)  FROM `project_timecards`

 

As stated in Date and Time Literals:

MySQL recognizes DATE values in these formats:

• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.
• As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Therefore, the string '08/25/2012' is not a valid MySQL date literal. You have four options (in some vague order of preference, without any further information of your requirements):

1. Configure Datepicker to provide dates in a supported format using its dateFormat option:

   $( ".selector" ).datepicker({ dateFormat: "yyyy-mm-dd" });

2. Use MySQL’s STR_TO_DATE() function to convert the string:

   INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y'))

3. Convert the string received from jQuery into a PHP timestamp—e.g. using strtotime():

   $timestamp = strtotime($_POST['_date']);

   and then either:

   • format the timestamp using date():

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

   • pass the timestamp directly to MySQL using FROM_UNIXTIME():

     INSERT INTO user_date VALUES ('', '$name', FROM_UNIXTIME($timestamp))

4. Manually manipulate the string into a valid literal:

   $parts = explode('/', $_POST['date']); $date = "$parts[2]-$parts[0]-$parts[1]";