CODEWITHSUNDEEP
sqlmysqldatetime
Yaniv Golan
Yaniv Golan

Reputation: 982

mysql datetime field

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?

Upvotes: 1

Views: 4579

Answers (4)

Zoe
Zoe

Reputation: 2169

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'

Upvotes: 1

Littlejon
Littlejon

Reputation: 1066

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';

Upvotes: 2

Ivan Nevostruev
Ivan Nevostruev

Reputation: 28713

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

Upvotes: 3

codymanix
codymanix

Reputation: 29468

select * from table where date1 between date2 and date3;

Upvotes: 0

Related Questions