php - How can I make a column that stores the time that row was created in mysql?

Question

After tons of googling I could not find an answer. I want to create a column in mysql that stores the the time that row was created as HH:MM:SS

I'm making a chat room and the table currently has a column for the message and id

I dont want do use timestamp or datetime as I don't want to have date month year etc. just Hour min and sec.

I tried time but it always say the time is 00:00:00

EDIT: I guess I want to be able to echo only time from time stamp.

I tried echo '<div><p>User@' . substr($value['Time'], 11) . '->' . $value['message'] . '</p></div>'; But it does not work.

Answer 1

Edited Answer

If you want to use a regular TIMESTAMP field in MySQL, first create the field.

ALTER TABLE  `YourTable` ADD  `LastAccessed` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;

Once you have setup the field you can leave it alone. When you insert a record pass null as the value and it will default to the current time. When you update the record the time will be adjusted.

To obtain the time from the TIMESTAMP field you will need to run your select query and get your results. For illustrative purposes, this would look something like the following:

//Integrate this into your existing code
$result = mysqli_query($connection, "SELECT LastAccessed FROM YourTable"); //get only the time field for this example
$row = mysqli_fetch_array($result);
$time = new DateTime($row['LastAccessed']); //The constructor accepts the time information as an argument
echo $time->format("H:i:s"); //This is your time information.

Since you have a TIMESTAMP field now, not only will the database take responsibility for updating it with the correct time, but you can always modify the $time->format() method's argument to produce a string with time data in a variety of different ways.

The timestamp gets abstracted and your application gain flexibility.

Win-Win, right?

Original Answer

Create a char(8) column in MySQL and then send it the output from a call to:

date("H:i:s");

Although using a timestamp field in MySQL would be optimal, considering you can have it update when the record is interacted with. You don't have to use the entire timestamp.

• Date function in PHP
• Working with timestamps in MySQL