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
DATEvalues 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
YYYYMMDDorYYMMDDformat, provided that the number makes sense as a date. For example,19830905and830905are 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):
- Configure Datepicker to provide dates in a supported format using its
dateFormatoption:$( ".selector" ).datepicker({ dateFormat: "yyyy-mm-dd" }); - Use MySQL’s
STR_TO_DATE()function to convert the string:INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y')) - 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))
- format the timestamp using
- Manually manipulate the string into a valid literal:
$parts = explode('/', $_POST['date']); $date = "$parts[2]-$parts[0]-$parts[1]";