John Davidson

Message:

I am using MySQL's DATETIME to set the date and time in two different tables in a database (with PHP). One of these instances is in the users table and logs the time the person signs up for an account and when they last logged in. The other usage is in a separate messages table (within the same database) that logs when a user sends a message to another user.

Although the MySQL DATETIME code is the same in each table, on the messages table it records the date, but the time the message was sent is always exactly midnight e.g. 2021-12-06 00:00:00

In PHPmyAdmin I am using DATETIME, with no default value and not null in both tables.

The PHP prepared statements for each usage are below. As you can see, where the data and time are needed I am using the current_date function.

EXAMPLE 1 users table (time is recorded correctly in the database). Note: some of the variables such as fname are from form elements. I haven't included the form code to keep the code simpler.

$passwordHash = password_hash($pword, PASSWORD_DEFAULT);



$sql = "INSERT INTO lj_users 

            (firstname, lastname, email, username, password, 

            date_registered, last_login, active, profile_image, 

            permissions) 

        VALUES (:firstname, :lastname, :email, :username, :password, 

                current_date, current_date, 0, '', 

                'standard' )";



$stmt = $connection->prepare($sql);



$stmt->bindParam(':firstname', $fname);

$stmt->bindParam(':lastname', $lname);

$stmt->bindParam(':email', $email);

$stmt->bindParam(':username', $uname);

$stmt->bindParam(':password', $passwordHash);



$stmt->execute();   

EXAMPLE 2 (time is NOT recorded correctly in the database)

$message_sql = "INSERT INTO lj_messages 

                    (message_title, message_body, dm_recipient_id, 

                    dm_sender_id, message_date) 

                VALUES (:message_title, :message_body, :dm_recipient_id, 

                        :dm_sender_id, current_date )";



$stmt = $connection->prepare($message_sql);



$stmt->bindParam(':message_title', $dm_title);

$stmt->bindParam(':message_body', $dm_body);

$stmt->bindParam(':dm_recipient_id', $dm_recipient_id);

$stmt->bindParam(':dm_sender_id', $dm_sender_id);

$stmt->execute();

I have absolutely no idea why this is happening.

---

My Answer:
It looks like the issue might be with the way you are using the `current_date` function in your SQL query. The `current_date` function only returns the date portion without the time. If you want to include both the date and time in your DATETIME field, you should use the `current_timestamp` function instead.

Here's how you can modify your second example to use `current_timestamp` instead of `current_date`:

php
$message_sql = "INSERT INTO lj_messages
(message_title, message_body, dm_recipient_id,
dm_sender_id, message_date)
VALUES (:message_title, :message_body, :dm_recipient_id,
:dm_sender_id, current_timestamp )";

$stmt = $connection->prepare($message_sql);

$stmt->bindParam(':message_title', $dm_title);
$stmt->bindParam(':message_body', $dm_body);
$stmt->bindParam(':dm_recipient_id', $dm_recipient_id);
$stmt->bindParam(':dm_sender_id', $dm_sender_id);
$stmt->execute();


By using `current_timestamp`, you should now be able to record both the date and time accurately in your messages table.