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
orYYMMDD
format, provided that the number makes sense as a date. For example,19830905
and830905
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):
- Configure Datepicker to provide dates in a supported format using its
dateFormat
option:$( ".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]";