I have an INT (11) column for storing the current timestamp in seconds. The query looks like:
INSERT INTO `abc` (id, timestamp) VALUES ('', UNIX_TIMESTAMP ( NOW () ) )
I don't know why, but the date isn't changed. It doesn't matter when I send the query, the column value isn't changed. It has 1342692014 value, but I don't know why.
Is there any option or other function for timestamps? I must store dates in seconds.
Use the UNIX_TIMESTAMP() function to convert MySQL dates/times (such as now() = current time) to epochs.
UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since '1970-01-01 00:00:00'UTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that.
MySQL TIMESTAMP() Function The TIMESTAMP() function returns a datetime value based on a date or datetime value. Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value.
You never refer to the timestamp
column in your query. You only have a string:
INSERT INTO `abc` (id, 'timestamp') VALUES ('', UNIX_TIMESTAMP ( NOW () ) )
^^^^^^^^^^^
Edit:
I get this with your updated code:
ERROR 1630 (42000): FUNCTION test.NOW does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
Assuming it's not still the actual code and after fixing the syntax error, I can't reproduce your results. My educated guess is that id
is an auto-incremented integer primary key, your current SQL mode is making MySQL take ''
as NULL
and inserting a new row... But I haven't really tested this hypothesis.
My working code is this:
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With