Date time reads: 0000-00-00 00:00:00

Databases
1

Hi there,

I have entered a field in a MySQL table using DATETIME selection. I left the character length blank because it has a default format.

but when I added something to the table I expected it to fill in the time and date. But it only reads:

0000-00-00 00:00:00

this is wrong, obviously.

I was thinking about adding date and time separately instead. Why is it not working? What do you recommend I use/do?

Matt.

2

but when I added something to the table

How did you do that? Can you post the code/query?

3

Show us what you are trying to enter into the database. Paste a typical value here.

4

I am not entering any code. i thought it was like an auto-increment. ie. when you enter something, it automatically adds the time and date.

You seem to be saying i have to have some code to enter the time and date!!?

Matt.

5

Was the column created with a DEFAULT value, like CURRENT_TIMESTAMP ?

6

depends

show us your table and we can help more

do a SHOW CREATE TABLE please

7

I do not know how to do a SHOW CREATE TABLE.

All I have done is chosen DATETIME. And named the field. Nothing more.

You suggest this is not enough!? Can I not make it automatic, like the auto-increment. ie. whenever an entry is made the time and date is taken!?

Matt.

8

Please, just give us the INSERT and CREATE TABLE queries.

For example:


CREATE TABLE `timetest` (`id` INTEGER AUTO_INCREMENT PRIMARY KEY, `date` DATETIME)

INSERT INTO `timetest` SET `date` = '2011-09-20 12:05:15'

The above code works like charm.

To show create table use:

SHOW CREATE TABLE `timetest`
9

In PHPMyAdmin, open the SQL box, enter the query SHOW CREATE TABLE and run it.

10

actually you also have to provide the table name :slight_smile:

SHOW CREATE TABLE employees

but no, a DATETIME does not get updated automatically, you gots to do that yourself

11

Details… :lol:

12

r937,

Now I know you have to use some code, I will enter it. But what code should I use?

I have tried this

$sql = "update cart set  timeofpurchase=datetime where cookieid = '" . GetCartId() . "'";
	$res = mysql_query($sql) or die(mysql_error());

but it does not work.

Since there is no user input, do I have to specify what to enter into the field?

Matt.

13

change this –

set timeofpurchase=datetime

to this –

set timeofpurchase=current_timestamp
14

r937,

Yes - great - that is working - but now there is another problem. I am in the UK and the time was about 11:00.

However the current_timestamp has recorded the time as 2011-09-21 03:00:30

It is not 03:00:30

What is wrong?

Thanks,
Matt.

15

Nothing is wrong. It’s using the server time.