• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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.
0
shishir_sri
Asked:
shishir_sri
  • 3
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Presuming you have the correct format for http://us3.php.net/strtotime defined, the issue is likely the format which the $date is represented by default when concatenated into the SQL string used in MySQL.  Try explicitly formatting it as shown when you look at the column "0000-00-00 00:00:00" (i.e., yyyy-mm-dd hh:mi:ss)
0
 
Ray PaseurCommented:
Are you sure you want a TIMESTAMP column?  This might make more sense in a DATETIME column.  Either way, please have a look over here:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
shishir_sriAuthor Commented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ray PaseurCommented:
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!
0
 
shishir_sriAuthor Commented:
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
0
 
Ray PaseurCommented:
Sounds good!  Best regards, ~Ray
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now