timestamp is 0000-00-00 00:00:00 in database

[clankill3r]

$pubDate:

 

Thu, 01 Dec 2011 20:22:52 +0100

 

$timestamp = strtotime($pubDate);

gives:

1322767372

 

so that seems to be ok.

Also i get no query errors for inserting, only all values it inserts are  0000-00-00 00:00:00

 

I tried different setting in my database, this was the last:

 

# Kolom       Type               Collatie Attributen                                         Null     Standaardwaarde                   Extra

5 timestamp timestamp         on update CURRENT_TIMESTAMP Nee       CURRENT_TIMESTAMP ON      UPDATE CURRENT_TIMESTAMP

 

[floridaflatlander]

How do you input your time? Yesterday I put 'Now()'  with single quotes in my sql statement by mistake instead of Now() and it entered 0000-00 etc, in my database.

[clankill3r]

$query = "INSERT INTO articles VALUES ('', '$headline', '$description', '$broadtext', '$timestamp', '$categoryID')";

 

where $timestamp = 1322767372 for example.

[Pikachu2000]

Illegal DATETIME' date=' DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00 00:00:00' or '0000-00-00'). [/quote']

 

You're attempting to insert a UNIX timestamp into a MySQL TIMESTAMP field.

[clankill3r]

How do i get a MySQL timestamp out of 'Thu, 01 Dec 2011 20:22:52 +0100' ?

[Pikachu2000]

In the query string, you can do it with STR_TO_DATE().

 

$stamp = 'Thu, 01 Dec 2011 20:22:52 +0100';
$query = "INSERT INTO table (time_field) VALUES ( STR_TO_DATE('$stamp', '%a, %d %b %Y %T') )";

[clankill3r]

thanks, the insert works now.

 

One more thing, i have a function to check if something is inserted:

 

function timeStampExists($pubDate) {
//$query = "SELECT * FROM articles WHERE timestamp='$timestamp'"; 
$query = "SELECT * FROM articles WHERE timestamp='STR_TO_DATE('$pubDate', '%a, %d %b %Y %T')'"; 
$res = mysql_query($query); 
if (mysql_num_rows($res) > 0) { 
	return true;
} else {
	return false;
} 
}

 

I only get this error:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /

on this line:

 

if (mysql_num_rows($res) > 0) {

 

[Pikachu2000]

Remove the quotes that are enclosing the entire STR_TO_DATE function.

[clankill3r]

thanks

[smd]

Hi,

 

Sorry to open this older post, but since my question connects so much to this topic, I hope somebody could help me out with something I must be doing wrong:

 

I load rss-feeds into my database and receive  0000-00-00 00:00:00 results in the mysql column pubdate (DATETIME type) when using:

 

mysql_query("INSERT INTO rss_feeds (id, title, description, link, pubdate) 
VALUES (
'', 
'".mysql_real_escape_string($item->title)."', 
'".mysql_real_escape_string($item->description=htmlspecialchars(trim($item->description)))."', 
'".mysql_real_escape_string($item->link)."', 
'".mysql_real_escape_string($item->pubdate)."')");

 

With the solution given in this topic I tried several combinations of $item->pubdate and STR_TO_DATE but my codes give errors al the time.

mysql_query("INSERT INTO rss_feeds (id, title, description, link, pubdate) 
VALUES (
'', 
'".mysql_real_escape_string($item->title)."', 
'".mysql_real_escape_string($item->description=htmlspecialchars(trim($item->description)))."', 
'".mysql_real_escape_string($item->link)."', 
'".mysql_real_escape_string(STR_TO_DATE('$item->pubdate', '%a, %d %b %Y %T')."')");

 

It must be something simple but I cant find it.. Anybody who wants to help me out?