MySQL DATETIME shows correct date, but incorrect time (always midnight) in one table but not in anot-CodePudding

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.

CodePudding user response：

current_date returns just a date:

mysql> select current_date; 
 --------------                                                                                                                                                                                                             
| current_date |                                                                                                                                                                                                            
 --------------                                                                                                                                                                                                             
| 2021-12-07   | 
 --------------

You want now(), which returns a datetime.

mysql> select now();
 --------------------- 
| now()               |
 --------------------- 
| 2021-12-07 09:33:40 |
 ---------------------