Insert current date to the database?

Question

How do I insert the current date to my database? I have a column called date to put it on. I want to insert it at the same time I insert this:

$sql="INSERT INTO `Lines` (Text, PID, Position)
VALUES
('$text','$pid','$position')";

Is there a way to automate it in PHPMyAdmin or it's the same to do it this way? Thanks

Answer 1

If the table definition has the timestamp column default set to CURRENT_TIMESTAMP, you actually don't have to do anything at all. Otherwise, NOW() and CURRENT_TIMESTAMP will work, as in:

INSERT INTO t1 (timestamp_column) VALUES (NOW());

There is a difference between CURRENT_TIMESTAMP and NOW() but it's probably too small to matter to you.

phpMyAdmin seems like it has CURRENT_TIMESTAMP as an option when creating a new column.

Answer 2

INSERT INTO `Lines` (`date`) VALUES (NOW());

Answer 3

Depending on your requirement, you can also do this

$date=date('d.m.y h:i:s');

And then insert $date. I mean if you only want to view the date & time. Otherwise i also recommend time().

Answer 4

Otherwise... if you're using an integer, as is common in PHP, just use time() and insert it's value the same way you inserted the other variables, or use MySQL's UNIX_TIMESTAMP()

Answer 5

The best way to store timestamps is as UNIX timestamp integers in GMT / UTC. This allows you to always know the exact time no matter where you, your server, or your users are located. A bonus is that you can allow your users to set their timezone and display times meaningful to them.

$sql = "INSERT INTO `Lines` (`timestamp`) VALUES ('" . time() . "')";

or

$sql = "INSERT INTO `Lines` (`timestamp`) VALUES ( UNIX_TIMESTAMP() )";

Be careful if you choose to use NOW() or CURRENT_TIMESTAMP as they are managed by the database server and it's settings. If the server your site is hosted on is in one time zone and you move to another host in another timezone all of your timestamps will be incorrect. Using Unix integers will add a little extra effort wherever you application deals with times but it gives you the most accuracy and the most flexibility.