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.
but when I added something to the table
How did you do that? Can you post the code/query?
Cups
3
Show us what you are trying to enter into the database. Paste a typical value here.
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.
salathe
5
Was the column created with a DEFAULT value, like CURRENT_TIMESTAMP ?
r937
6
depends
show us your table and we can help more
do a SHOW CREATE TABLE please
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.
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`
In PHPMyAdmin, open the SQL box, enter the query SHOW CREATE TABLE and run it.
r937
10
actually you also have to provide the table name 
SHOW CREATE TABLE employees
but no, a DATETIME does not get updated automatically, you gots to do that yourself
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.
r937
13
change this –
set timeofpurchase=datetime
to this –
set timeofpurchase=current_timestamp
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.
Nothing is wrong. It’s using the server time.