Mysql query - insert data unix_timestamp (now ()) issue
I have an INT (11) column to store the current timestamp in seconds. The request looks like this:
INSERT INTO `abc` (id, timestamp) VALUES ('', UNIX_TIMESTAMP ( NOW () ) )
I don't know why, but the date hasn't changed. It doesn't matter, when I submit the request, the column value doesn't change. It has a value of 1342692014, but I don't know why.
Is there any parameter or other function for timestamps? I have to store dates in seconds.
source to share
You never reference a column timestamp
in your request. You have a line:
INSERT INTO `abc` (id, 'timestamp') VALUES ('', UNIX_TIMESTAMP ( NOW () ) )
^^^^^^^^^^^
Edit:
I get this with updated code:
ERROR 1630 (42000): FUNCTION test.NOW does not exist. Check the Parsing and Resolution of Function Names Section in the Reference Manual
Assuming this is not the actual code yet, and after fixing the syntax error, I cannot reproduce your results. My educated guess is that id
being an auto incremental integer primary key, your current SQL mode is forcing MySQL to accept ''
as NULL
and insert a new row ... But I haven't really tested this hypothesis.
My working code:
CREATE TABLE `abc` (
`pk` INT(10) NOT NULL AUTO_INCREMENT,
`id` VARCHAR(10) NULL DEFAULT NULL,
`timestamp` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`pk`)
)
ENGINE=InnoDB;
INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());
-- Wait a few seconds
INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());
-- Wait a few seconds
INSERT INTO abc (id, timestamp) VALUES ('', UNIX_TIMESTAMP());
SELECT timestamp FROM abc WHERE id='';
... and returns this:
+------------+
| timestamp |
+------------+
| 1342694445 |
| 1342694448 |
| 1342694450 |
+------------+
3 rows in set (0.00 sec)
source to share