asked on

Insert Date into MySQL table

Hey,

I have a web service that receives certain data. This data includes a "date" field which contains the date in the following format:
"Tue Apr 07 22:52:51 +0000 2009"

I need to insert this date into a MySQL table which contains a TIMESTAMP column.

Currently, I'm trying to do this:

$date = strtotime($strDate); // Here $strDate is the supplied date in the above mentioned format

Query: INSERT INTO <table_name> (`id`, `date_col`) VALUES (NULL, $date);

The problem is that the row is inserted successfully, but the value inserted into the timestamp column is "0000-00-00 00:00:00"

Please let me know where I'm going wrong. Its pretty urgent.

Thanks,
Shishir S.

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shishir_sri

ASKER

Hey,

Thank you for the replies. The link provided by Ray Paseur was very helpful.

I went through the link and made a few changes to my code. I've realized that the problem is not in the time conversion. The time is being converted to the proper timestamp. For example, One of the dates that I converted to timestamp gives me 1304867725 which is correct for that date. Now its a matter of inserting it into the table.

For now, my query is:

"INSERT INTO `dbName`.`tableName` (`id`, `created_at`, `lat`, `lng`) VALUES (NULL, 1304867725, 32.1287742, -81.2609903);"

Here: created_at is the TIMESTAMP column.

Please let me know if I'm inserting the value incorrectly.

best,
Shishir

Ray Paseur

It looks OK to me. If you do not have phpMyAdmin, get it and install it. You can use it to visualize the contents of the tables. Very helpful!

shishir_sri

ASKER

Hey,

Thanks Ray. I already have phpMyAdmin. I used it and realized that I cannot directly insert the timestamp for some reason. So I followed mwvisa1's advise and it worked.

Thanks for taking the time to help me out.

best,
Shishir

Ray Paseur

Sounds good! Best regards, ~Ray