Best Way to Insert a unix timestamp using PHP into a mySQL integer field (INT)?

Question

I have a table and theDate field is structured as INT (32) .

Writing PHP scripts I would like to use mySQL PDO to insert the unix timestamp as an integer into theDate INT table field. What is the best way to do this? And what is the most efficient size for my theDate field? 8, 16, 32?

code:

$theDate = now();  // this returns a 0 in theDate mySQL field
    $stmt = $db->prepare("INSERT INTO countries(name, capital, language, theDate) VALUES (:country, :capital, :language, :theDate)");

$stmt->bindParam(':country', $country, PDO::PARAM_STR, 100);
$stmt->bindParam(':capital', $capital, PDO::PARAM_STR, 100);
$stmt->bindParam(':language', $language, PDO::PARAM_STR, 100);
$stmt->bindParam(':theDate', $theDate );
if($stmt->execute()) {
  echo '1 row has been inserted';  
}

I have googled this and searched this site.. can't find a good answer

thanks

Don't - use a proper datetime field instead; it makes life much easier when you want to retrieve anything by date... though, in this case, it looks like you might want a timestamp field... I can't see any logical reason to store the date on a country table except to record the time at which the record was last updated. — CD001
– CD001, Commented Sep 29, 2016 at 14:24
I'm with @CD001 here. The most efficient size is a DATETIME field, and using the native format gives you access to tons of additional functions as well as making them human readable. Be sure to insert these times as UTC to avoid time-zone issues. You can do any time-zone conversion in PHP when displaying these values if that's relevant. — tadman
– tadman, Commented Sep 29, 2016 at 15:20

Your Common Sense · Accepted Answer · 2016-09-29 14:26:19Z

2

The best way to store a datetime information in mysql is to use a datetime field. For which you can use the very now() function you were trying. But of course you have to call it in SQL, not PHP:

$sql = "INSERT INTO countries(name, capital, language, theDate) VALUES (?, ?, ?, now())";
$stmt = $db->prepare($sql);
$stmt->execute([$country,$capital,$language]);

if you insist on using unreliable and outdated unix_timestamp, you have to use unix_timestamp() SQL function instead of now(). The code would be the same as above, save for the function name.

If you want to send an int value to a database via PDO - just send it:

$theDate = time();
$sql = "INSERT INTO countries(name, capital, language, theDate) VALUES (?, ?, ?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute([$country,$capital,$language,$theDate]);

answered Sep 29, 2016 at 14:26

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Best Way to Insert a unix timestamp using PHP into a mySQL integer field (INT)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related