mysql datetime field

Question

I'm parsing a feed that contains a "created at" entry, formatted like this:

Thu, 08 Oct 2009 23:01:33 +0000

I need to be able to store this in a MySQL table, in a DateTime datatype. Then, I want to select rows WHERE datetime clause equals or in range between dates and/or times.

I have two questions:

1. Will this string be acceptable as a MySQL DateTime datatype?
2. If yes, which query should I construct to get results from and between dates or hours?

Answer 1

MySQL will accept a string as a date in the following format

YYYY-MM-DD HH:MM:SS

SELECT * FROM tbl WHERE datestamp = '2009-10-08 23:01:33';

Answer 2

select * from table where date1 between date2 and date3;

Answer 3

You can find STR_TO_DATE function usefull for date parsing in mysql.

mysql> SELECT STR_TO_DATE('Thu, 08 Oct 2009 23:01:33 +0000', '%a, %d %b %Y %H:%i:%s +0000');
    -> '2009-10-08 23:01:33'

Available formats are the same as for DATE_FORMAT

Answer 4

You could do something along these lines (in your language of choice) to turn your string into an acceptable mysql datetime:

$inputdate = "Thu, 08 Oct 2009 23:01:33 +0000";
$unixdate = strtotime($inputdate); //output: 1255042893
$datetime = date("Y-m-d H:i:s", $unixdate); //output: 2009-10-08 23:01:33

There are a few ways to find records between two dates, this is my favourite:

SELECT * FROM table WHERE date >= '2009-07-01 00:00:00' and date <= '2009-10-31 23:59:59'