DATETIME is only storing the date and not the time

[thumoo_main]

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.

[m_hutley]

@thumoo_main

DATETIME function in mysql

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

@thumoo_main

When I store a record in the table, I am trying to use DATETIME to record the date and time is was entered

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

[thumoo_main]

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.

[m_hutley]

Run a EXPLAIN card query and show us the result?

[thumoo_main]

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()

[r937]

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