Php mysql date / time question
I created a small application that has User Management, an interface console for data entry, and a backend console for managing parts of the interface. The interface adds rows to the MySQL database that are timestamped. The backend should be able to select rows from the database between dates X and Y.
Everything works so far, except for the part of the date that I'm really struggling with.
The external SQL input looks like this (simplified by removing the fake code):
$date = time();
$top_level_category = $_POST['top_level_category'];
$sub_level_category = $_POST['sub_level_category'];
$company = $_POST['company'];
$agent_name = $_POST['agent_name'];
$ticket_id = $_POST['ticket_id'];
$sql = "INSERT INTO dacc_data ("
. "id, top_level_category, sub_level_category, "
. "agent_name, date, ticket_id, company"
. ") VALUES ("
. "NULL, '$top_level_category', '$sub_level_category', "
. "'$agent_name', FROM_UNIXTIME('$date'), '$ticket_id', '$company'"
. ")"
;
$result = mysql_query($sql) or die (mysql_error());
Everything seems to work fine, the timestamp is picked up and added to the DATETIME column in my table. It shows up as dd / mm / yyyy hh: mm: ss in the database.
So ... my first question is the correct way to do this?
The second question is which SQL statement would be to pull the array of strings between dates X and Y.
Sorry if this is a little scary, I hope this is clear, but if you need more information, let me know.
source to share
MySQL datetime must be formatted with a dash:
YYYY-MM-DD HH: MM: SS
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
Then you can query date ranges in several ways:
select *
from table
where date >= '[start date]' and date <= '[end date]';
or
select *
from table
where date between '[start date]' and '[end date]';
where "table" is the name of your database table and "date" is the name of your datetime field.
source to share
You're right. I can confirm that there is "YYYY-MM-DD HH: MM: SS" in the database. I am using SQLWave editor for quick DB browsing, it formats DATETIME column automatically.
// Initial questions still stand :)
Or not, just noticed that you updated the answer - thanks a lot! I have actually tried this very query several times to no avail, mainly because my WHERE was incorrectly specifying the date format. Error in SQLWave :(
Let's get back to using the command line from now !!!
source to share