DATETIME is only storing the date and not the time

Databases
1

I am having an issue with the DATETIME function in mysql. When I store a record in the table, I am trying to use DATETIME to record the date and time is was entered but whenever a record is entered, the correct date is displayed but the time is always 0. Meaning to say that it always displays as: 2021-01-06 00:00:00.

I have looked around online but cannot see anywhere to help me solve this.

I have tried both DATETIME and TIMESTAMP with both the default set to NONE and CURRENT_TIME, but none of those combinations are storing the time.

2

first of all, DATETIME is a data type, not a function.

Are you mistaking DATETIME for CURTIME() ? How exactly are you entering the record?

3

Sorry, I should have paid more attention to the wording I was using. I did mean Type and not function.

I am using a prepared statement to insert the record into the table:

INSERT INTO card (title, content) VALUES (?, ?);

and letting mysql insert the date and time.

The record is created as expected but the ‘created’ column always inserts as 2021-01-06 00:00:00.

4

Run a EXPLAIN card query and show us the result?

5
Field Type Null Key Default Extra
card_id int(5) NO PRI NULL auto_increment
title varchar(64) NO NULL
content mediumtext NO NULL
created datetime NO current_timestamp()
6

works for me –

CREATE TABLE card
( card_id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT 
, title VARCHAR(64) NOT NULL  
, content MEDIUMTEXT NOT NULL  
, created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() 
);
INSERT INTO card (title, content) VALUES ('foo', 'barbara')
;
SELECT * FROM card
;

card_id title content created
------- ----- ------- -------------------
   1    foo   barbara 2021-01-06 10:36:07
7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.